2 added 5 characters in body
source | link

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
source | link

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