The answer given by

Pablo Santa Cruzis correct; however, if anyone stumbles upon this page and needs more clarification, here's a detailed breakdown.

Example table

Suppose we have the following table:

-- t1 id name 1 Tim 2 Marta -- t2 id name 1 Tim 3 Katarina

Inner join

Internal joins, as shown below:

SELECT * FROM `t1` INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

will only allow us to see records that appear in both tables, as shown below:

1 Tim 1 Tim

Inner joins have no direction (e.g. left or right) because they are explicitly bidirectional - we need both sides to match.

OUTER JOIN

On the other hand, outer joins are used to find records that may not match in another table. Therefore, you must specify which side of theconnection is allowed to have missing records.

LEFT JOINandRIGHT JOINare shorthand forLEFT OUTER JOINandRIGHT OUTER JOIN; I will use theirs below Full name to reinforce the concept of outer joins and inner joins.

Left Outer Join

Left outer join, as shown below:

SELECT * FROM `t1` LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

... will get all records from the left table regardless of whether they have a match in the right table, like this:

1 Tim 1 Tim 2 Marta NULL NULL

Right outer join

Right outer join, as shown below:

SELECT * FROM `t1` RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

...will get all records from the right table regardless of whether they have a match in the left table, like this:

1 Tim 1 Tim NULL NULL 3 Katarina

Full outer join

A complete outer join will give us all records from both tables regardless of whether they have a match in the other table, or NULL on both sides if not. The result is as follows:

1 Tim 1 Tim 2 Marta NULL NULL NULL NULL 3 Katarina

However, as Pablo Santa Cruz pointed out, MySQL does not support this. We can emulate it with a UNION of a left join and a right join, like this:

SELECT * FROM `t1` LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id` UNION SELECT * FROM `t1` RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

You can think of

UNIONas "run these two queries and then stack the results together"; some rows will come from the first query and some from the second.

It is important to note that

UNIONin MySQL will eliminate exact duplicates: Tim will appear in both queries here, but the results forUNIONwill only be listed Him once. My fellow database experts believe this behavior should not be relied upon. So, to make this more explicit, we can add aWHEREclause to the second query:

SELECT * FROM `t1` LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id` UNION SELECT * FROM `t1` RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id` WHERE `t1`.`id` IS NULL;

On the other hand, if for some reason you

want tosee duplicates, you can useUNION ALL.

You don't havefull connectionsin MySQL, but you can sureemulate them.

For code transcribed fromthis Stack Overflow questionExampleYou have:

There are two tables t1 and t2:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id

---

The above query is suitable for the special case wherecomplete outer joinoperation will not produce any duplicate rows. The above query relies on the UNION set operator to remove duplicate rows introduced by the query pattern. We can avoid introducing duplicate rows by using theanti-joinpattern on the second query, and then using the UNION ALL set operator to combine the two sets. In the more general case where a full outer join will return duplicate rows, we can do this:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id UNION ALL SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id WHERE t1.id IS NULL