469,571 Members | 1,613 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,571 developers. It's quick & easy.

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

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
Nov 12 '05 #1
4 20558
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
Nov 12 '05 #2
> 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
Nov 12 '05 #3
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
Nov 12 '05 #4
> 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!!
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Donald Firesmith | last post: by
11 posts views Thread by Scott Brady Drummonds | last post: by
4 posts views Thread by Anastasios Hatzis | last post: by
3 posts views Thread by ajay2552 | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.