Don't silently turn your outer joins into inner joins ...

Surprised by outer joins not returning the expected results? Looks like they behave like an inner join?

You are probably messing with the join and the where clause.

That's also why it's important to use the full join notation for every join instead of the where based one (often used for inner joins) as it allows for a better separation of the join and the where clause.

Remember that the where clause is applied after the join clause, so if you want to restrict results from the table on the "right" of the left outer join but still have the NULLs you'll have to put the restriction in the join condition, putting it in the where clause will turn your outer join into an inner join.

Not convinced? Let's see an example:

I'll query two tables of the Firebird sample database, EMPLOYEE and EMPLOYEE_PROJECT, I'm looking for a list of employees assigned to a specific project and those not assigned (probably I'll pick a few of them and assign those too), let's see the first query

SELECT 
e.EMP_NO,
e.FULL_NAME,
ep.PROJ_ID 
FROM 
 EMPLOYEE e LEFT OUTER JOIN EMPLOYEE_PROJECT ep
  ON e.EMP_NO = ep.EMP_NO; 

and it's output is

2 Nelson, Robert [null]
4 Young, Bruce VBASE
4 Young, Bruce MAPDB
5 Lambert, Kim [null]
8 Johnson, Leslie VBASE
8 Johnson, Leslie GUIDE
8 Johnson, Leslie MKTPR
9 Forest, Phil [null]
11 Weston, K. J. [null]
12 Lee, Terri MKTPR
14 Hall, Stewart MKTPR

So far so good, now I want only those on the VBASE project and the unassigned ones, let's do what instinct suggests, add a where clause, the outer join will take care of the rest (...)

SELECT 
 e.EMP_NO,
 e.FULL_NAME,
 ep.PROJ_ID 
FROM EMPLOYEE e LEFT OUTER JOIN EMPLOYEE_PROJECT ep
 ON e.EMP_NO = ep.EMP_NO
WHERE 
 ep.PROJ_ID = 'VBASE' 

Result is:

4 Young, Bruce VBASE
8 Johnson, Leslie VBASE
15 Young, Katherine VBASE
44 Phong, Leslie VBASE
45 Ramanathan, Ashok VBASE
71 Burbank, Jennifer M. VBASE
83 Bishop, Dana VBASE
136 Johnson, Scott VBASE
138 Green, T.J. VBASE
145 Guckenheimer, Mark VBASE

Eeek, where are all the unassigned gone???
That's because the WHERE is applied after the JOIN and so it throws away all those NULLs, infact it's bound to pick only PROJ_ID = 'VBASE'
Now the right one, we want it executed in the join

SELECT 
 e.EMP_NO,
 e.FULL_NAME,
 ep.PROJ_ID
FROM EMPLOYEE e LEFT OUTER JOIN EMPLOYEE_PROJECT ep
 ON e.EMP_NO = ep.EMP_NO
  AND 
   ep.PROJ_ID = 'VBASE'  

And the result is just what we are looking for:

2 Nelson, Robert [null]
4 Young, Bruce VBASE
5 Lambert, Kim [null]
8 Johnson, Leslie VBASE
9 Forest, Phil [null]
11 Weston, K. J. [null]
12 Lee, Terri [null]
14 Hall, Stewart [null]
15 Young, Katherine VBASE
20 Papadopoulos, Chris [null]
24 Fisher, Pete [null]
28 Bennet, Ann [null]
29 De Souza, Roger [null]