Left Join - Join two tables and pull all data from left table

Question

I am trying to join two tables ORDER and ORDER_FLG. I want to pull all columns from ORDER not having Flg as 'Y' in ORDER_FLG table.

ORDER

Branch_nbr |Order_Id | Order_start_dt |Order_end_dt 
0001    | 110000   | 01/01/2014 | 02/02/2014
0002    |   110001  |  03/03/2014  |  04/04/2014
0003    |   110002  |   05/05/2014  |  06/06/2014

ORDER_FLG

Branch_Nbr |Order_Id |Flg
0001    |   110000| Y
0003    |   110002| N

SQL:

SELECT A.*
FROM ORDER AS A LEFT JOIN ORDER_FLG AS B
ON A.Branch_nbr=B.Branch_nbr AND A.Order_Id=B.Order_Id
WHERE A.Order_start_dt >= 'SOME_DATE'
AND A.Order_end_dt <= 'SOME_DATE'
AND B.Flg <> 'Y' 

I should get two rows (110002, 110001) as output but I get only one. If entry is not present in second table, data is not pulled from first table. Shouldn't left join pull all data from first table?

I tried this, it works but takes time -

SELECT A.*
FROM ORDER AS A 
WHERE A.Order_start_dt >= 'SOME_DATE'
AND A.Order_end_dt <= 'SOME_DATE'
AND A.Order_Id NOT IN ( SELECT Order_Id FROM ORDER_FLG
WHERE Order_Id=A.Order_Id AND Branch_nbr=A.Branch_nbr 
AND Flg='Y')

Answer 1

Try this:

SELECT A.*
FROM ORDER AS A LEFT JOIN ORDER_FLG AS B
ON A.Branch_nbr=B.Branch_nbr AND A.Order_Id=B.Order_Id
WHERE A.Order_start_dt >= 'SOME_DATE'
AND A.Order_end_dt <= 'SOME_DATE'
AND (B.Flg IS NULL OR B.Flg <> 'Y' )

The B.Flg <> 'Y' condition is obviously false for all Orders that have no matching row in ORDER_FLG. You get a NULL in those fields, so you have to allow for that possibility in the WHERE clause.

Answer 2

Your where clause includes a reference to your outer table: AND B.Flg <> 'Y'

That where clause is applied after the outer join. Any rows coming from the outer joined table that don't have matches will have nulls in the result set. Null doesn't match your constraint, so the rows are dropped from the result set.

Try this instead:
SELECT A.*
FROM ORDER AS A LEFT JOIN ORDER_FLG AS B
ON A.Branch_nbr=B.Branch_nbr AND A.Order_Id=B.Order_Id
AND B.Flg <> 'Y' 
WHERE A.Order_start_dt >= 'SOME_DATE'
AND A.Order_end_dt <= 'SOME_DATE'