Search code examples
sqlansi-sql

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.

Solution

  • 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'