How does WHERE EXISTS ( SELECT NULL... ) work?

How does WHERE EXISTS ( SELECT NULL FROM xyz blah blah blah blah ) work? I have seen this in many places but do not understand the logic. Can you please explain with a simple demo table.

I've seen the subquery written with SELECT 1 and with SELECT * but never with SELECT NULL. I have no reason to suspect it won't work, though. I just prefer to use SELECT 1.

It doesn't really matter what you select, though, because whenever you have an EXISTS subquery, the database will try to retrieve the result set defined by the subquery, and base the execution of the outer query on whether any subquery rows were found, and not on what was in them. So it doesn't matter whether you SELECT 1 or SELECT * or SELECT NULL, what matters is if any rows are found -- and this is a yes/no result.

Consider the following example --

select DriverName
  from DriverTable
 where DriverAge > 72
    or EXISTS
       ( select 'Doh!'  
           from Tickets
          where DriverID = DriverTable.ID )

The above query lists drivers due for re-examination, based on either their age or on having received a traffic ticket. The subquery looks for tickets for each driver, and if it finds at least one for any driver, then the EXISTS evaluates true, and the driver gets re-examined. It doesn't matter what the subquery returns, because whatever it returns isn't passed to the outer query. All that matters is that one or more rows were found.

You could do the above query with an outer join, but it would be exceedingly messy. It would have to be an outer join, because there are plenty of old drivers that have never received a ticket. It would be inefficient, too, because it would return all tickets, even though you only really need one to decide, so then you'd have to do a GROUP BY or a DISTINCT, both of which are comparatively inefficient.

For More Information

• What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.