Back to the Basics: A Simple Join Issue
Posted by decipherinfosys on August 11, 2008
We have covered join basics before and one of the questions that all beginners face is that if they are joining TableA with TableB in an outer join condition BUT they need to join TableC with TableB in an inner join condition, then they lose the outer join effect since for records in TableB that do not match TableA, it will result into NULL records after the join and since NULL != NULL in SQL (not considering the ANSI_NULL setting in SQL Server for this post), then the inner join with TableC will get rid of those records. Let’s take this up with an example (using SQL Server Syntax though the same applies to Oracle of DB2 LUW as well):
create table TableA (col1 int primary key, col2 nvarchar(10));
create table TableB (col3 int primary key, col1 int, col4 nvarchar(10), constraint fk_b FOREIGN KEY (col1) references TableA(col1));
create table TableC (col5 int primary key, col3 int, col6 nvarchar(10), constraint fk_c FOREIGN KEY (col3) references TableB(col3));
set nocount on
go
insert into TableA values (1, ‘A-1 value’);
insert into TableA values (2, ‘A-2 value’);
insert into TableB values (1, 1, ‘B-1 value’);
insert into TableC values (1, 1, ‘C-1 value’);
insert into TableC values (2, 1, ‘C-2 value’);
go
Now, if we want to get all records in A but only those records in B where there is a match, the query is a simple left outer join between A and B:
select *
from dbo.TableA as A
Left Outer Join dbo.TableB as B
on A.col1 = B.col1
And the result will be:
col1 col2 col3 col1 col4 ----------- ---------- ----------- ----------- ---------- 1 A-1 value 1 1 B-1 value 2 A-2 value NULL NULL NULL
And now, if we want to add an inner join condition between TableB and TableC to restrict getting only those records from TableB where there is a match in TableC, one would be tempted to do just this:
select *
from dbo.TableA as A
Left Outer Join dbo.TableB as B
on A.col1 = B.col1
Inner join dbo.TableC as C
on B.col3 = C.col3
But as you can see from the result:
col1 col2 col3 col1 col4 col5 col3 col6 ----------- ---------- ----------- ----------- ---------- ----------- ----------- ---------- 1 A-1 value 1 1 B-1 value 1 1 C-1 value 1 A-1 value 1 1 B-1 value 2 1 C-2 value
The second record from TableA is not present in the result anymore since the inner join negated that Null value. In order to get the desired result, one can make use of what are called as derived tables (SQL Server lingo) or inline views (Oracle lingo) and re-write the query as:
select *
from dbo.TableA as A
Left Outer Join (dbo.TableB as B
Inner join dbo.TableC as C
on B.col3 = C.col3)
on A.col1 = B.col1
or
select *
from dbo.TableA as A
Left Outer Join (select B.col1, B.col3, B.col4, C.col5, C.col6 from dbo.TableB as B
Inner join dbo.TableC as C
on B.col3 = C.col3) as X
on A.col1 = X.col1
and the result will be:
col1 col2 col1 col3 col4 col5 col6 ----------- ---------- ----------- ----------- ---------- ----------- ---------- 1 A-1 value 1 1 B-1 value 1 C-1 value 1 A-1 value 1 1 B-1 value 2 C-2 value 2 A-2 value NULL NULL NULL NULL NULL
And in this particular case, one can even do this:
select *
from dbo.TableB as b
inner join dbo.tableC as c
on b.col3 = c.col3
right outer join dbo.tableA as a
on b.col1 = a.col1
Leave a Reply