+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: MySQL (https://0day.red/Forum-MySQL)
+---- Thread: How can I do a FULL OUTER JOIN in MySQL? (/Thread-How-can-I-do-a-FULL-OUTER-JOIN-in-MySQL)
Pages:
1
2
How can I do a FULL OUTER JOIN in MySQL? - linhdgjva - 07-27-2023
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]
RE: How can I do a FULL OUTER JOIN in MySQL? - daphenehnzvxd - 07-27-2023
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
RE: How can I do a FULL OUTER JOIN in MySQL? - erudite482 - 07-27-2023
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
RE: How can I do a FULL OUTER JOIN in MySQL? - leasehold822 - 07-27-2023
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
RE: How can I do a FULL OUTER JOIN in MySQL? - Mrmesomorph7 - 07-27-2023
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);
RE: How can I do a FULL OUTER JOIN in MySQL? - Mrreesexrwurepr - 07-27-2023
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
RE: How can I do a FULL OUTER JOIN in MySQL? - anamorphoscope572 - 07-27-2023
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]
RE: How can I do a FULL OUTER JOIN in MySQL? - xuanafppcl - 07-27-2023
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]
RE: How can I do a FULL OUTER JOIN in MySQL? - grassland22521 - 07-27-2023
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
);
RE: How can I do a FULL OUTER JOIN in MySQL? - effieeffigy44 - 07-27-2023
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]