0

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
  • 1
    I am not quite sure exactly what you are trying to accomplish. You may want to do a full outer join. – Justin Cave Aug 24 '16 at 19:45
4

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 Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.