Outer join, syntax 
Author Message
 Outer join, syntax
Hi all,

In a sql-statement I've replaced ex1 with ex2, but I've got a different
query-result. What have I done wrong?

Any help would be highly appreciated.
Leo

--ex1:
FROM Aa, Bb
WHERE Aa.fn1 *= Bb.fn1
AND Aa.fn2 *= Bb.fn2
AND ...

--ex2:
FROM Aa left outer join Bb
on (Aa.fn1 = Bb.fn1 AND Aa.fn2 = Bb.fn2)
where ...



Mon, 07 Jul 2003 22:37:57 GMT
 Outer join, syntax

ex2 should look like this:

FROM   Aa
left   outer join Bb
          on Aa.fn1 = Bb.fn1
left   outer join Bb
          on Aa.fn2 = Bb.fn2
where ...

Mike

Quote:
-----Original Message-----

Hi all,

In a sql-statement I've replaced ex1 with ex2, but I've got a different
query-result. What have I done wrong?

Any help would be highly appreciated.
Leo

--ex1:
FROM Aa, Bb
WHERE Aa.fn1 *= Bb.fn1
AND Aa.fn2 *= Bb.fn2
AND ...

--ex2:
FROM Aa left outer join Bb
on (Aa.fn1 = Bb.fn1 AND Aa.fn2 = Bb.fn2)
where ...
.



Mon, 07 Jul 2003 23:06:40 GMT
 Outer join, syntax
HI,

If I tried your solution in MS SQL Server 2000 Query Analyzer, since I was
expecting problems with the duplicated name, and effectively, I have the
following error:
-------------------
SELECT     Aa.*, Bb.*
FROM         Aa LEFT OUTER JOIN
                      Bb ON Aa.F1 = Bb.F1 LEFT OUTER JOIN
                      Bb ON Aa.F2 = Bb.F2

------------------
Tables or functions 'Bb' and 'Bb' have the same exposed names. Use
correlation names to distinguish them.
------------------

About the initial problem, while I understand

SELECT Aa.*, Bb.*
 FROM Aa left outer join Bb
 on (Aa.fn1 = Bb.fn1 AND Aa.fn2 = Bb.fn2)

for a given row of Aa. the result is supplying the matching Bb.* accordingly
to the condition, but if absolutely no row in Bb makes the result in the ON
expression true, for a given row in Aa, then that row in Aa is still listed,
but the selected Bb.* columns are filled with Nulls.

I don't understand the would be meaning of the older syntax... I thought it
would have been the same thing, and I am surprised it is not... ( I never
used that older syntax). Someone can explained it (about why it differs).

Vanderghast, Access MVP


Quote:
> ex2 should look like this:

> FROM   Aa
> left   outer join Bb
>           on Aa.fn1 = Bb.fn1
> left   outer join Bb
>           on Aa.fn2 = Bb.fn2
> where ...

> Mike

> -----Original Message-----
> Hi all,

> In a sql-statement I've replaced ex1 with ex2, but I've got a different
> query-result. What have I done wrong?

> Any help would be highly appreciated.
> Leo

> --ex1:
> FROM Aa, Bb
> WHERE Aa.fn1 *= Bb.fn1
> AND Aa.fn2 *= Bb.fn2
> AND ...

> --ex2:
> FROM Aa left outer join Bb
> on (Aa.fn1 = Bb.fn1 AND Aa.fn2 = Bb.fn2)
> where ...
> .



Wed, 09 Jul 2003 03:59:15 GMT
 Outer join, syntax

Quote:
>> In a sql-statement I've replaced ex1 with ex2, but I've got a

different query-result. What have I done wrong? <<

Nothing.

.  Here is how OUTER JOINs work in SQL-92.  Assume you are given:

Table1      Table2
a  b        a  c
======      ======
1  w        1  r
2  x        2  s
3  y        3  t
4  z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a      <== join condition
    AND Table2.c = 't';      <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved table"
in the query.  What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.

1)  We build the CROSS JOIN of the two tables.  Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:


Let * = passed the second predicate

Table1 CROSS JOIN Table2
a  b        a  c
=========================

1  w      2  s
1  w      3  t *
2  x      1  r

2  x      3  t *
3  y      1  r
3  y      2  s

4  z      1  r
4  z      2  s
4  z      3  t *

Table1 LEFT OUTER JOIN Table2
a  b        a  c
=========================
3  y    3      t      <= only TRUE row
-----------------------
1  w    NULL  NULL  Sets of duplicates
1  w    NULL  NULL
1  w    NULL  NULL
-----------------------
2  x    NULL  NULL
2  x    NULL  NULL
2  x    NULL  NULL
3  y    NULL  NULL  <== derived from the TRUE set - Remove 3  y
NULL  NULL
-----------------------
4  z    NULL  NULL
4  z    NULL  NULL
4  z    NULL  NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a  b        a  c
=========================
1  w    NULL  NULL
2  x    NULL  NULL
3  y    3      t
4  z    NULL  NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products.  Consider the two Chris Date tables

Suppliers        SupParts
supno            supno partno qty
=========        ==============
S1              S1  P1    100
S2              S1  P2    250
S3              S2  P1    100
                  S2  P2    250

and let's do an extended equality outer join like this:

SELECT *
  FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
  AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1    S1  P1    100
S1    S1  P2    250
S2    S2  P1    100
S2    S2  P2    250
S3  NULL  NULL  NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1  S1  P1    100
S2  S2  P1    100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1  S1  P1    100
S2  S2  P1    100
S3  NULL NULL  NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds!  In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
  FROM Supplier
        LEFT OUTER JOIN
        SupParts
        ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
  FROM Supplier
      LEFT OUTER JOIN
      SupParts
      ON Supplier.supno = SupParts.supno
          AND qty < 200;

Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-
92.  For example to find the students who have taken Math 101 and might
have taken Math 102:

SELECT C1.student, C1.math, C2.math
  FROM (SELECT * FROM Courses WHERE math = 101) AS C1
      LEFT OUTER JOIN
      (SELECT * FROM Courses WHERE math = 102) AS C2
      ON C1.student = C2.student;

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com
http://www.deja.com/



Wed, 09 Jul 2003 08:17:15 GMT
 Outer join, syntax
[posted and mailed]

Quote:
>In a sql-statement I've replaced ex1 with ex2, but I've got a different
>query-result. What have I done wrong?

>Any help would be highly appreciated.
>Leo

>--ex1:
>FROM Aa, Bb
>WHERE Aa.fn1 *= Bb.fn1
>AND Aa.fn2 *= Bb.fn2
>AND ...

>--ex2:
>FROM Aa left outer join Bb
>on (Aa.fn1 = Bb.fn1 AND Aa.fn2 = Bb.fn2)
>where ...

You probably need to move the conditions of the WHERE clause
that pertains to Bb to the ON part. If you have them in the
WHERE clause, they are all NULL for the rows in Aa that are
not in Bb, so these conditions evaluate to false.

--



Fri, 11 Jul 2003 06:59:36 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Outer Join Syntax (SQL Server and Oracle)

2. v8.1.5: LEFT OUTER JOINs syntax

3. Outer Join Syntax

4. Outer Join Syntax

5. Choosing Outer Join syntax

6. OUTER joins SYNTAX

7. outer join syntax differences

8. left outer join syntax

9. Oracle 7.34, 8.x, full outer join syntax

10. new outer join syntax

11. Version 7 bug with old outer join syntax???


 
Powered by phpBB® Forum Software