SQL Server – NOT IN Query with Join

join;sql serversql server 2014t-sql

I have two tables : Part, OrderLine:

Part (PartNum PK, PartDesc, Price)

OrderLine (OrderNum PK, PartNum PK, QtyOrdered)

And I want to find out the parts that were not ordered. I think I have a good idea, but I may be missing something; I think most likely on the syntax. Please check:

select Part.PartNum
    ,OrderLine.OrderNum
from OrderLine
inner join Part on OrderLine.PartNum = Part.PartNum
where PartNum not in (
        select distinct (OrderNum)
        from OrderLine
        )

Is this correct?

Best Answer

If you want the parts that were not ordered, then you should start with Part, not OrderLine. When you're thinking about sets, that is the only one that could conceivably have the items you're looking for. Part can be a Superset of ordered parts and OrderLine could easily be a Subset of parts.

SELECT PartName
FROM Part
WHERE PartNum NOT IN (
        SELECT PartNum
        FROM OrderLine
        )

More often than not, comparing integers will be far more efficient than strings, so I've changed the WHERE clause to look for PartNums, not PartNames.

Related Question