LEFT OUTER JOIN with WHERE clause on 2nd table, not effecting 1st table

Question

I'm trying to return a set of records from two tables. I'm using LEFT OUTER JOIN, because I want all data from table 1. I only want the data from table 2 JOINED if a certain clause is met.

The clause seems to be overriding the LEFT OUTER JOIN and not returning records from table 1, if a record from table 2 doesn't meet the WHERE clause.

SELECT p.code, p.name, d.product_code, d.detail_type, d.description
  FROM products p
  LEFT OUTER JOIN product_details d
    ON p.code = d.product_code 
 WHERE product_details.detail_type = 'ALTERNATIVE NAMES'

I want all rows from products returned, but I only want rows from product_details to be joined if product_details.detail_type = 'ALTERNATIVE NAMES'

I'm looking for some guidance as to whether this is possible or if I should be stripping out the unwanted data after the initial JOIN?

This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 21 '19 at 11:20
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Jun 21 '19 at 11:21
Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Jun 21 '19 at 11:22

score 3 · Accepted Answer · answered Jun 21 '19 at 11:01

use product_details.detail_type = 'ALTERNATIVE NAMES' condition in ON Clause instead of where Clause

SELECT products.code, products.name, product_details.product_code, product_details.detail_type, product_details.description
FROM products 
LEFT OUTER JOIN product_details 
ON products.code = product_details.product_code 
and product_details.detail_type = 'ALTERNATIVE NAMES'

score 3 · Answer 2 · answered Jun 21 '19 at 11:02

3

Just replace WHERE with AND to be able to use exactly as LEFT [OUTER] JOIN.

e.g.

ON products.code = product_details.product_code
        AND product_details.detail_type = 'ALTERNATIVE NAMES'

Otherwise the query treats as an INNER JOIN.

answered Jun 21 '19 at 11:02

Barbaros Özhan

59,113
10
31
55

LEFT OUTER JOIN with WHERE clause on 2nd table, not effecting 1st table

2 Answers2