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
If no
B
exists for a given name, the conditionB.title = COALESCE($1, B.title)
will never match, it evaluates toNULL
regardless of the value of$1
. Same forC
and$2
.If you want to ignore the condition if the parameter is
NULL
, you should writeYou can also try
or (imo clearer to understand)
but you should check the query plans for those, they seem harder to optimise.
Move the conditions of the
WHERE
clause to their respectiveON
clauses:If
$1
isnull
, the condition:will return
null
and the full condition:will also be
null
, which will lead to a no-match for all the rows ofB
.The same applies to
$2
.