Null result from multiple left Join in postgresql

Question

I have 3 tables in a database A, B and C which share the same column "name". B has an attribute "title" and C has an attribute "age".

I am looking to write a SQL query where I will have to choose all the contents of A based on query input either B.title, C.age or all of them.

What I have tried so far,

SELECT * FROM A
LEFT JOIN B ON A.name = B.name
LEFT JOIN C ON A.name = C.name 
WHERE B.title = COALESCE($1, B.title)
AND C.age = COALESCE($2, C.age)

$1 and $2 are coming from an external program. Some examples would be $1 = "Jones" and $2 = 12.

I am getting a null result from the above query. Also Left Join seems to be very expensive. Are there faster ways to achieve this without joining the tables.

If $1 is null then the query will not have any join from B table. I would like to get back the names from A. If $2 is null then the query will not join C table and so on. If both are null it will return whatever A has.

The WHERE clause changes the joins to INNER joins. Set the conditions in the ON clauses. — forpas
– forpas, Commented Feb 26, 2023 at 18:16
What is "a null result"? Zero rows? A row in which every column is NULL? A row in which at least one column is NULL? — jjanes
– jjanes, Commented Feb 26, 2023 at 18:50
If $1 is null then what do you want in the results? Do you want the titles that are null or any row? Do the columns title and age contain nulls? — forpas
– forpas, Commented Feb 26, 2023 at 18:59
If $1 is null then the query will not have any join from B table. I would like to get back the names from A. If $2 is null then the query will not join C table and so on. If both are null it will return whatever A has. — bluestacks454
– bluestacks454, Commented Feb 26, 2023 at 19:04

forpas · Accepted Answer · 2023-02-26 19:14:09Z

1

Move the conditions of the WHERE clause to their respective ON clauses:

SELECT * 
FROM A
LEFT JOIN B ON B.name = A.name AND B.title = $1
LEFT JOIN C ON C.name = A.name AND C.age = $2;

If $1 is null, the condition:

B.title = $1

will return null and the full condition:

A.name = B.name AND B.title = $1

will also be null, which will lead to a no-match for all the rows of B.

The same applies to $2.

answered Feb 26, 2023 at 19:14

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bergi Over a year ago

Ah, this is a different interpretation of the question. It selects all rows from A, and may or may not have columns for the B and C tables depending on the conditions.

forpas Over a year ago

@Bergi this is based on the asker's latest comment which I already edited in the question.

Bergi · Accepted Answer · 2023-02-26 19:14:13Z

If no B exists for a given name, the condition B.title = COALESCE($1, B.title) will never match, it evaluates to NULL regardless of the value of $1. Same for C and $2.

If you want to ignore the condition if the parameter is NULL, you should write

SELECT * FROM A
WHERE ($1 IS NULL OR (SELECT title FROM B WHERE B.name = A.name) = $1)
  AND ($2 IS NULL OR (SELECT age FROM C WHERE C.name = A.name) = $2)

You can also try

SELECT * FROM A
LEFT JOIN B USING (name)
LEFT JOIN C USING (name)
WHERE B.title IS NOT DISTINCT FROM COALESCE($1, B.title)
  AND C.age IS NOT DISTINCT FROM COALESCE($2, C.age)

or (imo clearer to understand)

SELECT * FROM A
LEFT JOIN B USING (name)
LEFT JOIN C USING (name)
WHERE ($1 IS NULL OR $1 = B.title)
  AND ($2 IS NULL OR $2 = C.age)

but you should check the query plans for those, they seem harder to optimise.

Collectives™ on Stack Overflow

Null result from multiple left Join in postgresql

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related