exception join vs. inner join with <> (is it the same?)

[PASQUALE]

Hi
I have a question:
do the both statements below give the same result?
If yes then does somebody know something about preformance differencies
using these joins?

SELECT A.*
FROM Table1 A
INNER JOIN Table2 B on A.Field1 <> B.Field1

SELECT A.*
FROM Table1 A
EXCEPTION JOIN Table2 B on A.Field1 = B.Field1

Thanks in advance

[Knut Stolze]

PASQUALE wrote:

Hi
I have a question:
do the both statements below give the same result?
If yes then does somebody know something about preformance differencies
using these joins?

SELECT A.*
FROM Table1 A
INNER JOIN Table2 B on A.Field1 <> B.Field1

SELECT A.*
FROM Table1 A
EXCEPTION JOIN Table2 B on A.Field1 = B.Field1

They don't give the same results because the 1st version succeeds whereas
the 2nd gives a syntax error because DB2 doesn't know anything about
"EXCEPTION JOIN"s.

What exactly is an exception join supposed to be? The SQL99 standard
doesn't know about that either.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[PASQUALE]

> They don't give the same results because the 1st version succeeds whereas

the 2nd gives a syntax error because DB2 doesn't know anything about
"EXCEPTION JOIN"s.

What exactly is an exception join supposed to be? The SQL99 standard
doesn't know about that either.

Hmmm... I've got here a DB2 database on AS/400. I have used queries with the
exception join and it works very well.
Please take a look at:
http://publib.boulder.ibm.com/html/a...bafymst106.htm
There is some info about it and that's where I've read of it for the first
time.
But - yes, I was surprised, too. Until now I programmed under MsSQL and
there are no exception joins.

Greetings

[Knut Stolze]

PASQUALE wrote:

They don't give the same results because the 1st version succeeds whereas
the 2nd gives a syntax error because DB2 doesn't know anything about
"EXCEPTION JOIN"s.

What exactly is an exception join supposed to be? The SQL99 standard
doesn't know about that either.

Hmmm... I've got here a DB2 database on AS/400. I have used queries with
the exception join and it works very well.
Please take a look at:

http://publib.boulder.ibm.com/html/a...bafymst106.htm

Oh, I thought you were talking about DB2 for Linux, Unix, Windows. I don't
know enough about AS400 to comment here.

The way I understand the documentation you provided is that your two queries
are not semantically equivalent. Let's say you have such a situation:

CREATE TABLE t1 ( c1 INT )
CREATE TABLE t2 ( c1 INT )
INSERT INTO t1 VALUES (1), (2), (3)
INSERT INTO t2 VALUES (1), (2)

SELECT a.* FROM t1 AS a INNER JOIN t2 AS b ON a.c1 <> b.c1

--> returns 1, 2, 3 because there is _at least one_ row in t2 for each row
in t1 where the column values are different, i.e. t1.c1 = 1 and t2.c1 = 2
--> true

SELECT a.* FROM t1 AS a EXCEPTION JOIN t2 AS b ON a.c1 = b.c1

--> returns only 3 because there is _no_ row in t2 where the column value is
the same

Besides using the NOT EXISTS predicate shown in the manual, you can also use
an OUTER JOIN and a predicate in the WHERE clause to get the same semantics
as the EXCEPTION JOIN:

SELECT a.*
FROM t1 AS a LEFT OUTER JOIN t2 AS b ON a.c1 = b.c1
WHERE b.c1 IS NULL

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[PASQUALE]

> Oh, I thought you were talking about DB2 for Linux, Unix, Windows. I
don't

know enough about AS400 to comment here.

The way I understand the documentation you provided is that your two queries are not semantically equivalent. Let's say you have such a situation: (...) Besides using the NOT EXISTS predicate shown in the manual, you can also use an OUTER JOIN and a predicate in the WHERE clause to get the same semantics as the EXCEPTION JOIN:

(...)
OK, I've tried it out and now I understand how it works. Thanks a lot!!