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
1 foo bar
Then, I insert a row with
x = 2 into
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