Question 193252 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – lateral join with a right join

so I have two tables, A and B.

first I do a lateral join of A with one column (items)

select A.id, item->>'id' as item_id, b.id 
from a, jsonb_array_elements(items) rx(item)

enter image description here

then I do a right join with table b (has only 2 rows, with id 1 and 2)

select A.id, item->>'id' as item_id, b.id 
from a, jsonb_array_elements(items) rx(item) 
right join b on b.id::varchar = item->>'id'

enter image description here

my main question is, why didn’t the right join take out the rows where item_id = 3 and 4? why still null there. On the other hand, if I change the cross lateral join to left lateral join, it solves my problem. But I want to understand the evaluation order in this particular query.

Tags:

2

Answers


  1. 0

    As I remember, you use RIGHT join to get all the records from right side (table b in your example) and their corresponding records in left side (table a) using the ON clause conditions. If the record in right side doesn’t have a corresponding record in left side, then the selected fields FROM left are NULL.

    Use LEFT join to get the contrary effect, or switch the tables each other.

    If you want get just the exact corresponding records in both tables (sides), use JOIN with the ON clause needed (or INNER JOIN, is the same).

    If you use JOIN without the ON clause (or FROM A, B without JOIN OR WHERE), you will get the cartesian product: all records from LEFT side combined (plus) all records from RIGTH side.

    Login or Signup to reply.
  2. 0

    On the other hand, if I change the cross lateral join to left lateral join, it solves my problem.

    You didn’t have a cross join to start with, you had a comma join. Those are not quite identical, as the comma join has lower precedence. If you change it from a comma join to a true cross join, it also solves the problem.

    I think this is probably a bug. The comma join should throw an error because the "items" column should be unavailable at the point where it is needed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search