INTERSECT and MINUS

Although long discussed as part of the relational model, INTERSECT and MINUS are not widely implemented as keywords. You may be able to get the same effect with other code.

Oracle is one system that supports INTERSECT and MINUS. The first query shows the intersection on city between authors and publishers. Berkeley is the only shared location.

Oracle
SQL> select city
  2  from authors
  3  intersect
  4  select city
  5  from publishers;

CITY
--------------------
Berkeley

The following MINUS queries have different results. The first returns all the cities from authors, except those that appear in publishers.

Oracle
SQL> select city
  2  from authors 3 minus 4 select city 5 from publishers; CITY -------------------- Ann Arbor Corvallis Covelo ...

Get Practical SQL Handbook, The: Using SQL Variants, Fourth Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial