2 added 5 characters in body edited Aug 24 '16 at 20:07 MguerraTorres 2,04855 silver badges1111 bronze badges Your join clause is the issue here. The reason the query is returning the same result is because your primary table is aaa, and there is no x=2 record in aaa. a left join returns everything in the left table (aaa) whether or not it matches the right table (bbb). Since x=2 does not exist in aaa, it will never join to the x=2 record in bbb. As Justin Cave states, if you don't want to use bbb as your left table (as you stated) you'll need to use a FULL OUTER JOIN which will return all records from both tables, regardless of whether or not they have a match in the other table. Something like this should work: ``````SELECT COALESCE(a.x,b.x) AS x ,a.y ,b.y FROM aaa a FULL OUTER JOIN bbb b ON a.x=b.x `````` Your join clause is the issue here. The reason the query is returning the same result is because your primary table is aaa, and there is no x=2 record in aaa. a left join returns everything in the left table (aaa) whether or not it matches the right table (bbb). Since x=2 does not exist in aaa, it will never join to the x=2 record in bbb. As Justin Cave states, if you don't want to use bbb as your left table (as you stated) you'll need to use a FULL OUTER JOIN which will return all records from both tables, regardless of whether or not they have a match in the other table. Something like this should work: ``````SELECT COALESCE(a.x,b.x) ,a.y ,b.y FROM aaa a FULL OUTER JOIN bbb b ON a.x=b.x `````` Your join clause is the issue here. The reason the query is returning the same result is because your primary table is aaa, and there is no x=2 record in aaa. a left join returns everything in the left table (aaa) whether or not it matches the right table (bbb). Since x=2 does not exist in aaa, it will never join to the x=2 record in bbb. As Justin Cave states, if you don't want to use bbb as your left table (as you stated) you'll need to use a FULL OUTER JOIN which will return all records from both tables, regardless of whether or not they have a match in the other table. Something like this should work: ``````SELECT COALESCE(a.x,b.x) AS x ,a.y ,b.y FROM aaa a FULL OUTER JOIN bbb b ON a.x=b.x `````` 1 answered Aug 24 '16 at 19:54 MguerraTorres 2,04855 silver badges1111 bronze badges Your join clause is the issue here. The reason the query is returning the same result is because your primary table is aaa, and there is no x=2 record in aaa. a left join returns everything in the left table (aaa) whether or not it matches the right table (bbb). Since x=2 does not exist in aaa, it will never join to the x=2 record in bbb. As Justin Cave states, if you don't want to use bbb as your left table (as you stated) you'll need to use a FULL OUTER JOIN which will return all records from both tables, regardless of whether or not they have a match in the other table. Something like this should work: ``````SELECT COALESCE(a.x,b.x) ,a.y ,b.y FROM aaa a FULL OUTER JOIN bbb b ON a.x=b.x ``````