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?
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