postgresqljoinleft-joinsql-nullon-clause

Null result from multiple left Join in postgresql

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.

Solution

  • 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.