How to do EXISTS select in ANSI SQL
Is there a cross-database (at least SQL Server, Oracle, Postgre, MySQL, SQLite) way of doing what I would do in T-SQL using
开发者_运维知识库SELECT 1 WHERE EXISTS (SELECT * FROM Foo WHERE Bar = 'Quux')
It seems that this is not valid ANSI SQL syntax because there is no FROM clause. At present I am using COUNT(*) to do this, but I don't think that's optimal.
The subquery is unnecessary - use:
SELECT DISTINCT
1
FROM Foo
WHERE Bar = 'Quux'
A more complicated version, using aggregates & CASE expression:
SELECT CASE
WHEN COUNT(*) >= 1 THEN 1
ELSE 0
END
FROM Foo
WHERE Bar = 'Quux'
Cheating a little, but this ought to work and the DISTINCT 1 FROM Foo
should be trivially optimized by all but the dumbest of DBMSs!
SELECT DISTINCT 1 FROM Foo WHERE EXISTS (SELECT * FROM Foo WHERE Bar = 'Quux')
Just use COUNT until you need otherwise. Assuming good selectivity on Bar and and index, it won't matter too much but will be far more readable
SELECT COUNT(Bar) FROM Foo WHERE Bar = 'Quux'
You will trade optimisation for portability of course, so in this case use COUNT
精彩评论