Monthly Archives: August 2011

Not In != Not Exists

So, in SQL, what is the distinction between NOT IN and NOT EXISTS?

This can be a bit confusing, and is a bit of a gotcha. Because SQL is a declarative language, the idea is that you declare what you want to query. But sometimes, a normal English declaration of intent does not always map to the correct SQL construct. And the NOT IN vs. NOT EXISTS mixup is a good case in point.

I’ll demonstrate this by way of example. Say we have the following database schema (run the script CreateCampusDB.sql to create and populate the database):

As you can see, a Student may also be a lecturer. This is often the case where phd graduates are also lecturers.

Now, we want to know which lecturers are not also students. If we verbalise this, we might say “Tell me all of the lecturers which are not students”. That starts to look like, “Tell me all the lecturers which are not in the list of all the students.” So, we may form the query:

select * from dbo.Lecturer l where l.StudentId not in (select st.StudentId from dbo.Student st)

The logic seems correct. If the foreign key of Lecturer does not show up in the list of primary keys in the Student table, then the Lecturer will not be a Student. Wrong! Fail!

Taking a good look at that query, you will never get the information that you seek. The subquery brings back a finite list of primary keys, not including nulls. A lecturer will be a “not-Student” where it has a null value in the StudentId foreign key column of the Lecturer table. A comparison of a value (primary key) to null will return no rows at all, regardles of whether it is negated or not (* see further discussion of this point below).

The correct way to get our result would be to use NOT EXISTS and a correlated subquery:

select * from dbo.Lecturer l where not exists (select * from dbo.Student st where l.StudentId  = st.StudentId )

Here, we have a row by row comparison and the null values are taken into account in the evaluation.

Or, you could use an outer join as so:

select * 
from dbo.Student st right outer join dbo.Lecturer l 
on st.StudentId = l.StudentId
where l.StudentId is null

(I only showed that as a proof of the query above which uses NOT EXISTS.)

* This post has been prevaricated on the assumption that the query is running with ANSI_NULLS on. That is the default behavour i.e. comparing a value to null will return no rows at all. This makes sense, as null is an unknown. It usually means that it is unknown whether there is a value or not. You can change that default behaviour by setting ANSI_NULLS to OFF. If you do this and run the 1st query, you will get the same anwser as the correlated subquery and the outer join:

select * from dbo.Lecturer l where l.StudentId not in (select st.StudentId from dbo.Student st)

Delegates Explained – Part 3

The Delegates Series:

  1. Delegates Explained – Part 1
  2. Delegates Explained – Part 2
  3. Delegates Explained – Part 3 <<
  4. Delegates Explained – Part 4

A quick detour (as in my last post I said I would talk about events in this post).

There was something I left out of the last post which should be covered before we move onto events.

Recall our subscription methods:

            public void SubscribeMeForNotifications(params NotifyDvdArrived[] notificationMethod)
                if (notificationMethod != null)
                    //  Create a new Delegate array for the overload of the Combine method which takes a Delegate array as a parameter.
                    Delegate[] listOfDelegates = new Delegate[notificationMethod.Length + 1];

                    //  Now, add the existing notification to the array, along with the ones passed in as a parameter (the params array).
                    if (this.notification != null)
                        listOfDelegates[0] = notification;
                        notificationMethod.CopyTo(listOfDelegates, 1);
                        notificationMethod.CopyTo(listOfDelegates, 0);

                    notification = NotifyDvdArrived.Combine(listOfDelegates) as NotifyDvdArrived;
                    throw new ArgumentException("The notificationMethod argument was null. It must have a value for a subscription to be successful.");


            public void SubscribeMeForNotifications(NotifyDvdArrived notificationMethod)
                notification = NotifyDvdArrived.Combine(notification, notificationMethod) as NotifyDvdArrived;

The good news is, you don’t need to write those methods when using multicast delegates. I only wrote those methods to expose you to the Combine method of the Delegate. However, there is a succinct piece of C# syntax that results in the Combine method being called. It abstracts away the need to write those 2 subscription methods. It is +=:

            theStore.notification += new NotifyDvdArrived(BroadcastArrivalOfDvdByBatSignal);
            theStore.notification += new NotifyDvdArrived(BroadcastArrivalOfDvdByLoudSpeaker);
            theStore.notification += new NotifyDvdArrived(BroadcastArrivalOfDvdBySnailMail);

So, we can re-factor our DvdStore class:

        public class DvdStore
            public NotifyDvdArrived notification;

            public DvdStore()
                // default constructor

            public void SendOutNotification(string titleOfDvd)

Note that in addition to removing the subscription methods, I also made the notification member public. In my next post in this series, I will refactor this even further (it’s probably not a good idea making the notification member public).