Avoiding two LEFT JOINs?

Given these 2 tables:

CREATE TABLE aaa (
  x number primary key,
  y varchar2(10))

CREATE TABLE bbb (
  x number primary key,
  y varchar2(10))

I insert two columns:

insert into aaa values (1, 'foo')
insert into bbb values (1, 'bar')

Then, I query for:

select a.x, a.y, b.y from aaa a
LEFT JOIN bbb b on a.x = b.x

which returns:

1 foo bar

Then, I insert a row with x = 2 into bbb:

insert into bbb values (2, 'bar')

Re-running the above query returns the same result.

However, I’d like to write a query to get a result of:

1 foo bar
2 null bar

Can I achieve that generically without doing:

select b.x, b.y, a.y from bbb b
LEFT JOIN aaa a ON b.x = a.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