danm
Intermediate
Joined: 29 Jun 2004
Posts: 170
Topics: 73
|
 Posted: Fri Jun 16, 2006 11:07 am Post subject: Outer Join |
 |
|
| Code: |
Table1:
COL1 COL2
---- ---
XX A
XX B
XX C
XX D
YY A
YY C
Table2:
COLA COLB
---- ---
A 1
C 2
D 3
I only want the rows where Col1 = 'XX' from Table1.
For Inner Join, either one of these two queries works:
SELECT T1.COL1, T1.COL2, T2.COLA, T2.COLB
FROM TABLE1 T1
INNER JOIN
TABLE3 T2
ON T1.COL2 = T2.COLA
WHERE T1.COL1 = 'XX' <-- Key word WHERE
ORDER BY T1.COL1, T1.COL2;
SELECT T1.COL1, T1.COL2, T2.COLA, T2.COLB
FROM TABLE1 T1
INNER JOIN
TABLE3 T2
ON T1.COL2 = T2.COLA
AND T1.COL1 = 'XX' <-- Key word AND
ORDER BY T1.COL1, T1.COL2;
COL1 COL2 COLA COLB
---- ---- ---- ----
XX A A 1
XX C C 2
XX D D 3
For Outer join, only the one with WHERE works:
SELECT T1.COL1, T1.COL2, T2.COLA, T2.COLB
FROM TABLE1 T1
LEFT OUTER JOIN
TABLE3 T2
ON T1.COL2 = T2.COLA
WHERE T1.COL1 = 'XX' <-- Key word 'WHERE'
ORDER BY T1.COL1, T1.COL2;
COL1 COL2 COLA COLB
---- ---- ---- ----
XX A A 1
XX B - -
XX C C 2
XX D D 3
SELECT T1.COL1, T1.COL2, T2.COLA, T2.COLB
FROM TABLE1 T1
LEFT OUTER JOIN
TABLE3 T2
ON T1.COL2 = T2.COLA
AND T1.COL1 = 'XX' <-- Key word AND
ORDER BY T1.COL1, T1.COL2;
COL1 COL2 COLA COLB
---- ---- ---- ----
XX A A 1
XX B - -
XX C C 2
XX D D 3
YY A - -
YY C - -
I don't understand why YY in col1 is not eliminated, and ColA and Colb = NULL.
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
