CODEWITHSUNDEEP
sqlt-sql
Michael DiLeo
Michael DiLeo

Reputation: 601

Get product description that isn't in an order

I need the p_desc where products.p_no is not in order_details.

Note: I have done many searches and this is what I came up with as the result.

-- Why would this work?
select p_desc from products
except
select p_no from order_details

-- Then,
select p_desc from products
where products.p_no not in order_details.p_no

-- and finally,
select p_desc from products
where (p_no not in (select p_no from order_details))

Are any of these right? I was told to use except, but I don't see how the first statement is comparing the p_no property, so I'm assuming that this is wrong.

Upvotes: 2

Views: 55

Answers (2)

Johan
Johan

Reputation: 76641

Because this is homework I'm not going to spell it out.

// Why would this work?
select p_desc from products
except
select p_no from order_details

See: http://technet.microsoft.com/en-us/library/ms188055.aspx
Hint: the following code is equivalent:

SELECT p.p_desc FROM products p
LEFT JOIN order_details o ON (o.p_no = p.p_no)
WHERE o.p_no IS NULL

// Then,
select p_desc from products
where products.p_no not in order_details.p_no

That looks like syntax error.

// and finally,
select p_desc from products
where (p_no not in (select p_no from order_details))

Looks good and I find this one the easiest to understand.

Remarks on Microsoft's EXCEPT
Whilst I can see the ease of use argument, using code like that will make it very hard to port your code to another platform.
I recommend staying closer to the core of SQL-92 and use the form in your last statement.
It will make you a more all-round programmer and prevent lock-in to a single platform, which can only be a good thing as far as job prospects go.

From a functional point of view code sample A offers no benefit over sample C, both take the same amount of time to run.

Upvotes: 1

Sebastian Meine
Sebastian Meine

Reputation: 11813

The cleanest way is to use NOT EXISTS. I just answered a similar question here:

https://dba.stackexchange.com/questions/51270/get-unmatched-rows-for-foreign-key

(Note that that answer is for MySQL, however the T-SQL syntax is the same.)

In your case that query would look like this:

SELECT p_desc FROM dbo.products AS P
 WHERE (NOT EXISTS (SELECT 1 FROM dbo.order_details AS OD WHERE OD.p_no = P.p_no));

Upvotes: 0

Related Questions