Does INTERSECT operator exist in the SQL standard?

Question

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

Please, leave a trustable source.

Why are you asking? I'm pretty sure the answer is "yes", but that has little bearing on whether or not databases support it. — Gordon Linoff, Nov 26 '19 at 22:57
Hello,if wikipedia is trustable for you: https://en.wikipedia.org/wiki/Set_operations_(SQL) — zip, Nov 26 '19 at 23:02
@wallyk This operator intersect the result of two queries. https://en.wikipedia.org/wiki/Set_operations_(SQL)#INTERSECT_operator as GordonLinoff sent. But Wikipedia is not trustable for me. — Bakhanov A., Nov 26 '19 at 23:04
@GordonLinoff I am asking because my professor said the SQL standard does not support it. But I cannot believe because many DBMS that use SQL support intersection. — Bakhanov A., Nov 26 '19 at 23:07
@zip this is not trustable enough. But I know that such an operator exists in many SQL languages — Bakhanov A., Nov 26 '19 at 23:08
For the first few decades, we had only genuine SQL platforms, thank Codd. They were not called SQL unless they complied with the SQL Standard. In the 00's with freeware; shareware; vapourware; noware becoming accepted for non-commercial use, there were various suites of code, which were neither platforms (no Server Architecture; no ACID Transactions; etc) nor SQL (no compliance). But they have "SQL" in their name, which is **fraud**. Most lecturers like their freeware **NON**sql, erroneously think that it is SQL, and then make false claims about SQL from that place of ignorance. — PerformanceDBA, Nov 30 '19 at 09:25
The SQL 99 Validator (https://developer.mimer.com/services/sql-validator-99/) says: _Validation result: The following feature outside Core SQL-99 is used: F302, "INTERSECT table operator"_ — jarlh, Dec 19 '19 at 16:24

score 6 · Accepted Answer · edited Jun 20 '20 at 09:12

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.

score 2 · Answer 2 · answered Dec 19 '19 at 16:30

2

INTERSECT is included in the ANSI/ISO SQL standard (ISO/IEC 9075-2:2016(E)), as the optional feature F302:

F302 INTERSECT table operator

F302-01 INTERSECT DISTINCT table operator
F302-02 INTERSECT ALL table operator

answered Dec 19 '19 at 16:30

jarlh

42,561
8
45
63

Does INTERSECT operator exist in the SQL standard?

2 Answers2