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:
::= INTERSECT [ALL]
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