Sql：如何正确检查记录是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4253960/
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
Sql: How to properly check if a record exists
提问by systempuntoout
Reading some SQL Tuningdocumentation I found this:
阅读一些SQL 调优文档，我发现了这一点：
Select count(*):
- Counts the number of rows
- Often is improperly used to verify the existence of a record
Select count(*):
- 计算行数
- 通常不正确地用于验证记录的存在
Is Select count(*)really that bad?
是否Select count(*)真的如此糟糕？
What's the proper way to verify the existence of a record?
验证记录存在的正确方法是什么？
回答by Martin Schapendonk
It's better to use either of the following:
最好使用以下任一方法：
-- Method 1.
SELECT 1
FROM table_name
WHERE unique_key = value;
-- Method 2.
SELECT COUNT(1)
FROM table_name
WHERE unique_key = value;
The first alternative should give you no result or one result, the second count should be zero or one.
第一个选择应该给你没有结果或一个结果，第二个计数应该是零或一。
How old is the documentation you're using? Although you've read good advice, most query optimizers in recent RDBMS's optimize SELECT COUNT(*)anyway, so while there is a difference in theory (and older databases), you shouldn't notice any difference in practice.
您使用的文档有多旧？尽管您已经阅读了很好的建议，但最近 RDBMS 中的大多数查询优化器都进行了优化SELECT COUNT(*)，因此虽然理论上（和旧数据库）存在差异，但您在实践中应该不会注意到任何差异。
回答by Pavel Morshenyuk
I would prefer not use Count function at all:
我宁愿根本不使用 Count 函数：
IF [NOT] EXISTS ( SELECT 1 FROM MyTable WHERE ... )
<do smth>
For example if you want to check if user exists before inserting it into the database the query can look like this:
例如，如果您想在将用户插入数据库之前检查用户是否存在，查询可能如下所示：
IF NOT EXISTS ( SELECT 1 FROM Users WHERE FirstName = 'John' AND LastName = 'Smith' )
BEGIN
INSERT INTO Users (FirstName, LastName) VALUES ('John', 'Smith')
END
回答by C?t?lin Piti?
You can use:
您可以使用：
SELECT 1 FROM MyTable WHERE <MyCondition>
If there is no record matching the condition, the resulted recordset is empty.
如果没有符合条件的记录，则结果记录集为空。
回答by JesseW
The other answers are quite good, but it would also be useful to add LIMIT 1(or the equivalent, to prevent the checking of unnecessary rows.
其他答案非常好，但添加LIMIT 1（或等效的，以防止检查不必要的行）也很有用。
回答by oski
SELECT COUNT(1) FROM MyTable WHERE ...
will loop thru all the records. This is the reason it is bad to use for record existence.
将遍历所有记录。这就是用于记录存在不好的原因。
I would use
我会用
SELECT TOP 1 * FROM MyTable WHERE ...
After finding 1 record, it will terminate the loop.
找到 1 条记录后，它将终止循环。
回答by Winston Smith
You can use:
您可以使用：
SELECT COUNT(1) FROM MyTable WHERE ...
or
或者
WHERE [NOT] EXISTS
( SELECT 1 FROM MyTable WHERE ... )
This will be more efficient than SELECT *since you're simply selecting the value 1 for each row, rather than all the fields.
这将比SELECT *您简单地为每一行而不是所有字段选择值 1更有效。
There's also a subtle difference between COUNT(*) and COUNT(column name):
COUNT(*) 和 COUNT(列名) 之间也有细微的区别：
COUNT(*)will count all rows, including nullsCOUNT(column name)will only count non null occurrencesof column name
COUNT(*)将计算所有行，包括空行COUNT(column name)只会计算列名的非空出现
回答by user3059943
You can use:
您可以使用：
SELECT 1 FROM MyTable WHERE... LIMIT 1
Use select 1to prevent the checking of unnecessary fields.
使用select 1以避免不必要的字段的检查。
Use LIMIT 1to prevent the checking of unnecessary rows.
使用LIMIT 1以避免不必要的行的检查。
回答by DiPix
I'm using this way:
我正在使用这种方式：
IIF(EXISTS (SELECT TOP 1 1
FROM Users
WHERE FirstName = 'John'), 1, 0) AS DoesJohnExist
回答by Pranav
Other option:
其他选择：
SELECT CASE
WHEN EXISTS (
SELECT 1
FROM [MyTable] AS [MyRecord])
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END
