By Spencer


2011-01-25 17:34:18 8 Comments

I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?

14 comments

@Pablo Santa Cruz 2011-01-25 17:38:14

You don't have FULL JOINS on MySQL, but you can sure emulate them.

For a code SAMPLE transcribed from this SO question you have:

with two tables t1, 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 query above works for special cases where a FULL OUTER JOIN operation would not produce any duplicate rows. The query above depends on the UNION set operator to remove duplicate rows introduced by the query pattern. We can avoid introducing duplicate rows by using an anti-join pattern for the second query, and then use a UNION ALL set operator to combine the two sets. In the more general case, where a FULL OUTER JOIN would 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

@Pavle Lekic 2013-03-19 18:41:23

Actually the thing you wrote is not correct. Because when you do a UNION you will remove duplicates, and sometimes when you join two different tables there should be duplicates.

@Pavle Lekic 2013-03-19 18:45:21

This is the correct example: (SELECT ... FROM tbl1 LEFT JOIN tbl2 ...) UNION ALL (SELECT ... FROM tbl1 RIGHT JOIN tbl2 ... WHERE tbl1.col IS NULL)

@Pavle Lekic 2013-03-19 18:49:17

So the difference is that I am doing a left inclusive join and then right exclusive using UNION ALL

@Nikola Bogdanović 2013-11-14 20:13:43

@PavleLekic: you are simply wrong on all accounts, as you never want any duplicates in a FULL OUTER JOIN (otherwise it's a completely different type of join)

@Nikola Bogdanović 2013-11-14 20:26:56

@PavleLekic: his example is quite correct and your's is just the exact same thing (although more efficient, as UNION ALL is much faster than a distinct UNION - tbl1.col should be the join column tbl1.id) - there is no difference in results whatsoever

@Nikola Bogdanović 2013-11-14 20:47:04

@PavleLekic: the only way his answer could be wrong is if the join column is not unique (there would be fewer results than in your example) - but you should never do that in a FULL OUTER JOIN anyway

@ysth 2014-03-31 21:04:07

@NikolaBogdanović: there certainly is a difference if what you are joining on is not a unique key. say t2 had two rows with the same id and t1 had zero or one row with that id; your UNION query only gives one row of results; the correct query 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 gives two.

@ysth 2014-03-31 21:09:31

and I see now that you say that yourself, sorry. Perhaps you could update your answer, given there is this case that it gets wrong and that the UNION ALL is always going to be more efficient?

@Mark Amery 2014-08-31 15:08:25

@PavleLekic Your criticism is valid, but your 'correct' example is unclear (how do we choose which column to check for NULL in the WHERE clause?) and still fails to perfectly emulate an outer join. For example, it can fail if you have an eccentric join condition, like joining on all the columns of both tables being null.

@jarlh 2014-12-30 11:22:30

create table t1 (id int); create table t2 (id int); insert into t1 values (1); insert into t1 values (1); Then the correct result has two rows. This solution with UNION removes duplicates, i.e. it doesn't work correctly in all cases.

@ypercubeᵀᴹ 2015-04-11 17:33:48

@jarth. You are right, the UNION version may result in wrong result, but only if one of the 2 tables has no primary key or unique constraints. (in the "relational" sense, one can claim they are not proper tables in that case ;)

@spencer7593 2015-05-07 14:54:23

@ypercube: If there no duplicate rows in t1 and t2, the query in this answer does return a resultset that emulates FULL OUTER JOIN. But in the more general case, for example, the SELECT list doesn't contain sufficient columns/expressions to make the returned rows unique, then this query pattern is insufficient to reproduce the set that would be produced by a FULL OUTER JOIN. To get a more faithful emulation, we'd need a UNION ALL set operator, and one of the queries would need an anti-join pattern. The comment from Pavle Lekic (above) gives the correct query pattern.

@spencer7593 2015-05-07 15:07:14

@MarkAmery asked how we choose which column (or expression) to check for NULL in the anti-join? We simply choose a column (or expression) in the returned set that would be guaranteed to be non-NULL if a matching row was found. And that's fairly easy to do, if the join predicate is an equality comparison on a column, then we are guaranteed that any matching row returned will have a non-NULL value in that column. The only rows that would have a NULL value would be rows that didn't have a match. (If the join predicate matches on NULL values, we have to find an expression that is non-NULL)

@ypercubeᵀᴹ 2015-05-07 16:06:45

@spencer7593 not sure why you pinged me ;)

@still_dreaming_1 2015-11-12 15:01:02

How do you combine this with an ORDER BY? It is complaining that I can't use those tables in my global ORDER.

@still_dreaming_1 2015-11-12 15:09:34

I figured it out. I just had to only use the column names instead of the table name and column name in the ORDER BY.

@still_dreaming_1 2015-11-12 15:44:55

@PavleLekic At least on my system, a UNION ALL always creates extra duplicate rows even if the two tables do not have any duplicates and enforce that through primary keys. You always need to use UNION without the ALL if you want to get unique rows. This makes sense if you just think about what UNION and UNION ALL do without thinking about trying to emulate a full outer join. To illustrate this, in the case where every row just happens to have a matching row in the other table (matching as in the id field used for the on clause is the same), UNION ALL will select twice as many rows as UNION.

@Cruncher 2017-01-19 14:50:10

if t1 and t2 are anonymous queries, is there a way I can format this query without having to repeat the select for t1 and t2?

@fthiella 2017-03-31 08:24:43

an UNION query would fail if one of the 2 tables has no primary key or unique constraints, but it would also fail if the SELECT is returning just a subset of columns instead of *. I understand that the UNION query is supposed to work and if it fails there's a problem in "relational terms" elsewhere, but I think that the general way to reproduce a FULL OUTER JOIN is an UNION ALL query with an anti-alias pattern

@fthiella 2017-04-03 12:45:17

I rolled back to the original answer (union all without anti-join pattern is plain wrong while an union query is somehow, but not always, correct...I still consider the right answer an union all with an anti-join pattern on either the first or the second query...)

@tom10271 2018-04-16 08:48:13

I got Can't reopen table

@Akshayraj Kore 2017-10-12 19:22:27

MySql does not have FULL-OUTER-JOIN syntax. You have to emulate by doing both LEFT JOIN and RIGHT JOIN as follows-

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

But MySql also does not have a RIGHT JOIN syntax. According to MySql's outer join simplification, the right join is converted to the equivalent left join by switching the t1 and t2 in the FROM and ON clause in the query. Thus, the MySql Query Optimizer translates the original query into the following -

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

Now, there is no harm in writing the original query as is, but say if you have predicates like the WHERE clause, which is a before-join predicate or an AND predicate on the ON clause, which is a during-join predicate, then you might want to take a look at the devil; which is in details.

MySql query optimizer routinely checks the predicates if they are null-rejected. Null-Rejected Definition and Examples Now, if you have done the RIGHT JOIN, but with WHERE predicate on the column from t1, then you might be at a risk of running into a null-rejected scenario.

For example, THe following query -

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'

gets translated to the following by the Query Optimizer-

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id
WHERE t1.col1 = 'someValue'

So the order of tables has changed, but the predicate is still applied to t1, but t1 is now in the 'ON' clause. If t1.col1 is defined as NOT NULL column, then this query will be null-rejected.

Any outer-join (left, right, full) that is null-rejected is converted to an inner-join by MySql.

Thus the results you might be expecting might be completely different from what the MySql is returning. You might think its a bug with MySql's RIGHT JOIN, but thats not right. Its just how the MySql query-optimizer works. So the developer-in-charge has to pay attention to these nuances when he is constructing the query.

@philipxy 2018-08-11 22:09:28

The SQL standard says full join on is inner join on rows union all unmatched left table rows extended by nulls union all right table rows extended by nulls. Ie inner join on rows union all rows in left join on but not inner join on union all rows in right join on but not inner join on.

Ie left join on rows union all right join on rows not in inner join on. Or if you know your inner join on result can't have null in a particular right table column then "right join on rows not in inner join on" are rows in right join on with the on condition extended by and that column is null.

Ie similarly right join on union all appropriate left join on rows.

From What is the difference between “INNER JOIN” and “OUTER JOIN”?:

(SQL Standard 2006 SQL/Foundation 7.7 Syntax Rules 1, General Rules 1 b, 3 c & d, 5 b.)

@Super Mario 2017-06-13 19:53:53

what'd you say about Cross join solution?

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

@Marc L. 2017-07-24 19:10:37

No, this is a cross join. It will match every row in t1 to every row in t2, yielding the set of all possible combinations, with select (select count(*) from t1) * (select count(*) from t2)) rows in the result set.

@Alexander 2018-03-27 03:13:14

While this code may answer the question, providing additional context regarding how and why it solves the problem would improve the answer's long-term value.

@Super Mario 2018-03-29 12:51:01

Which addition may be helpful? maybe en example?

@lolo 2018-06-05 07:54:37

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);

@Sterica Capatina 2019-01-24 18:32:42

This working! Thank you!

@shA.t 2015-05-28 05:01:38

Using a union query will remove duplicates, and this is different than the behavior of full outer join that never removes any duplicate:

[Table: t1]                            [Table: t2]
value                                  value
-------                                -------
1                                      1
2                                      2
4                                      2
4                                      5

This is the expected result of full outer join:

value | value
------+-------
1     | 1
2     | 2
2     | 2
Null  | 5
4     | Null
4     | Null

This is the result of using left and right Join with union:

value | value
------+-------
Null  | 5 
1     | 1
2     | 2
4     | Null

[SQL Fiddle]

My suggested query is:

select 
    t1.value, t2.value
from t1 
left outer join t2  
  on t1.value = t2.value
union all      -- Using `union all` instead of `union`
select 
    t1.value, t2.value
from t2 
left outer join t1 
  on t1.value = t2.value
where 
    t1.value IS NULL 

Result of above query that is as same as expected result:

value | value
------+-------
1     | 1
2     | 2
2     | 2
4     | NULL
4     | NULL
NULL  | 5

[SQL Fiddle]


@Steve Chambers: [From comments, with many thanks!]
Note: This may be the best solution, both for efficiency and for generating the same results as a FULL OUTER JOIN. This blog post also explains it well - to quote from Method 2: "This handles duplicate rows correctly and doesn’t include anything it shouldn’t. It’s necessary to use UNION ALL instead of plain UNION, which would eliminate the duplicates I want to keep. This may be significantly more efficient on large result sets, since there’s no need to sort and remove duplicates."


I decided to add another solution that comes from full outer join visualization and math, it is not better that above but more readable:

Full outer join means (t1 ∪ t2): all in t1 or in t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only: all in both t1 and t2 plus all in t1 that aren't in t2 and plus all in t2 that aren't in t1:

-- (t1 ∩ t2): all in both t1 and t2
select t1.value, t2.value
from t1 join t2 on t1.value = t2.value    
union all  -- And plus 
-- all in t1 that not exists in t2
select t1.value, null
from t1
where not exists( select 1 from t2 where t2.value = t1.value)    
union all  -- and plus
-- all in t2 that not exists in t1
select null, t2.value
from t2
where not exists( select 1 from t1 where t2.value = t1.value)

[SQL Fiddle]

@Kabir Hossain 2015-10-13 07:57:50

We are doing same task tow times , If there are sub query for t1 and t2 then mysql have to do same task more times, is not it ? Can we remove this using alias in this situation ?:

@shA.t 2015-10-13 09:20:09

I suggest you to use some temporary tables ;).

@Steve Chambers 2016-07-22 07:44:40

This method seems to be the best solution, both for efficiency and for generating the same results as a FULL OUTER JOIN. This blog post also explains it well - to quote from Method 2: "This handles duplicate rows correctly and doesn’t include anything it shouldn’t. It’s necessary to use UNION ALL instead of plain UNION, which would eliminate the duplicates I want to keep. This may be significantly more efficient on large result sets, since there’s no need to sort and remove duplicates."

@shA.t 2017-09-07 14:31:15

@SteveChambers it's too late, but thanks for your comment. I added your comment to then answer to highlighted more, If you are not agree please roll it back ;).

@Steve Chambers 2017-09-07 15:05:05

No problem @shA.t - IMO this should really have more upvotes and/or be the accepted answer.

@Rubén Ruíz 2017-05-22 21:53:33

I fix the response, and works include all rows (based on 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
    );

@Marc L. 2017-07-24 19:15:00

No, this is a type of "outer-only" join, that will only return the rows from tablea that don't have a match in tableb and vice versa. The you try to UNION ALL, which would only work if these two tables have equivalently ordered columns, which isn't guaranteed.

@Rubén Ruíz 2017-07-27 21:22:18

it works, I create on temp database tablea(1,2,3,4,5,6) and tableb(4,5,6,7,8,9) its rows have 3 cols "id", "number" and "name_number" as text, and works in result only have (1,2,3,7,8,9)

@Marc L. 2017-07-28 03:46:47

That's not an outer join. An outer join also includes the matching members.

@Rubén Ruíz 2017-08-02 06:41:58

I Fix see new....

@Rubén Ruíz 2017-08-02 06:45:31

that new sentence have all results 1,2, ..., 9

@Angelos 2017-02-09 14:29:57

Answer:

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

Can be recreated as follows:

 SELECT t1.*, t2.* 
 FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
 LEFT JOIN t1 ON t1.id = tmp.id
 LEFT JOIN t2 ON t2.id = tmp.id;

Using a UNION or UNION ALL answer does not cover the edge case where the base tables have duplicated entries.

Explanation:

There is an edge case that a UNION or UNION ALL cannot cover. We cannot test this on mysql as it doesn't support FULL OUTER JOINs, but we can illustrate this on a database that does support it:

 WITH cte_t1 AS
 (
       SELECT 1 AS id1
       UNION ALL SELECT 2
       UNION ALL SELECT 5
       UNION ALL SELECT 6
       UNION ALL SELECT 6
 ),
cte_t2 AS
(
      SELECT 3 AS id2
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 6
)
SELECT  *  FROM  cte_t1 t1 FULL OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2;

This gives us this answer:

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

The UNION solution:

SELECT  * FROM  cte_t1 t1 LEFT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
UNION    
SELECT  * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

Gives an incorrect answer:

 id1  id2
NULL  3
NULL  4
1  NULL
2  NULL
5  5
6  6

The UNION ALL solution:

SELECT  * FROM cte_t1 t1 LEFT OUTER join cte_t2 t2 ON t1.id1 = t2.id2
UNION ALL
SELECT  * FROM  cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

Is also incorrect.

id1  id2
1  NULL
2  NULL
5  5
6  6
6  6
6  6
6  6
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

Whereas this query:

SELECT t1.*, t2.*
FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp 
LEFT JOIN t1 ON t1.id = tmp.id 
LEFT JOIN t2 ON t2.id = tmp.id;

Gives the following:

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

The order is different, but otherwise matches the correct answer.

@Marc L. 2017-07-24 19:31:39

That's cute, but misrepresents the UNION ALL solution. Also, it presents a solution using UNION which would be slower on large source tables because of the required de-duplication. Finally, it wouldn't compile, because the field id doesn't exist in the subquery tmp.

@Angelos 2017-07-24 21:04:59

I never made a claim about speed, and neither did the OP mention anything about speed. Assuming the the UNION ALL (you don't rely specify which one) and this both give the correct answer, if we wanted to make the assertion that one is faster, we would need to provide benchmarks, and that would be digressing from the OP question.

@Angelos 2017-07-24 21:18:47

As to the observation about the id not being in the sub-query, I corrected the typo - thank you for pointing it out. Your misrepresentations claim is vague - if maybe you could provide more information, I can address that. On your final observation about cuteness, I don't have any comment, I would rather focus on the logic of the sql.

@Marc L. 2017-07-25 20:58:20

Misrepresents: "The UNION ALL solution: ... Is also incorrect." The code you present leaves out the intersection-exclusion from the right-join (where t1.id1 is null) that must be provided in the UNION ALL. Which is to say, your solution trumps all the others, only when one of those other solutions is incorrectly implemented. On "cuteness," point taken. That was gratuitous, my apologies.

@Gordon Linoff 2017-01-14 13:27:01

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

For a query such as (from this duplicate):

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 for this to work with NULL values (which may also be necessary), then use the NULL-safe comparison operator, <=> rather than =.

@fthiella 2017-03-31 09:26:07

this is often a good solution, but it might give different results than a FULL OUTER JOIN whenever the name column is null. The union all query with anti-join pattern should reproduce the outer join behavior correctly, but which solution is more appropriate depends on the context and on the constraints that are active on the tables.

@Gordon Linoff 2017-03-31 11:50:17

@fthiella . . . That is a good point. I adjusted the answer.

@fthiella 2017-04-01 08:43:08

okay, but the null-safe comparison operator will make the join succeed, which is different than the full outer join behavior in case you have null names both in t1 and in t2

@Gordon Linoff 2017-04-01 22:12:52

@fthiella . . . I'll have to think about the best way to do this. But given how wrong the accepted answer is, almost anything is closer to the right answer. (That answer is just wrong if there are multiple keys on either side.)

@fthiella 2017-04-03 10:29:36

yes the accepted answer is wrong, as a general solution I think it's correct to use union all, but that answer misses an anti-join pattern in either the first or the second query that will keep existing duplicates but prevents from adding new ones. Depending on the context other solutions (like this one) might be more appropriate.

@cweitat 2018-06-17 16:29:02

working best as of Jun'18

@a20 2016-03-11 08:47:04

Modified shA.t's query 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 t2.value IS NULL 

@Nathan Long 2012-02-09 16:23:09

The answer that Pablo Santa Cruz gave is correct; however, in case anybody stumbled on this page and wants more clarification, here is a detailed breakdown.

Example Tables

Suppose we have the following tables:

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

Inner Joins

An inner join, like this:

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

Would get us only records that appear in both tables, like this:

1 Tim  1 Tim

Inner joins don't have a direction (like left or right) because they are explicitly bidirectional - we require a match on both sides.

Outer Joins

Outer joins, on the other hand, are for finding records that may not have a match in the other table. As such, you have to specify which side of the join is allowed to have a missing record.

LEFT JOIN and RIGHT JOIN are shorthand for LEFT OUTER JOIN and RIGHT OUTER JOIN; I will use their full names below to reinforce the concept of outer joins vs inner joins.

Left Outer Join

A left outer join, like this:

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

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

1 Tim   1    Tim
2 Marta NULL NULL

Right Outer Join

A right outer join, like this:

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

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

1    Tim   1  Tim
NULL NULL  3  Katarina

Full Outer Join

A full outer join would give us all records from both tables, whether or not they have a match in the other table, with NULLs on both sides where there is no match. The result would look like this:

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

However, as Pablo Santa Cruz pointed out, MySQL doesn't support this. We can emulate it by doing 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 a UNION as meaning "run both of these queries, then stack the results on top of each other"; some of the rows will come from the first query and some from the second.

It should be noted that a UNION in MySQL will eliminate exact duplicates: Tim would appear in both of the queries here, but the result of the UNION only lists him once. My database guru colleague feels that this behavior should not be relied upon. So to be more explicit about it, we could add a WHERE clause 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 you wanted to see duplicates for some reason, you could use UNION ALL.

@Nathan Long 2013-05-27 16:43:48

This answer is more than a year old, but it turns out that Mr. Atwood had an even better answer on his blog in 2007: codinghorror.com/blog/2007/10/…

@Garen 2014-02-11 21:47:57

For MySQL you really want to avoid using UNION instead of UNION ALL if there is no overlap (see Pavle's comment above). If you could add some more info to that effect in your answer here, I think it'd be the preferred answer for this question as it's more thorough.

@spencer7593 2015-05-07 15:18:23

The recommendation from the "database guru colleague" is correct. In terms of the relational model (all the theoretical work done by Ted Codd and Chris Date), a query of the last form emulates a FULL OUTER JOIN, because it combines two distinct sets, The second query doesn't introduce "duplicates" (rows already returned by the first query) that would not be produced by a FULL OUTER JOIN. There's nothing wrong with doing queries that way, and using UNION to remove those duplicates. But to really replicate a FULL OUTER JOIN, we need one of the queries to be an anti-join.

@spencer7593 2015-05-07 15:22:29

Jeff Atwood's post does a really good job of visually explaining join types with Venn diagrams. And, he gives an example of the anti-join... returning rows from A that are not in B. And we can visually see how this set is distinct from the set of all rows in B along with matching rows in A.

@Ken 2016-10-23 02:36:46

@NathanLong your post here is old but man it is an awesome little piece of clarification and instruction. well written and examplefied! Awesome.

@Istiaque Ahmed 2017-11-06 11:31:49

@spencer7593, can you explain more as to why we need what the guru colleagues recommend i.e. the where clause in the second select query WHERE `t1`.`id` IS NULL;?

@spencer7593 2017-11-06 14:12:51

@IstiaqueAhmed: the goal is to emulate a FULL OUTER JOIN operation. We need that condition in the second query so it returns only rows that don't have a match (an anti-join pattern.). Without that condition, the query is an outer join... it returns rows that match as well as those without a match. And the rows that match were already returned by the first query. If the second query returns those same rows (again), we've duplicated rows and our result will not be equivalent to a FULL OUTER JOIN.

@spencer7593 2017-11-06 14:16:39

@IstiaqueAhmed: It is true that a UNION operation will remove those duplicates; but it also removes ALL duplicate rows, including duplicate rows that would be in the returned by a FULL OUTER JOIN. To emulate a FULL JOIN b, the correct pattern is (a LEFT JOIN b) UNION ALL (b ANTI JOIN a).

@Istiaque Ahmed 2017-11-06 18:20:55

@spencer7593, including duplicate rows that would be in the returned by a FULL OUTER JOIN - can you explain it more ?

@Najeeb 2019-02-22 06:32:11

Very concise answer with a great explanation. Thanks for this.

@Rami Jamleh 2013-01-14 13:50:31

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

@Kabir Hossain 2015-10-14 06:32:10

Can we use it ? like as: SELECT * FROM leftTable lt LEFT JOIN rightTable rt ON lt.id = rt.lrid UNION SELECT lt.*, rl.* -- To match column set FROM leftTable lt RIGHT JOIN rightTable rt ON lt.id = rt.lrid;

@Rami Jamleh 2016-04-19 22:42:57

yes but SQLite doesn't support right joins but yes in MYSQL yes

@alamelu 2014-02-03 12:30:07

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

@Matthew Read 2018-03-27 02:31:56

This is just duplicating the results from a left join.

@Alex Pliutau 2013-10-24 11:10:07

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

Related Questions

Sponsored Content

27 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

41 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

13 Answered Questions

[SOLVED] LINQ - Full Outer Join

13 Answered Questions

[SOLVED] How to join (merge) data frames (inner, outer, left, right)

36 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

3 Answered Questions

16 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

19 Answered Questions

[SOLVED] LEFT OUTER JOIN in LINQ

  • 2010-08-04 11:18:21
  • Toy
  • 566643 View
  • 448 Score
  • 19 Answer
  • Tags:   c# linq join

10 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

Sponsored Content