Back to the Basics: A Simple Join Issue

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