Intersect

[Jack Klebanoff <kl...@Mutagen.Net>]

I plan on working on the INTERSECT operator in Derby.

It is a table operator, syntactically like UNION, e.g.
select a,b from t1 INTERSECT select d,e from t2
Like UNION, it requires that the two source tables have compatible 
columns. It returns the rows that are in both the source tables.

By default duplicates are removed. If  "INTERSECT ALL" is specified then 
duplicates are not removed. In that case if row R occurs m times in one 
source table and n times in the other then row R will appear min(m,n) 
times in the result. (This is specified by the SQL standard).

The full syntax is:
  <nonJoinQueryTerm> ::= <nonJoinQueryPrimary> INTERSECT [ALL] 
<nonJoinQueryPrimary>
According to the SQL standard, the INTERSECT operator takes precedence 
over the UNION operator. Thus
  query1 INTERSECT query2 UNION query3
is equivalent to
  (query1 INTERSECT query2) UNION query3

The INTERSECT operator is supported in DB2, Oracle, and Postgres.

Comments? Objections?

Jack Klebanoff