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?
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` 
NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server which goes into way more detail than would be possible in an answer here. this answer clearly summarizes that long article: NOT EXISTS is more efficient than LEFT JOIN/IS NULL
You would just check that the value you got from LEFT JOINing with Banned was NULL:
SELECT U.* 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 -- no match in the Banned table. 
select u.* from Users u left outer join Banned b on u.userId = b.userId where u.isActive = 1 and u.status <> 'disabled' and b.UserID is null 
SELECT u.* FROM Users u LEFT JOIN Banned b ON u.userId = b.userId AND b.userRoles = 'VIP' WHERE u.isActive = 1 AND b.id IS NULL Use it if You need result and something should be excluded and it is not a key id for table.