Sql server® 2012 t-sql fundamentals

dealing with Misbehaving Subqueries

This section introduces cases in which subqueries might behave counter to your expectations, and 

provides best practices that you can follow to avoid logical bugs in your code that are associated with 

those cases.

NULL

 Trouble

Remember that T-SQL uses three-valued logic. In this section, I will demonstrate problems that can 

evolve with subqueries when NULL marks are involved and you do not take into consideration the 

three-valued logic.

Consider the following apparently intuitive query that is supposed to return customers who did 

not place orders. 

SELECT custid, companyname 

FROM Sales.Customers 

WHERE custid NOT IN(SELECT O.custid 

                    FROM Sales.Orders AS O);

With the current sample data in the Orders table in the TSQL2012 database, the query seems to 

work the way you expect it to; and indeed, it returns two rows for the two customers who did not 

place orders.

custid      companyname 

----------- ---------------- 

22          Customer DTDMN 

57          Customer WVAXS 

www.it-ebooks.info

---