Sql-server – JOIN to obtain all the rows from the first table along with NULLs from the second table

join;sql server

I have two tables (tblReps and tblDailyWorkingTime). The tblReps table, i.e., the first one, returns 37 rows, but when I JOIN it with the second table I get 36 rows as the second table only has 36 rows that match the first table.

How do I go about getting the 37 rows back with a NULL result for the row that was not matched?

Here is the query that I have so far:

SELECT 
    tblReps.[sName] AS 'RepName',
    tblReps.sNote AS 'Type',
    RIGHT(CAST(tblDaily.sDateAndTimeStart  AS smalldatetime), 8) AS 'DayStarted'

FROM 
    [tblRepresentatives] AS tblReps

FULL OUTER JOIN 
    tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode 

WHERE 
    tblDaily.sDate = CAST(GETDATE() AS DATE)
    AND tblReps.[sActive] = 'True'

Best Answer

First of all, the correct type of join to use in this case is a left join:

...
FROM 
    [tblRepresentatives] AS tblReps

LEFT OUTER JOIN 
    tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode 
...

In your specific case, you could still use FULL because this condition in WHERE would turn it into a left join anyway:

tblReps.[sActive] = 'True'

But it is better to express the intent accurately.

The same effect that turns your full join into a left join is actually responsible for the missing 37th row that you expected. More specifically, this other WHERE condition:

tblDaily.sDate = CAST(GETDATE() AS DATE)

transforms your join further into an inner join. That is why the 37th row is missing from the output.

The reason for that happening is this. The FROM clause returns a null in tblDaily.sDate for the unmatched row. Because the WHERE clause logically executes after the FROM, the above-mentioned predicate excludes the unmatched row, since NULL = CAST(GETDATE() AS DATE) does not evaluate to True.

What you need to do instead is move the sDate condition to the ON subclause:

...
FROM 
    [tblRepresentatives] AS tblReps

LEFT OUTER JOIN 
    tblDailyWorkingTime AS tblDaily ON tblDaily.sRepresentativeCode = tblReps.sCode
                                   AND tblDaily.sDate = CAST(GETDATE() AS DATE)

WHERE 
    tblReps.[sActive] = 'True'

That way the right-hand side of the join is filtered on tblDaily.sDate = CAST(GETDATE() AS DATE) before the join takes place. Consequently, the query will return the expected 37 rows from tblReps complemented either with matching data from tblDaily or with nulls.

Related Question