2

I've a following query of 'Full Outer Join':

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

As per my knowledge The purpose of 'Full Outer Join' in MySQL is to return all the rows from the left table (Customers), and all the rows from the right table (Orders).

If there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.

My doubt/query is if the 'Full Outer Join' in MySQL is going to return rows from both the tables irrespective of a matching element then is it necessary to specify a condition in WHERE clause? Can't I skip it?

Please give me canonical and to the point answer with respect to MySQL RDBMS.

Thanks.

PHPLover
  • 1
  • 51
  • 158
  • 311
  • 1
    There is no `FULL JOIN` in MySQL and you don't need to specify a `WHERE` clause, although there is nothing a priori preventing you from doing this. – Tim Biegeleisen Oct 18 '15 at 10:02

1 Answers1

3

When you specify something in the WHERE clause you have to make sure, that you check for NULL values, too, like this:

...
WHERE (col1 = 'whatever' OR col1 IS NULL)
AND (col2 = 'other_value' OR col2 IS NULL)
...

If you don't check for NULL your FULL OUTER JOIN actually becomes an INNER JOIN or LEFT/RIGHT JOIN.

If you had a typo and meant "why specify a condition in the ON clause?", then the answer is that without an ON clause your JOIN is actually a CROSS JOIN, which would mean, that every row of one table is associated with every row from the other table, which is a totally different thing.

To make this answer more MySQL specific, there is actually no FULL OUTER JOIN in MySQL and you have to emulate it with LEFT JOIN ... UNION ... RIGHT JOIN. But still the same principal applies. 

a left join b using (id)
where b.col1 = 'x'

is the same as 

a inner join b using (id)
where b.col1 = 'x'

and you actually have to write it like

a left join b on a.id = b.id and b.col1 = 'x'

to really have a left join. Or of course

a left join b using (id)
where b.col1 = 'x' or b.col1 is null
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • I've always been told I have a large head. Now this is hitting below the belt ;-) – Tim Biegeleisen Oct 18 '15 at 10:14
  • Interesting...so by using a `WHERE` clause you implicitly admit a null check into the condition. – Tim Biegeleisen Oct 18 '15 at 10:16
  • My general feeling and observation is that there a ton of nubes here who don't even spend 5 minutes searching SO or Google and just dump a question here. So I have come to be trigger happy with regard to closing questions but I will pull back a bit. Thanks for the feedback. – Tim Biegeleisen Oct 18 '15 at 10:26