Combining Left & Right Join in mysql query

Question

I am joining 2 tables -tbl1 and tbl2. Left join give all data from tbl1 which is in tbl2 or only on tbl1. Right join gives data from tbl2 which is don't exists in tbl1.

I want to combine both results.

What is the best way to do this so that i get all data from tbl1 and tbl2 ?

Answer 1

The only you can do that is by using UNION. MySQL doesn't support FULL JOINjust like in MSSQL.

SELECT * 
FROM tbl1 t1 
       LEFT JOIN tbl2 t2
          ON t1.col = t2.col
UNION 
SELECT * 
FROM tbl1 t1 
       RIGHT JOIN tbl2 t2 
          ON t1.col>= t2.<col

SEE HERE: Simulating FULL JOIN in MYSQL

By the way, UNION has optional keyword ALL,when the ALL is omitted, UNION automatically selects DISTINCT rows from the resultset.

EXAMLE:

SELECT *
FROM   tableA
UNION ALL
SELECT *
FROM   tableA

this can result duplicates rows

ColA    ColB
==================
1       John
2       Jade
2       Jade
3       Hello

BUT if you omit the word ALL

SELECT *
FROM   tableA
UNION
SELECT *
FROM   tableA

this can result distinct rows only

ColA    ColB
==================
1       John
2       Jade
3       Hello

Answer 2

What you want is FULL JOIN

LEFT JOIN + RIGHT JOIN = FULL JOIN

So try this:

SELECT * FROM tbl1
LEFT JOIN tbl2 ON tbl1.id = tbl2.id
UNION
SELECT * FROM tbl1
RIGHT JOIN tbl2 ON tbl1.id = tbl2.id

The UNION clause combines the results of two SQL queries into a single table of all matching rows.

Answer 3

Here's an alternative that can be easily extended if you have a full join of more than 2 tables:

SELECT t1*, t2.* 
FROM 
    ( SELECT col
      FROM tbl1
    UNION
      SELECT col
      FROM tbl2
    ) AS d
  LEFT JOIN tbl1 AS t1 
    ON t1.col = d.col
  LEFT JOIN tbl2 AS t2
    ON t2.col = d.col ;

Answer 4

you have to use FULL OUTER JOIN, But mysql doesnt support it.. You could do this for getting the result:

 SELECT * 
FROM tbl1 t1 LEFT JOIN tbl2 t2
ON t1.<col> = t2.<col>
UNION 
SELECT * 
FROM tbl1 t1 RIGHT JOIN tbl2 t2 
ON t1.<col>= t2.<col>