How can I do a FULL OUTER JOIN in MySQL?

I want to do a *[full outer join][1]* in MySQL. Is this possible? Is a *full outer join* supported by MySQL?

[1]:

[To see links please register here]

SELECT
a.name,
b.title
FROM
author AS a
LEFT JOIN
book AS b
ON a.id = b.author_id
UNION
SELECT
a.name,
b.title
FROM
author AS a
RIGHT JOIN
book AS b
ON a.id = b.author_id

It is also possible, but you have to mention the same field names in select.

SELECT t1.name, t2.name FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT t1.name, t2.name FROM t2
LEFT JOIN t1 ON t1.id = t2.id

In SQLite you should do this:

SELECT *
FROM leftTable lt
LEFT JOIN rightTable rt ON lt.id = rt.lrid
UNION
SELECT lt.*, rl.* -- To match column set
FROM rightTable rt
LEFT JOIN leftTable lt ON lt.id = rt.lrid

You can do the following:

(SELECT
*
FROM
table1 t1
LEFT JOIN
table2 t2 ON t1.id = t2.id
WHERE
t2.id IS NULL)
UNION ALL
(SELECT
*
FROM
table1 t1
RIGHT JOIN
table2 t2 ON t1.id = t2.id
WHERE
t1.id IS NULL);

You can just convert a full outer join, e.g.

SELECT fields
FROM firsttable
FULL OUTER JOIN secondtable ON joincondition

into:

SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields (replacing any fields from firsttable with NULL)
FROM secondtable
WHERE NOT EXISTS (SELECT 1 FROM firsttable WHERE joincondition)

Or if you have at least one column, say `foo`, in `firsttable` that is NOT NULL, you can do:

SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields
FROM firsttable
RIGHT JOIN secondtable ON joincondition
WHERE firsttable.foo IS NULL

I modified [shA.t's query][1] for more clarity:

-- t1 left join t2
SELECT t1.value, t2.value
FROM t1 LEFT JOIN t2 ON t1.value = t2.value

UNION ALL -- include duplicates

-- t1 right exclude join t2 (records found only in t2)
SELECT t1.value, t2.value
FROM t1 RIGHT JOIN t2 ON t1.value = t2.value
WHERE t1.value IS NULL


[1]:

[To see links please register here]

None of the previous answers are actually correct, because they do not follow the semantics when there are duplicated values.

For a query such as (from [this duplicate][1]):

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.Name = t2.Name;

The correct equivalent is:

SELECT t1.*, t2.*
FROM (SELECT name FROM t1 UNION -- This is intentionally UNION to remove duplicates
SELECT name FROM t2
) n LEFT JOIN
t1
ON t1.name = n.name LEFT JOIN
t2
ON t2.name = n.name;

If you need this to work with `NULL` values (which may also be necessary), then use the `NULL`-safe comparison operator, `<=>` rather than `=`.

[1]:

[To see links please register here]

I fix the response, and works include all rows (based on the response of Pavle Lekic):

(
SELECT a.* FROM tablea a
LEFT JOIN tableb b ON a.`key` = b.key
WHERE b.`key` is null
)
UNION ALL
(
SELECT a.* FROM tablea a
LEFT JOIN tableb b ON a.`key` = b.key
where a.`key` = b.`key`
)
UNION ALL
(
SELECT b.* FROM tablea a
right JOIN tableb b ON b.`key` = a.key
WHERE a.`key` is null
);

Use a [cross join][1] solution:

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2
ON 1=1;

[1]:

[To see links please register here]