Catch That Bug! - 25 Jan 2006

Welcome to a new challenge called "Catch That Bug!" Each month, I'll present T-SQL code that has a bug, and your challenge is to find it. I'll provide the solution the following month, along with a new challenge. Sometimes, you'll be able to research past magazine articles to find the cause of the bug. Other times, you'll have to figure out the solution on your own. Here's your first "Catch That Bug!" challenge.

From the Customers and Orders tables that you create and populate by running the code in Listing A, you need to return the details of customers who made orders. You build the following query to return the details:

SELECT custid,
  custname 
FROM dbo.Customers
WHERE custid 
  IN(SELECT custid
  FROM dbo.Orders); 

Given the sample data in the Customers and Orders tables, you probably expect your query to return customers A and B. Instead, you get back all customers, including those who made no orders. Why?