Search code examples
sqlintersectansi-sql

Does INTERSECT operator exist in the SQL standard?

Does INTERSECT operator exist in the SQL standard? If it exists, is it an optional operator?

Please, leave a trustable source.

Solution

  • Your professor is either wrong, or else you misunderstood what they said. INTERSECT is not supported in every implementation of SQL, but it is the standard.

    I checked my copy of "Understanding the New SQL: A Complete Guide" by Jim Melton and Alan R. Simon (1993) which covers SQL-92.

    Page 171 says:

    The INTERSECT and EXCEPT Operators

    INTERSECT returns all rows that exist in the intersection of two tables; that is, in both tables.

    SELECT * 
  FROM music_titles
INTERSECT
SELECT * 
  FROM discontinued_albums;

    The preceding query will return, for example, all discontinued albums that have been re-released.

    Here's a link to Google Books with the word INTERSECT highlighted in a search: https://www.google.com/books/edition/Understanding_the_New_SQL/ZOOMSTZ4T_QC?bsq=intersect&gbpv=1

    I also checked my copy of "SQL-99 Complete, Really" by Peter Gulutzan and Trudy Pelzer (1999). It also documents INTERSECT.