SQL 使用左连接并检查该行是否与另一个检查 where 子句一起存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13128635/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using a left join and checking if the row existed along with another check in where clause
提问by loyalflow
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.
我不想包含用户也可能在 Banned 表中的任何行。
What's the best way to do this?
做到这一点的最佳方法是什么？
I could do this put a subquery in the where clause so it does something like:
我可以这样做在 where 子句中放置一个子查询，以便它执行以下操作：
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?
我认为最好的方法是进行 LEFT JOIN，我该怎么做？
回答by GarethD
According to this answer, in SQL-Server using NOT EXISTSis more efficient than LEFT JOIN/IS NULL
根据这个答案，在 SQL-Server 中使用NOT EXISTS比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:
为了完整起见，这就是我将如何使用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`
回答by Bort
You would just check that the value you got from LEFT JOINing with Banned was NULL:
您只需检查您从LEFT JOINing 与 Banned获得的值是否为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.
回答by RedFilter
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
回答by Muhito
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.
如果您需要结果并且应该排除某些内容并且它不是表的键 ID，请使用它。
