Trouble with left outer join and multiple conditions-CodePudding

I want to left outer join and display all id records that appear in table1 even if they don't exist in the table2. Table1 can have multiple id records for a year so I need to select distinct.

Here is the selection of ID's i need from table1

select distinct a.id
from table1 a
where a.year = 2022
and a.type = 'R'

This will display 650 records

So here is my attempt to join with another table. I want to still see the all the results of the first query on table1 even if there is no record found in table 2. So the desired result should be 650 records in total.

select distinct a.id, b.value
from table1 a left join table2 b on
a.id = b.id
where b.month = 'JAN'
and a.year = 2022
and a.type = 'R';

My result gives me 550 records. So I am still missing 100 records which do not exist in table2 that need to be included in the query.

If I change the WHERE to an AND in the ON condition, I will get all YEARS displayed and all TYPES. Resulting in a massive amount of records.

select distinct a.id, b.value
from table1 a left join table2 b on
a.id = b.id
and b.month = 'JAN'
and a.year = 2022
and a.type = 'R';

What am I missing? I though left joins will display all records in one table even if they don't exist in another. I don't really understand how to do this will multiple conditions.

CodePudding user response：

What am I missing? I though left joins will display all records in one table even if they don't exist in another. I don't really understand how to do this will multiple conditions.

A LEFT OUTER JOIN condition will display all records from the table on the left of the join and any matching ones on the right of the join or NULL values otherwise.

The conditions in the WHERE clause will only display records that are true.

You are putting the b.month = 'JAN' condition into the WHERE clause (and not into the JOIN condition) so it will look for only those rows where the WHERE clause it true and then it will not match any rows where b.month is NULL and you have effectively turned the join condition to an INNER JOIN and not a LEFT OUTER JOIN.

You have several choices:

Perform the filtering in the WHERE clause but do it in a sub-query before joining the tables:

SELECT a.id, b.value
FROM   (
        select distinct
               id
        from   table1
        where  year = 2022
        and    type = 'R'
       ) a
       LEFT OUTER JOIN (
        select id, value
        from   table2
        where  month = 'JAN'
       ) b
       ON (a.id = b.id)

Put the b.month = 'JAN' filter into the JOIN condiiton:

SELECT DISTINCT
       a.id, b.value
FROM   table1 a
       LEFT OUTER JOIN table2 b
       ON (a.id = b.id AND b.month = 'JAN')
WHERE  year = 2022
AND    type = 'R';

CodePudding user response：

How about

select distinct a.id, b.value
from table1 a left join table2 b on a.id = b.id and b.month = 'JAN'
where a.year = 2022
  and a.type = 'R';