How do you return data Joined with a Blacklist table?

Ask Time：2012-04-23T06:07:39 Author：Control Freak

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?

Author:Control Freak，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/10272539/how-do-you-return-data-joined-with-a-blacklist-table

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

How do you return data Joined with a Blacklist table?