Using a left join and checking if the row existed along with another check in where clause

Question

I have the following tables:

Users Banned  SELECT u.* FROM Users WHERE u.isActive = 1     AND       u.status <> 'disabled' 

I don't want to include any rows where the user may also be in the Banned table.

What's the best way to do this?

I could do this put a subquery in the where clause so it does something like:

u.status <> 'disabled' and not exist (SELECT 1 FORM Banned where userId = @userId) 

I think the best way would be to do a LEFT JOIN, how could I do that?

Answer 1

According to this answer, in SQL-Server using NOT EXISTS is more efficient than LEFT JOIN/IS NULL

SELECT  * FROM    Users u WHERE   u.IsActive = 1 AND     u.Status <> 'disabled' AND     NOT EXISTS (SELECT 1 FROM Banned b WHERE b.UserID = u.UserID) 

EDIT

For the sake of completeness this is how I would do it with a LEFT JOIN:

SELECT  * FROM    Users u         LEFT JOIN Banned b             ON b.UserID = u.UserID WHERE   u.IsActive = 1 AND     u.Status <> 'disabled' AND     b.UserID IS NULL        -- EXCLUDE ROWS WITH A MATCH IN `BANNED`