Left outer join with 2 column missing some output rows

Question

When I select all rows from table zvw_test it return 145 rows. Table Customer_Class_Price have 160 rows. When I try to join this 2 table with 2 condition it return 122 rows. I don't understand why it not return all rows from zvw_test (145 rows) becasue I use left outer join it should return all rows from left table.

Thank you.

 SELECT zvw_test.Goods_ID, 
        zvw_test.Thai_Name, 
        zvw_test.UM, 
        zvw_test.CBal, 
        Customer_Class_Price.ListPrice
   FROM zvw_test 
   LEFT OUTER JOIN 
        Customer_Class_Price ON zvw_test.Goods_ID = Customer_Class_Price.Goods_ID AND 
                                zvw_test.UM = Customer_Class_Price.UM
  WHERE (Customer_Class_Price.ClassCode = '444-666')

is it because of the `WHERE (Customer_Class_Price.ClassCode = '444-666')`? you should change it to `WHERE (Customer_Class_Price.ClassCode IS NULL OR Customer_Class_Price.ClassCode = '444-666')` — JamieD77, Feb 04 '16 at 16:13
After I change where clause it reture 138 rows. Still missing 7 rows. — njtd, Feb 04 '16 at 16:18
Possible duplicate of [LEFT OUTER JOIN query not returning expected rows](http://stackoverflow.com/questions/14861927/left-outer-join-query-not-returning-expected-rows) — Tab Alleman, Feb 04 '16 at 16:19
There must be 7 rows that join to a ClassCode that is neither '444-666' nor is it NULL. — Tab Alleman, Feb 04 '16 at 16:22
which is why you should move the where condition to the join clause — JamieD77, Feb 04 '16 at 16:23

score 3 · Answer 1 · answered Feb 04 '16 at 16:14

3

By putting one of your columns from the LEFT OUTER JOIN table in your WHERE clause, you have effectively turned it into an INNER JOIN. You need to move that up to the JOIN clause.

answered Feb 04 '16 at 16:14

Tom H

46,766
14
87
128

score 2 · Accepted Answer · edited Feb 04 '16 at 17:23

2

I had this problem before, I used a CTE to solve this, like:

WITH A AS
(
    SELECT Customer_Class_Price.Goods_ID, Customer_Class_Price.UM, Customer_Class_Price.ListPrice  
    FROM Customer_Class_Price
    WHERE Customer_Class_Price.ClassCode = '444-666'
)
SELECT zvw_test.Goods_ID, zvw_test.Thai_Name, zvw_test.UM, zvw_test.CBal, A.ListPrice
FROM zvw_test LEFT OUTER JOIN A 
ON zvw_test.Goods_ID = A.Goods_ID AND zvw_test.UM = A.UM

edited Feb 04 '16 at 17:23

Equalsk

7,954
2
41
67

answered Feb 04 '16 at 16:29

Mingxin

36
1

This query is working But I don't understand why use CTE can solve this problem. Can you explain. Thank you very much. – njtd Feb 06 '16 at 08:13

score 1 · Answer 3 · answered Feb 04 '16 at 16:14

You demand in your WHERE clause:

 (Customer_Class_Price.ClassCode = '444-666')

Ergo you are not selecting rows where Customer_Class_Price.ClassCode IS NULL. Customer_Class_Price.ClassCode would be NULL if there is no corresponding row, but you are filtering those out explicitely.

Left outer join with 2 column missing some output rows

3 Answers3