Return-Path: 
 <derby-dev-return-1084-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: (qmail 81343 invoked from network); 29 Nov 2004 22:49:13 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur-2.apache.org with SMTP; 29 Nov 2004 22:49:13 -0000
Received: (qmail 23673 invoked by uid 500); 29 Nov 2004 22:49:12 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 23637 invoked by uid 500); 29 Nov 2004 22:49:12 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
list-help: <mailto:derby-dev-help@db.apache.org>
list-unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
list-post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 23617 invoked by uid 99); 29 Nov 2004 22:49:12 -0000
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=
X-Spam-Check-By: apache.org
Received-SPF: neutral (hermes.apache.org: local policy)
Received: from e2.ny.us.ibm.com (HELO e2.ny.us.ibm.com) (32.97.182.142)
  by apache.org (qpsmtpd/0.28) with ESMTP; Mon, 29 Nov 2004 14:49:10 -0800
Received: from d01relay04.pok.ibm.com (d01relay04.pok.ibm.com [9.56.227.236])
	by e2.ny.us.ibm.com (8.12.10/8.12.10) with ESMTP id iATMn8Ft029439
	for <derby-dev@db.apache.org>; Mon, 29 Nov 2004 17:49:08 -0500
Received: from d01av03.pok.ibm.com (d01av03.pok.ibm.com [9.56.224.217])
	by d01relay04.pok.ibm.com (8.12.10/NCO/VER6.6) with ESMTP id iATMmrYm286924
	for <derby-dev@db.apache.org>; Mon, 29 Nov 2004 17:49:08 -0500
Received: from d01av03.pok.ibm.com (loopback [127.0.0.1])
	by d01av03.pok.ibm.com (8.12.11/8.12.11) with ESMTP id iATMmrdu012209
	for <derby-dev@db.apache.org>; Mon, 29 Nov 2004 17:48:53 -0500
Received: from [9.72.133.44] (JACK-IBM-LT1.usca.ibm.com [9.72.133.44])
	by d01av03.pok.ibm.com (8.12.11/8.12.11) with ESMTP id iATMmqrs012170
	for <derby-dev@db.apache.org>; Mon, 29 Nov 2004 17:48:52 -0500
Message-ID: <41ABA7EE.9010608@Mutagen.Net>
Date: Mon, 29 Nov 2004 14:51:26 -0800
From: Jack Klebanoff <klebanof@Mutagen.Net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
 rv:1.7.1) Gecko/20040707
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Derby Developers <derby-dev@db.apache.org>
Subject: Intersect
X-Enigmail-Version: 0.85.0.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Checked: Checked
X-Spam-Rating: minotaur-2.apache.org 1.6.2 0/1000/N

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