Joining on a common table, how do you get a FULL OUTER JOIN to expand on another table?

Question

I've scoured StackOverflow and Google for an answer to this problem.

I'm trying to create a Microsot SQL Server 2008 view. Not a stored procedure. Not a function. Just a query (i.e. a view).

I have three tables. The first table defines a common key, let's say "CompanyID". The other two tables have a sometimes-common field, let's say "EmployeeName".

I want a single table result that, when my WHERE clause says "WHERE CompanyID = 12" looks like this:

CompanyID | TableA    | TableB
12        | John Doe  | John Doe
12        | Betty Sue | NULL
12        | NULL      | Billy Bob

I've tried a FULL OUTER JOIN that looks like this:

SELECT Company.CompanyID,
    TableA.EmployeeName,
    TableB.EmployeeName
FROM Company
FULL OUTER JOIN TableA ON Company.CompanyID = TableA.CompanyID
FULL OUTER JOIN TableB ON 
    Company.CompanyID = TableB.CompanyID AND 
    (TableA.EmployeeName IS NULL OR TableB.EmployeeName IS NULL OR TableB.EmployeeName = TableA.EmployeeName)

I'm only getting the NULL from one matched table, I'm not getting the expansion for the other table. In the above sample, I'm basically only getting the first and third rows and not the second.

Can someone help me create this query and show me how this is done correctly?

BTW I already have a stored procedure that looks very clean and populates an in-memory table, but that isn't what I want.

Thanks.

-- EDIT:

Here's a full-running sample of what currently doesn't work (it's missing 'someone 2' and 'someone 3'.

DECLARE @Company TABLE
(
    CompanyID int
)

INSERT INTO @Company (CompanyID) VALUES (10)
INSERT INTO @Company (CompanyID) VALUES (12)

DECLARE @TableA TABLE
(
    EmployeeId int,
    CompanyId int,
    EmployeeName varchar(30)
)

DECLARE @TableB TABLE
(
    EmployeeId int,
    CompanyId int,
    EmployeeName varchar(30)
)

INSERT INTO @TableA ( EmployeeId, CompanyId, EmployeeName )
VALUES ( 1, 10, 'someone' )

--INSERT INTO @TableA ( EmployeeId, CompanyId, EmployeeName )
--VALUES ( 2, 12, 'someone 2' )

INSERT INTO @TableA ( EmployeeId, CompanyId, EmployeeName )
VALUES ( 3, 12, 'someone 3' )

INSERT INTO @TableA ( EmployeeId, CompanyId, EmployeeName )
VALUES ( 3, 12, 'someone 4' )

INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName )
VALUES ( 1, 10, 'someone' )

INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName )
VALUES ( 2, 12, 'someone 2' )

--INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName )
--VALUES ( 3, 12, 'someone 3' )

INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName )
VALUES ( 3, 12, 'someone 4' )

SELECT Company.CompanyID,
    TableA.EmployeeName,
    TableB.EmployeeName
FROM @Company Company
FULL OUTER JOIN @TableA TableA ON Company.CompanyID = TableA.CompanyID
FULL OUTER JOIN @TableB TableB ON Company.CompanyID = TableB.CompanyID
WHERE
(
    TableA.EmployeeName IS NULL OR TableB.EmployeeName IS NULL OR 
    TableB.EmployeeName = TableA.EmployeeName
)
AND Company.CompanyID = 12

Result:

CompanyID   EmployeeName    EmployeeName
12          someone 4       someone 4

What I want:

CompanyID   EmployeeName    EmployeeName
12          NULL            someone 2
12          someone 3       NULL
12          someone 4       someone 4

Answer 1

try this:

SELECT Company.CompanyID,
    TableA.EmployeeName,
    TableB.EmployeeName
FROM Company
LEFT OUTER JOIN TableA ON Company.CompanyID = TableA.CompanyID
LEFT OUTER JOIN TableB ON Company.CompanyID = TableB.CompanyID
WHERE (TableA.EmployeeName IS NULL OR TableB.EmployeeName IS NULL OR TableB.EmployeeName = TableA.EmployeeName)

EDIT after OP gave test data and expected result set

try this (tables and test data from question):

DECLARE @Company TABLE (CompanyID int)
DECLARE @TableA TABLE (EmployeeId int,CompanyId int,EmployeeName varchar(30))
DECLARE @TableB TABLE (EmployeeId int,CompanyId int,EmployeeName varchar(30))

set nocount on
INSERT INTO @Company (CompanyID) VALUES (10)
INSERT INTO @Company (CompanyID) VALUES (12)

--INSERT INTO @TableA ( EmployeeId, CompanyId, EmployeeName )--VALUES ( 2, 12, 'someone 2' )
INSERT INTO @TableA ( EmployeeId, CompanyId, EmployeeName )VALUES ( 1, 10, 'someone' )
INSERT INTO @TableA ( EmployeeId, CompanyId, EmployeeName )VALUES ( 3, 12, 'someone 3' )
INSERT INTO @TableA ( EmployeeId, CompanyId, EmployeeName )VALUES ( 3, 12, 'someone 4' )

--INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName )--VALUES ( 3, 12, 'someone 3' )
INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName )VALUES ( 1, 10, 'someone' )
INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName )VALUES ( 2, 12, 'someone 2' )
INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName )VALUES ( 3, 12, 'someone 4' )
set nocount off

SELECT coalesce(TableA.CompanyID,TableB.CompanyID) CompanyID,
    TableA.EmployeeName,
    TableB.EmployeeName
FROM @TableA TableA
FULL OUTER jOIN  @TableB TableB ON TableA.CompanyID = TableB.CompanyID AND TableB.EmployeeName = TableA.EmployeeName
WHERE coalesce(TableA.CompanyID,TableB.CompanyID) = 12

OUTPUT:

CompanyID   EmployeeName                   EmployeeName
----------- ------------------------------ ------------------------------
12          NULL                           someone 2
12          someone 3                      NULL
12          someone 4                      someone 4

(3 row(s) affected)

Answer 2

Try this

DECLARE @Company TABLE 
( 
    CompanyID int 
) 

INSERT INTO @Company (CompanyID) VALUES (10) 
INSERT INTO @Company (CompanyID) VALUES (12) 

DECLARE @TableA TABLE 
( 
    EmployeeId int, 
    CompanyId int, 
    EmployeeName varchar(30) 
) 

DECLARE @TableB TABLE 
( 
    EmployeeId int, 
    CompanyId int, 
    EmployeeName varchar(30) 
) 

INSERT INTO @TableA ( EmployeeId, CompanyId, EmployeeName ) 
VALUES ( 1, 10, 'someone' ) 

--INSERT INTO @TableA ( EmployeeId, CompanyId, EmployeeName ) 
--VALUES ( 2, 12, 'someone 2' ) 

INSERT INTO @TableA ( EmployeeId, CompanyId, EmployeeName ) 
VALUES ( 3, 12, 'someone 3' ) 

INSERT INTO @TableA ( EmployeeId, CompanyId, EmployeeName ) 
VALUES ( 3, 12, 'someone 4' ) 

INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName ) 
VALUES ( 1, 10, 'someone' ) 

INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName ) 
VALUES ( 2, 12, 'someone 2' ) 

--INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName ) 
--VALUES ( 3, 12, 'someone 3' ) 

INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName ) 
VALUES ( 3, 12, 'someone 4' ) 

INSERT INTO @TableB ( EmployeeId, CompanyId, EmployeeName )  
VALUES ( 3, 12, 'someone 4' )  

SELECT Company.CompanyID,  
   A.EmployeeNameTableA,  
   A.EmployeeNameTAbleB 
FROM @Company Company  
left OUTER JOIN (select TableA.EmployeeName as EmployeeNameTableA, TableB.EmployeeName as EmployeeNameTableB , 
coalesce(TableA.CompanyID,TableB.CompanyID) as CompanyID 
from @TableA TableA  
FULL OUTER JOIN @TableB TableB ON TableA.CompanyID = TableB.CompanyID and TableB.EmployeeName = TableA.EmployeeName and (tablea.companyid = 12 or tableb.companyid = 12))A ON Company.CompanyID = A.CompanyID  

WHERE Company.CompanyID = 12  

Answer 3

The FULL OUTER JOIN should be made only between TableA and TableB on companyID AND employeeName since this is the value you want filled as NULL if it exists only on one table.
Once you get this, you can do an inner join with Company to get other data from Company.

FULL OUTER JOIN Solution:

select Company.companyID, EmployeeNameA, EmployeeNameB
from (
    SELECT isnull(TableA.CompanyID, TableB.CompanyID) as companyID,
        TableA.EmployeeName as EmployeeNameA,
        TableB.EmployeeName as EmployeeNameB
    FROM @TableA TableA 
    FULL OUTER JOIN @TableB TableB ON TableA.EmployeeName = TableB.EmployeeName and TableA.companyID = TableB.companyID
    WHERE
     TableA.CompanyID = 12 or TableB.CompanyID = 12 
) merged
inner join @Company Company
    on merged.companyID = Company.companyID

Personally I find it difficult to think in terms of FULL OUTER JOINS. My approach on this would be: Find the distinct EmployeeNames you need in your result by making a UNION between affected tables and then use left joins to get data from both tables thus getting your NULLs when you should.

LEFT JOIN Example:

select c.companyID, a.employeeName, b.employeeName
from  (
    select distinct employeeName, companyID
    from  (
        select a.employeeName, companyID 
        from @tableA  a
        union 
        select b.employeeName, companyID
        from @tableB b
    ) a
) z
inner join @company c
    on c.companyID = z.companyID
left join @tableA  a
    on z.companyID = a.companyID and z.employeeName = a.employeeName
left join @tableB  b
    on z.companyID = b.companyID and z.employeeName = b.employeeName
where z.companyID = 12

Answer 4

Here is a variation on Dimitris Baltas' answer, which is closer to what I had in mind.

SELECT Company.CompanyID,
    TableA.EmployeeName as EmployeeNameTableA,
    TableB.EmployeeName as EmployeeNameTableB
FROM @TableA TableA 
FULL OUTER JOIN @TableB TableB ON TableA.EmployeeName = TableB.EmployeeName 
    and TableA.companyID = TableB.companyID
INNER JOIN @Company Company ON (
    Company.CompanyID = TableA.CompanyId OR Company.CompanyID = TableB.CompanyId
)
WHERE Company.CompanyID = 12

A key thing I am looking for (and this sample doesn't do this but Dimitris' might) was pre-filtering on the CompanyID so that the execution plan will not sift through all of the rows of each of the joined tables before filtering out the CompanyID. In my case, the two joined tables are extremely slow.

I think what I'll ultimately have to do is keep using sprocs.