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.

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.

SQL> select city
  2  from authors
  3  minus
  4  select city
  5  from publishers;
CITY
--------------------
Ann Arbor
Corvallis
Covelo
Gary
Lawrence
Menlo Park
Nashville
Oakland
Palo Alto
Rockville
Salt Lake City
San Francisco
San Jose
Vacaville
Walnut Creek
15 rows selected.

The second reverses the order of the two queries. It displays all publisher cities, except those in the authors table.

SQL> select city
  2  from publishers
  3  minus
  4  select city from authors;
CITY
--------------------
Boston
Washington
[2 rows]