Outer Join

[desi5pure]

Does anyone know how to do outer left and our right joins in a same query?
If there are two tables, I want to get records from both table whether there is a match or not. For example, we would join on A-ID and B-ID

TableA Table B
A-ID A-Name B-ID B-Name
1 AA 1 BA
2 AB 3 BB

I want output like

A-ID B-ID A-Name B-Name
1 1 AA BA
2 NULLS AB NULLS
NULS 3 NULLS BB

 

[PHV]

SELECT A.ID, B.ID, A.Name, B.Name
FROM A LEFT JOIN B ON A.ID = B.ID
UNION SELECT A.ID, B.ID, A.Name, B.Name
FROM A RIGHT JOIN B ON A.ID = B.ID
WHERE A.ID Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

SELECT a.ID   AS A_ID
     , a.name AS A_name
     , b.ID   AS B_ID
     , b.name AS B_name
  FROM tableA AS a
[red]LEFT OUTER[/red]
  JOIN tableB AS b
    ON b.ID = a.ID
[red]UNION[/red]
SELECT a.ID   AS A_ID
     , a.name AS A_name
     , b.ID   AS B_ID
     , b.name AS B_name
  FROM tableA AS a
[red]RIGHT OUTER[/red]
  JOIN tableB AS b
    ON b.ID = a.ID

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[desi5pure]

Thanks! One question--since we are joining (union) left outer join and right outer join, wouldn't this double records that are in both tables? Bother outer quesries would include records in both tables.

 

[r937]

desi, that's mighty insightful of you

you're totally right, both the LEFT OUTER JOIN and the RIGHT OUTER JOIN return matching rows

however, UNION eliminates the duplicates

neat, eh? ;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[desi5pure]

Thanks! Forgot all about UNION. I tried and it and it works like a charm.

Once again, thanks to all for responding. This is a great site; I learn a lot from this site through everyone's participation.

 

[lespaul]

but if for some reason you WANT the duplicates then you use UNION ALL.....



Leslie

Come join me at New Mexico Linux Fest!