Re: JOINs verses ='s ?

From: bpfh <daniel_page_at_yahoo.fr>
Date: Tue, 10 Apr 2001 14:23:11 +0200
Message-ID: <9autr6$tkj$1_at_s1.read.news.oleane.net>


Guten Tag Heinz und viel danke fur die hilfe mit SQL !

(I believe that this is correct!!)

Cordially,

Daniel Page
"Heinz Huber" <Heinz.Huber_at_elbanet.co.at> a écrit dans le message news: 3AD18F2D.98E8417A_at_elbanet.co.at...
> Salut Daniel,
>
> bpfh wrote:
> >
> > Hi,
> >
> > I am being pushed by the management to learn SQL on my own - and now, I
 am
> > (maybe) stumped with left / right / outer joins...
> >
> > Let's say that I have 2 tables with 2 cols :
> > Table #1 - id (pri. key), fn
> > Table #2 - id (pri.key), ln
> >
> > Now, my own SQL instruction would be:
> > select table1.fn, table2.ln FROM table1, table2 WHERE table1.id =
 table2.id
> > AND table1.id = 1;
> >
> > Would I be correct in extrapolating this to:
> >
> > select table1.fn, table2.ln FROM table1, table2 WHERE table1.id LEFT
 JOIN
> > table2.id AND table1.id = 1; ?
> >
> > If not, could anyone give me a quick overview of left / right / outer
 joins
> > in an SQL instruction?
>
> The second statement you wrote above is not correct SQL since the JOIN
> syntax is part of the FROM clause, not the WHERE clause.
>
>
> The kinds of joins I know are:
>
> Cross join: cartesian product of two tables (taking each row of table1
> and appending each row of table2).
>
> Inner join: "normal" join (take each row of table1 matching certain
> criteria and append each row of table2 matching certain criteria).
>
> Outer join: "preserving" join (take each row of table1 and append each
> row of table2 matching certain criteria); here the difference between
> LEFT and RIGHT (and possibly FULL) is which table is going to be
> preserved (both in case of FULL); the columns of the non preserved table
> will be filled with null values if there is no matching row in the non
> preserved table.
>
>
> The ways to specify the criteria (or conditions) I know are:
> Natural: match columns with the same name
> Key: match PK/FK columns
> ON: explicit condition(s) after the JOIN statement
> WHERE clause: specify condition(s) in WHERE clause
>
>
> OK, now to make the whole thing a bit more perceivable:
>
> Table A
> idA (PK) valueA
> ------------------------------------------------
> 1 a
> 2 b
> 3 c
>
>
> Table B
> idB (PK) idA (FK) valueB
> ------------------------------------------------
> 1 1 Z
> 4 2 Y
> 5 6 X
>
>
> Cross join:
> SELECT * FROM A, B;
> idA valueA idB idA valueB
> ------------------------------------------------
> 1 a 1 1 Z
> 1 a 4 2 Y
> 1 a 5 6 X
> 2 b 1 1 Z
> 2 b 4 2 Y
> 2 b 5 6 X
> 3 c 1 1 Z
> 3 c 4 2 Y
> 3 c 5 6 X
>
>
> Inner join:
> SELECT * FROM A NATURAL JOIN B; match A.idA and B.idB (same name)
> SELECT * FROM A KEY JOIN B; match A.idA and B.idB (PK/FK)
> SELECT * FROM A JOIN B ON A.idA = B.idA; explicit
> SELECT * FROM A JOIN B WHERE A.idA = B.idA;
> idA valueA idB idA valueB
> ------------------------------------------------
> 1 a 1 1 Z
> 2 b 4 2 Y
> It might be that a natural join eliminates one of the idA columns.
>
>
> Left outer join (all four ways like inner join possible):
> SELECT * FROM A LEFT OUTER JOIN B ON A.idA = B.idA;
> idA valueA idB idA valueB
> ------------------------------------------------
> 1 a 1 1 Z
> 2 b 4 2 Y
> 3 c null null null
>
> Right outer join (all four ways like inner join possible):
> SELECT * FROM A RIGHT OUTER JOIN B ON A.idA = B.idB;
> idA valueA idB idA valueB
> ------------------------------------------------
> 1 a 1 1 Z
> 2 b 4 2 Y
> null null 5 6 X
>
> Full outer join (all four ways like inner join possible):
> SELECT * FROM A FULL OUTER JOIN B ON A.idA = b.idB;
> idA valueA idB idA valueB
> ------------------------------------------------
> 1 a 1 1 Z
> 2 b 4 2 Y
> 3 c null null null
> null null 5 6 X
>
>
> I hope this gives you an idea of the different kinds of join and how to
> use them.
>
> hth,
> Heinz
Received on Tue Apr 10 2001 - 14:23:11 CEST

Original text of this message