What Is a Left Join in Postgresql

What is a LEFT JOIN in PostgreSQL

Where an inner join returns only entries that match in both tables, a left join takes all the entries from first table and any that match in the second table. A right join is the reverse of a left join (ie: all from the second table)

So if TableA is

A B
1 a
2 b
3 c

and TableB is

A B
1 d
2 e

Then Select * from TableA inner join TableB on TableA.A = TableB.A returns

1 a 1 d
2 b 2 e

And Select * from TableA left join TableB on TableA.A = TableB.A returns

1 a 1 d
2 b 2 e
3 c null null  

Postgres LEFT JOIN with WHERE condition

left join two tables with a where condition

It's typically wrong to use a LEFT [OUTER] JOIN and then filter with a WHERE condition, thereby voiding the special feature of a LEFT JOIN to include all rows from the left table unconditionally. Detailed explanation:

• Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail

Put conditions supposed to filter all rows into the WHERE clause (rid = 2), but move conditions on record_table to the join clause:

SELECT t.start_date, t.end_date  -- adding those
     , r.id, r.name, r.date 
FROM   time_table t
LEFT   JOIN record_table r ON r.date >= t.start_date
                          AND r.date <  t.end_date
WHERE  t.rid = 2;

As commented, it makes sense to include columns from time_table in the result, but that's my optional addition.

You also need to be clear about lower and upper bounds. The general convention is to include the lower and exclude the upper bound in time (timestamp) ranges. Hence my use of >= and < above.

Related:

• SQL query on a time series to calculate the average
• Selecting an average of records grouped by 5 minute periods

Performance should be no problem at all with the right indexes.
You need an index (or PK) on time_table(rid) and another on record_table(date).

PostgreSQL LEFT JOIN with SUM & Arithmetic Operators

Aggregate in usage and product_restock separately and then join to product:

select p.id, p.product_name,
       p.current_stock  + coalesce(u.used, 0) - coalesce(r.restock, 0) initial_stock,
       coalesce(r.restock, 0) restock,
       coalesce(u.used, 0) used,
       p.current_stock
from product p
left join (select product_id, sum(used) used from usage where date_out between '2020-11-01' and '2020-11-30' group by product_id) u
on u.product_id = p.id
left join (select product_id, sum(restock_amount) restock from product_restock where date_in between '2020-11-01' and '2020-11-30' group by product_id) r
on r.product_id = p.id

See the demo.

Results:

> id | product_name | initial_stock | restock | used | current_stock
> -: | :----------- | ------------: | ------: | ---: | ------------:
>  1 | abc          |            10 |      30 |   30 |            10
>  2 | aaa          |            20 |       0 |   20 |             0
>  3 | bbb          |            10 |       0 |    0 |            10
>  4 | ddd          |            10 |      10 |    0 |            20

LEFT JOIN query in Postgres

That can't work because of this condition in the WHERE clause:

images.file_type = 3

For rows that don't match at the right side of the LEFT JOIN, the SQL engine will put NULL into all columns corresponding to the right side table. Then, the condition images.file_type = 3 is evaluated to false, as images.file_type is NULL. So the rows without image are eliminated.

In effect, this condition cancels the effect of the LEFT JOIN, making it the equivalent of an INNER JOIN (the same as JOIN alone)

You may solve this by applying the restriction on file_type before the LEFT JOIN, like this:

SELECT i.somecolumn, offers.* FROM
    offers
 LEFT JOIN
   (select * from images WHERE file_type=3) AS i
 ON i.offer_id = offers.id 
 WHERE  $where ...

Make sure that the other clauses in $where are also not conditions ON images.* columns, or they must be processed similarly.

PostgreSQL query: left outer join with where but also null values if not existing

If you move the logic in the WHERE clause to the ON clause, it should work as you expect:

select u.id, u.username, u.mail, a.attribute_value
from users u
left join user_attributes a
on a.user_id = u.id and a.attribute_name = 'loginDateMillis';

Postgres query left join take too time

This is a case where correlated subqueries might be the simplest approach:

select s.sito,
      (select count(*) from invetario_materiali m where s.sito = m.sito) as materiali,
      (select count(*) from struttura_tablest where s.sito = st.sito) as Struttura,
      (select count(*) from tafonomia_table t where s.sito = t.sito) as tafonomia
from (select sito, count(*) as us
      from us_table
      group by sito
     ) s
order by us;

This should be much, much faster than your version for two reasons. First, it avoids the outer aggregation. Second, it avoids the Cartesian products among the tables.

You can make this even faster by creating indexes on each of the secondary tables on sito.

PostgreSQL left join query with one to many relationship

Hi you can do it in this way:

with cte as (
select t1.employee->>'name' "name",
t2.id, t2.valid_from, json_agg(t3.details) "details" 
from employee t1
cross join lateral jsonb_to_recordset(employee->'perks') as t2(id int,valid_from time)
left join perks_details t3 on t2.id::text=t3.details->>'id'
group by 1,2,3
)

select row_to_json(t) from (
select 
name,
json_agg(jsonb_build_object('id',id,'valid_from',valid_from,'details',details)) "perks" 
from cte group by name
) t

DEMO

LEFT JOIN using a link table in PostgreSQL

You can use parentheses to prioritize joins. Like:

SELECT *
FROM   people p
LEFT   JOIN ( people_has_book pb JOIN books b USING (book_id) ) USING (people_id);

This is subtly different from two LEFT JOINs:

SELECT *
FROM   people p
LEFT   JOIN people_has_book pb USING (people_id)
LEFT   JOIN books b USING (book_id);

The latter would show rows from people_has_book even if there is no related entry in books. However, in a classic many-to-many implementation with FK constraints enforcing referential integrity, there is typically no effective difference for your particular query, since all people_has_book.book_id must reference an existing row in books anyway - with the exotic exception of NULL values. (If (people_id, book_id) is the PK of people_has_book, both columns are NOT NULL automatically.)

Related:

• Join four tables involving LEFT JOIN without duplicates
• How to implement a many-to-many relationship in PostgreSQL?

Doing a left join with old style joins

The problem is (not sure if it's standard but most database engines seem to follow it) explicit joins are processed before implicit joins. At the point at which you're doing your join, the only tables/aliases which are in scope are table6alias and alias3. But you're trying to reference table1 in your ON clause for the join.

As you suspected, the solution is to use explicit joins throughout, which also gives you more control over the order in which joins happen.

(Or the quick fix would be to put the LEFT JOIN to alias3 immediately after table1)

---

Related Topics