Please review this statement:
SELECT TableID FROM Table t1
INNER JOIN BlackList b ON b.TableID <> t1.TableID
I was thinking this statement returned everything from Table
that wasn't found in the Blacklist
table, but instead it returned nothing at all (0 rows). If I'm trying to return everything from Table
that IS NOT found in the Blacklist
table, what's the best way to do this? I assume you can do this:
SELECT TableID FROM (
SELECT TableID, CASE WHEN b.TableID IS NULL THEN 1 ELSE 0 END OnBlackList
FROM Table t1
LEFT JOIN Blacklist b ON b.TableID = t1.TableID
) tb1
WHERE tb1.OnBlackList = 0
But I was looking for a shorter, more efficient solution. Any suggestions?
Aaron Bertrand :
SELECT TableID FROM dbo.Table\nEXCEPT\nSELECT TableID FROM dbo.Blacklist;\n",
2012-04-22T22:13:02
Mike Ryan :
One basic way is using a NOT EXISTS:\n\nSELECT TableID FROM Table t1\nwhere NOT EXISTS (select * from BlackList b where b.TableID = t1.TableID);\n\n\nThis will select rows in t1 that are not present in the BlackList table.",
2012-04-22T22:12:47
NaN :
SELECT TableT1.TableId FROM TableT1 \nLEFT OUTER JOIN BlackList ON \nTableT1.TableID = BlackList.TableID \nwhere BlackList.TableId IS NULL\n\n\nI wrote the above, but now I also found a previous question/ answer in StackOverflow:\nHow to find rows in one table that have no corresponding row in another table",
2012-04-22T22:17:14