I am using SQL Server 2008 R2
我正在使用SQL Server 2008 R2
I just want to test if something exists in a table
我只是想测试表中是否存在某些东西
IF EXISTS (SELECT * FROM ta WHERE ca = 'abc') PRINT 'YES'
IF EXISTS (SELECT ca FROM ta WHERE ca = 'abc') PRINT 'YES'
IF EXISTS (SELECT 1 FROM ta WHERE ca = 'abc') PRINT 'YES'
IF EXISTS (SELECT (1) FROM ta WHERE ca = 'abc') PRINT 'YES'
IF EXISTS (SELECT TOP 1 1 FROM ta WHERE ca = 'abc') PRINT 'YES'
Do they have any differences in result/side effect/performance (no matter how tiny)?
他们在结果/副作用/表现上有任何差异（无论多小）？
Thank you
谢谢
2 个解决方案
#1
6
Absolutely no difference - the IF EXISTS(...) will only check for existence of rows based on the WHERE clause in your statement.
绝对没有区别 - IF EXISTS（...）只会根据语句中的WHERE子句检查是否存在行。
Everything else in the statement is irrelevant - doesn't make any difference whether you use SELECT * or SELECT 1 or or SELECT TOP 1 *. Even using SELECT * .... does NOT select all columns from the table - it again just checks for existence of the data based on the WHERE clause.
声明中的其他所有内容都无关紧要 - 使用SELECT *或SELECT 1或SELECT TOP 1 *没有任何区别。即使使用SELECT * ....也不会从表中选择所有列 - 它只是根据WHERE子句检查数据是否存在。
All five queries have exactly the same execution plan
所有五个查询都具有完全相同的执行计划
#2
3
There is no difference, the execution plan seems to be same.
没有区别，执行计划似乎是一样的。
#1
6
Absolutely no difference - the IF EXISTS(...) will only check for existence of rows based on the WHERE clause in your statement.
绝对没有区别 - IF EXISTS（...）只会根据语句中的WHERE子句检查是否存在行。
Everything else in the statement is irrelevant - doesn't make any difference whether you use SELECT * or SELECT 1 or or SELECT TOP 1 *. Even using SELECT * .... does NOT select all columns from the table - it again just checks for existence of the data based on the WHERE clause.
声明中的其他所有内容都无关紧要 - 使用SELECT *或SELECT 1或SELECT TOP 1 *没有任何区别。即使使用SELECT * ....也不会从表中选择所有列 - 它只是根据WHERE子句检查数据是否存在。
All five queries have exactly the same execution plan
所有五个查询都具有完全相同的执行计划
#2
3
There is no difference, the execution plan seems to be same.
没有区别，执行计划似乎是一样的。