Menu
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?
218
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
When you use a Left Outer join without an On or Where clause, there is no difference between the On and Where clause. Both produce the same result as in the following. First we see the result of the left join using neither an On nor a Where clause.
An outer join returns all of the rows that the equivalent inner join would return, plus non-matching rows from one or both tables. In the FROM clause, you can specify left, right, and full outer joins. In the WHERE clause, you can specify left and right outer joins only.
EXISTS and NOT EXISTS both short circuit - as soon as a record matches the criteria it's either included or filtered out and the optimizer moves on to the next record. LEFT JOIN will join ALL RECORDS regardless of whether they match or not, then filter out all non-matching records.
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` If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
