[ALUG] MySQL Joins

Jim Rippon jim at rippon.me.uk
Wed Jun 3 13:43:33 BST 2009


Mark, 

>What I have so far is: 
>SELECT tbl1.email AS email1, tbl2.email AS email2 
>SELECT IF(tbl1.email IS NOT NULL, tbl1.email, tbl2.email) as email 
>FROM tbl1 LEFT JOIN tbl2 USING (email) 
>
>.. which will get me all the addresses from tbl1, with the matching 
>address from tbl2 in email2 or NULL if there is no match. 
>
>What I want is to also get all the addresses in tbl2, with NULL against 
>email1 where there is no match. That is what I thought an OUTER join did 
>but it seems I'm wrong. 

An OUTER join can be a LEFT or RIGHT join like this:

SELECT tbl1.email AS email1, tbl2.email AS email2
FROM tbl1 LEFT OUTER JOIN email2 USING (email);

will show you all the records from the left table (in this case tbl1) and NULL for any records that didn't appear in the right table (tbl2).

Perhaps what you want is a union query:

SELECT DISTINCT email FROM tbl1 UNION SELECT DISTINCT email FROM tbl2 ORDER BY email;

This should give you one result for each distinct entry between the two tables, if I understand it correctly.  Worked for my (admittedly very small) test db.

Hope this helps,

Jim



More information about the main mailing list