• simon.barnes - Tuesday, April 25, 2017 6:46 PM

    I have it in my head that the exists (select * …) construct is adherent to the ANSI standards. I don't have a source on that unfortunately.

    That's what I thought, but I checked and it turns out I was wrong.

    I do have several versions of the ANSI standard (the final version of SQL1992, and late draft versions of SQL2003 and SQL2011). In all those versions, the EXISTS predicate is described as accepting a "<table subquery>". If the cardinality of that subquery is zero, the EXISTS predicate is false, otherwise it is true.
    A <table subquery> is any subquery that returns zero or more rows of one or more columns.

    So, SELECT * is allowed in this context, but not required.

    (Personally, I do like how the * represents the row instead of individual columns, which matches the semantics of EXISTS: checking the existence of a row)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/