CODEWITHSUNDEEP
sqlsql-serverjoinouter-join
Sam Makin
Sam Makin

Reputation: 1556

RIGHT OUTER JOIN and WHERE when there are matching rows

I am having issues understanding the RIGHT OUTER JOIN logic.

We have two tables of data - Process and Resource

Process                             Resource    
ProcessID   ResourceID  Date        ResourceID  ResourceName
1           1           01/01/2015  1           Resource 1
2           1           02/01/2015  2           Resource 2
3           2           02/01/2015

We created the SELECT statement 

SELECT *
FROM Process P
RIGHT OUTER JOIN Resource R ON P.ResourceID = R.ResourceID
WHERE P.Date = '2015-01-01'

And the expected results are

ProcessID   ResourceID  Date        ResourceID  ResourceName    
1           1           01/01/2015  1           Resource 1  
NULL        NULL        NULL        2           Resource 2  <-- This row is not returned

However we only receive one row - we want each resource for each process that we select, with null values returned from the process table where they dont match.

If we change the query and remove the row with ProcessID of 3 the query returns both resources, but our example has a join on a row we have not selected, so seems not to be returned.

SELECT *
FROM Process P
RIGHT OUTER JOIN Resource R ON P.ResourceID = R.ResourceID
WHERE P.Date = '2015-01-01' OR P.Date IS NULL

How can we make the query return the expected results with the data shown above? What are we missing?

Upvotes: 0

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

In a right outer join, if you want to filter on the first table, the condition needs to go in the on clause. Otherwise, the join becomes an inner join.

However, I encourage you to use left outer join. Most people find it easier to follow the logic of "keep everything in the first table" rather than "keep everything in the last table". A similar rule holds, but for the second table rather than the first:

SELECT *
FROM Resource R LEFT OUTER JOIN
     Process P
     ON P.ResourceID = R.ResourceID AND P.Date = '2015-01-01';

Upvotes: 2

Related Questions