24

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?

loyalflow
  • 14,275
  • 27
  • 107
  • 168

4 Answers4

33

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`
Community
  • 1
  • 1
GarethD
  • 68,045
  • 10
  • 83
  • 123
  • 1
    Helpful, relevant and important but does not directly answer the question. Perhaps incorporate RedFilter's answer into yours - show the OP *how* to do things the way that he asked, and *then* explain why he shouldn't? – Mark Amery Oct 29 '12 at 20:07
  • 3
    @Mark Amery, this answer link points to an excellent article on `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` – KM. Oct 29 '12 at 20:13
  • 2
    +1 @GarethD I tend to only use a left join when data from the right table needs to be returned in the result set. If no need to return data from the right table, take advantage of the anti semi join operators and just check for non-existence. – brian Oct 29 '12 at 21:52
8

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.
Bort
  • 7,398
  • 3
  • 33
  • 48
5
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
D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
-1
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.

Muhito
  • 1