Postgresql Left join show null values if there is not in condition-CodePudding

lets say i have a table like this

hr_employee

id     name
1    'emp A'
2    'emp B'
3    'emp C'

sale_order

id    name      employee_id     date_order
1   'ORD/01'      1            '2021-07-07'
2   'ORD/02'      2            '2021-08-02'
3   'ORD/03'      3            '2021-08-17'
4   'ORD/04'      3            '2021-08-18'

im using this query:

SELECT 
string_agg(s.id::varchar,',') sale_ids,
e.name

from hr_employee e
left join sale_order s on s.employee_id = e.id
where s.date_order between '2021-08-01' and '2021-08-31'

group by e.name

and this is the output

sale_ids  name
 2        Emp B
 4,3      Emp C

i want to have output like this:

sale_ids  name
          Emp A
 2        Emp B
 4,3      Emp C

my question is how can i make Emp A appear but shows null in sale_ids fields?

CodePudding user response：

You should move the criteria on the date_order from the WHERE clause to the ON clause of the left join:

SELECT STRING_AGG(s.id::varchar, ',') sale_ids, e.name
FROM hr_employee e
LEFT JOIN sale_order s
    ON s.employee_id = e.id AND
       s.date_order >= '2021-08-01' AND s.date_order < '2021-09-01'
GROUP BY e.name;

The WHERE condition is (logically) after the FROM clause, so the NULL values added by the left join can be eliminated. The ON clause, on the other hand, is part of the join and cannot exclude rows from hr_employee.

CodePudding user response：

Filtered out by "date_order",Try to change to "between '2021-07-01' and '2021-08-31'"

CodePudding user response：

with sales as
(
    select * 
    from sale_order s 
    where s.date_order between '2021-08-01' and '2021-08-31'
)
select 
   string_agg(s.id::varchar,',') sale_ids,
   e.name
from sales s
group by e.name
left join hr_employee e
   on s.employee_id = e.id

CodePudding user response：

SELECT 
string_agg(s.id::varchar,',') sale_ids,
e.name

from hr_employee e
left join sale_order s on s.employee_id = e.id
where s.date_order between ('2021-08-01' and '2021-08-31') OR NULL

group by e.name