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