skip to Main Content

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.

2

Answers


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

    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search