SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

There EXISTS a place where SELECT 1/0 doesn’t return an error.

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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...