How to join two tables and show unmatched rows for BOTH tables

Say I want to join table1 and table2. If I do "SELECT * FROM table1 LEFT JOIN table2 USING(SomeField)" any rows that exist in table1 yet do not exist in table2 will still show up as well as matched rows, which is good. But any rows that exists in table2 yet not exist in table 1 will not.

I want to be able to show any rows that do not exists in table1 and yet exists in table2 and any rows that exist in table2 yet not exist in talbe1, along with all the matched rows. Please help.
krupiniAsked:
Who is Participating?
 
snoyes_jwCommented:
"FULL OUTER" doesn't seem to work in MySQL, at least not as of version 5.0.

You can do a left join and a right join and combine the two with union:
SELECT * FROM table1 LEFT JOIN table2 USING (someField) UNION SELECT * FROM table1 RIGHT JOIN table2 USING (someField);
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
SELECT * FROM table1 FULL OUTER JOIN table2 USING(SomeField)
0
 
krupiniAuthor Commented:
I tried and it didnt work. It says in MySQL manual that FULL OUTER JOIN is not supported.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
indeed, full outer join is (still) not available, even in 5.1
http://dev.mysql.com/doc/refman/5.1/en/join.html
:-(

I am using  MSSQL Server too much ...
0
 
sriramiyerCommented:
Hi krupini,

The only way I can think to make it work is through a UNION.

(
SELECT <column list>
FROM table a
LEFT JOIN table b
        ON a.keycol = b.keycol
) UNION (
SELECT <column list>
FROM table b
LEFT JOIN table a
        ON b.keycol = a.keycol
WHERE a.keycol is null
)


The UNION will only return the columns you ask it to. This method also
avoids manually creating a temporary table. So long as you do not say
UNION ALL, you won't get any duplicate rows.

Hope it works!
From
SriRamIyer.
0
 
krupiniAuthor Commented:
Hmm, I tried them all, none of the seem to do the trick. Maybe if I explain what I am trying to do then all will become clear.

I have a simple sales table

+----+------------+--------+
| id | saleDate   | amount |
+----+------------+--------+
|  1 | 2005-01-01 |   2.25 |
|  2 | 2005-01-02 |   2.27 |
|  3 | 2006-01-01 |   3.55 |
|  4 | 2006-03-01 |   7.88 |
+----+------------+--------+

I am trying to produce a comparative year over year sales summary. For example, I want to know how much was sold on day x in the year 2006, and on the same day in the year 2005. So after executing the query, I should have something like:

+--------------+------------+--------------+------------+-----------------------+
| currSaleDate | currAmount | prevSaleDate | prevAmount | amountDiff |
+--------------+------------+--------------+------------+-----------------------+
| 2006-03-01   |       7.88     | NULL             |       NULL      |       7.88 |
| 2006-01-01   |       3.55     | 2005-01-01    |       2.25      |        1.3 |
| NULL            |       NULL     | 2005-01-02   |       2.27       |      -2.27 |
+--------------+------------+--------------+------------+-----------------------+

My query thus far:

(
SELECT currSales.saleDate AS currSaleDate, currSales.amount AS currAmount,
prevSales.saleDate AS prevSaleDate, prevSales.amount AS prevAmount
FROM sales currSales
LEFT JOIN sales prevSales ON (DAYOFYEAR(currSales.saleDate) = DAYOFYEAR(prevSales.saleDate))
WHERE currSales.saleDate BETWEEN '2006-01-01' AND '2006-12-31'
)
UNION
(
SELECT prevSales.saleDate AS prevSaleDate, prevSales.amount AS prevAmount,
currSales.saleDate AS currSaleDate, currSales.amount AS currAmount
FROM sales prevSales
LEFT JOIN sales currSales ON (DAYOFYEAR(prevSales.saleDate) = DAYOFYEAR(currSales.saleDate))
WHERE currSales.saleDate is NULL AND prevSales.saleDate BETWEEN '2005-01-01' AND '2005-12-31'
)

And it produces:


+--------------+------------+--------------+-----------------------+
| currSaleDate | currAmount | prevSaleDate | prevAmount |
+--------------+------------+--------------+-----------------------+
| 2006-01-01   |       3.55 | 2005-01-01        |       2.25 |
| 2006-01-01   |       3.55 | 2006-01-01        |       3.55 |
| 2006-03-01   |       7.88 | 2006-03-01        |       7.88 |
+--------------+------------+--------------+-----------------------+

Please help.

0
 
snoyes_jwCommented:
Don't you need to extend the where clause to put the prevSales in 2005 for the first select, and currSales in 2006 for the second?
0
 
krupiniAuthor Commented:
If I do

(
SELECT currSales.saleDate AS currSaleDate, currSales.amount AS currAmount,
prevSales.saleDate AS prevSaleDate, prevSales.amount AS prevAmount
FROM sales currSales
LEFT JOIN sales prevSales ON (DAYOFYEAR(currSales.saleDate) = DAYOFYEAR(prevSales.saleDate))
WHERE currSales.saleDate BETWEEN '2006-01-01' AND '2006-12-31'
AND prevSales.saleDate BETWEEN '2005-01-01' AND '2005-12-31'
)
UNION
(
SELECT prevSales.saleDate AS prevSaleDate, prevSales.amount AS prevAmount,
currSales.saleDate AS currSaleDate, currSales.amount AS currAmount
FROM sales prevSales
LEFT JOIN sales currSales ON (DAYOFYEAR(prevSales.saleDate) = DAYOFYEAR(currSales.saleDate))
WHERE currSales.saleDate is NULL AND prevSales.saleDate BETWEEN '2005-01-01' AND '2005-12-31'
AND currSales.saleDate BETWEEN '2006-01-01' AND '2006-12-31'
)

I get:

+--------------+------------+--------------+----------------------+
| currSaleDate | currAmount | prevSaleDate | prevAmount |
+--------------+------------+--------------+----------------------+
| 2006-01-01   |       3.55     | 2005-01-01    |       2.25      |
+--------------+------------+--------------+----------------------+
0
 
krupiniAuthor Commented:
Ahh, but this:

SELECT currSales.saleDate AS currSaleDate, currSales.amount AS currAmount,
prevSales.saleDate AS prevSaleDate, prevSales.amount AS prevAmount,
(COALESCE(currSales.amount, 0) - COALESCE(prevSales.amount, 0)) AS amountDiff
FROM sales currSales
LEFT JOIN sales prevSales ON (DAYOFYEAR(currSales.saleDate) = DAYOFYEAR(prevSales.saleDate)
AND currSales.saleDate > prevSales.saleDate)
WHERE currSales.saleDate BETWEEN '2006-01-01' AND '2006-12-31'
UNION
SELECT currSales.saleDate AS currSaleDate, currSales.amount AS currAmount,
prevSales.saleDate AS prevSaleDate, prevSales.amount AS prevAmount,
(COALESCE(currSales.amount, 0) - COALESCE(prevSales.amount, 0)) AS amountDiff
FROM sales currSales
RIGHT JOIN sales prevSales ON (DAYOFYEAR(currSales.saleDate) = DAYOFYEAR(prevSales.saleDate)
AND currSales.saleDate > prevSales.saleDate)
WHERE prevSales.saleDate BETWEEN '2005-01-01' AND '2005-12-31'


Beauty:
+--------------+------------+--------------------------+------------+------------+
| currSaleDate | currAmount | prevSaleDate | prevAmount | amountDiff |
+--------------+------------+-------------------------+------------+------------+
| 2006-01-01   |       3.55     | 2005-01-01    |       2.25      |        1.3 |
| 2006-03-01   |       7.88     | NULL             |       NULL      |       7.88 |
| NULL            |       NULL     | 2005-01-02    |       2.27      |      -2.27 |
+--------------+------------+-------------------------+------------+------------+
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.