Possible Duplicate:
Full Outer Join in MySQL
There are two tables tableA and table B both the tables have common column id
We want to get the results which are having all records in A but not in B and all records which exists in B but not in A
Regards, Chinta kiran
You are looking for MINUS SET OPERATOR:
"We want to get the results which are having all records in A but not in B"
Easy way:
SELECT A.*
FROM A
WHERE A.id not in (SELECT id FROM B)
With Full Outer Join
SELECT A.*
FROM A full outer join B on A.id = B.id
WHERE B.id is Null
The right way:
SELECT A.*
FROM A left outer join B on A.id = B.id
WHERE B.id is Null
Change A to B and B to A for in order to get the results which are having all records in B but not in A.
I suggest you to read the article A Visual Explanation of SQL Joins by Jeff Atwood of Coding Horror.
Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.
You can use UNION operator as follow
SELECT * FROM tablea
UNION
SELECT * FROM tableb
if you want to read more about
UNION operator
This is best accomplished with a LEFT OUTER JOIN where the predicate (WHERE clause) ensures that the joined row is NULL; something like:
SELECT A.* FROM A LEFT OUTER JOIN B ON A.id = B.a_id WHERE B.a_id IS NULL;
