Kendra Little (b/t) reminded me of this fun little trick (with fairly important ramifications) in her latest quiz on logical joins (Q7)

Using AdventureWorks2014

SELECT *
FROM Sales.SalesOrderHeader as head
WHERE EXISTS (
SELECT 1/0 as y
FROM Sales.SalesOrderDetail AS det
WHERE det.SalesOrderID = head.SalesOrderID);

At this point you are probably expecting something like this:

Msg 8134, Level 16, State 1, Line 3

Divide by zero error encountered.

And most of the time you will. But the cool thing about this trick is that the field list in an EXISTS statement isn’t actually executed. So query plan for this:

EXISTS (
SELECT 1/0 as y
FROM Sales.SalesOrderDetail AS det
WHERE det.SalesOrderID = head.SalesOrderID);

Is the same as:

EXISTS (
SELECT *
FROM Sales.SalesOrderDetail AS det
WHERE det.SalesOrderID = head.SalesOrderID);

Is the same as:

EXISTS (
SELECT det.SalesOrderId * det.OrderQty / det.UnitPrice + det.UnitPriceDiscount
FROM Sales.SalesOrderDetail AS det
WHERE det.SalesOrderID = head.SalesOrderID);

Or even:

EXISTS (
SELECT (SELECT TOP 1 AccountNumber FROM Sales.Customer)
FROM Sales.SalesOrderDetail AS det
WHERE det.SalesOrderID = head.SalesOrderID);

Using SET STATISTICS IO, TIME ON and Richie Rump’s (b/t) statisticsparser.com. I noticed two important things when running these in queries.

• The execution and CPU times were almost identical.
• There was no reference to Sales.Customer in the last query plan or the IO statistics.

Ok. So that seems to prove that the field list in the EXISTS isn’t executed. But how about this?

SELECT *
FROM Sales.SalesOrderHeader as head
WHERE EXISTS (
SELECT Bob
FROM Sales.SalesOrderDetail AS det
WHERE det.SalesOrderID = head.SalesOrderID);

Msg 207, Level 16, State 1, Line 44

Invalid column name ‘Bob’.

In fact I can’t think of a query I’ve ever written where this would work. So what does this prove?

SQL Server hates Bob!

No, of course not. What it does mean however is that the field list is still parsed even though it isn’t executed. Bob in this case is a field. So if Sales.SalesOrderDetail had a column called Bob then the query would parse and execute. If you put quotes around it ‘Bob’ then it will parse just fine anyway because it’s now a string. The end result is that if you put a literal, or a valid field, table, query, etc in the field list then it will parse and not execute.

So, want to astonish and confuse your friends? Put 1/0 in your EXISTS subqueries. But I recommend putting in a comment so hey don’t get TOO confused.

SELECT *
FROM Sales.SalesOrderHeader as head
WHERE EXISTS (
SELECT 1/0 as y /*Yes this works so please don't change/delete.*/FROM Sales.SalesOrderDetail AS det
WHERE det.SalesOrderID = head.SalesOrderID);

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, T-SQL