0

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
bluestacks454
  • 151
  • 7
  • The WHERE clause changes the joins to INNER joins. Set the conditions in the ON clauses. – forpas Feb 26 '23 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 Feb 26 '23 at 18:50
  • Null result means zero rows. – bluestacks454 Feb 26 '23 at 18:53
  • 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 `null`s? – forpas Feb 26 '23 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 Feb 26 '23 at 19:04
  • LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN a WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS This is a faq. But one must pin down via a [mre] & write many clear, concise & precise phrasings of one's question/problem/goal to search reasonably. PS Please clarify via edits, not comments. – philipxy Feb 27 '23 at 09:12
  • [Left Join With Where Clause](https://stackoverflow.com/q/4752455/3404097) etc etc. [Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?](https://stackoverflow.com/a/55111083/3404097) – philipxy Feb 27 '23 at 09:13

2 Answers2

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.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • 1
    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. – Bergi Feb 26 '23 at 19:17
  • 1
    @Bergi this is based on the asker's latest comment which I already edited in the question. – forpas Feb 26 '23 at 19:18
  • This solution works for me! Thank you all for the help! – bluestacks454 Feb 26 '23 at 19:25