英文:
Null result from multiple left Join in postgresql
问题
我有3张数据库表，A、B和C，它们共享相同的列 "name"。
B有一个属性 "title"，C有一个属性 "age"。
我想编写一个SQL查询，根据查询输入选择A的所有内容，无论是B.title、C.age还是它们都选择。
到目前为止，我尝试过：
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和$2来自外部程序。一些示例可能是$1 = "Jones"，$2 = 12。
我从上面的查询中获得了一个空结果。而且左连接似乎非常昂贵。有没有更快的方法来实现这个，而不需要连接这些表。
如果$1为null，那么查询将不会与B表连接。我想从A中获取名称。如果$2为null，那么查询将不会与C表连接，依此类推。如果两者都为null，它将返回A拥有的内容。
英文:
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.
答案1
得分: 1
如果给定名称没有B存在，条件B.title = COALESCE($1, B.title)将永远不匹配，它会评估为NULL，无论$1的值如何。对于C和$2也是一样的。
如果要忽略参数为NULL时的条件，您应该编写：
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)
您也可以尝试：
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)
或者（在我看来更容易理解的方式）：
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)
但是，您应该检查这些查询计划，它们似乎更难优化。
英文:
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.
答案2
得分: 1
将WHERE子句中的条件移至各自的ON子句中：
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;
如果$1是null，条件：
B.title = $1
将返回null，并且完整的条件：
A.name = B.name AND B.title = $1
也将为null，这将导致B的所有行都无法匹配。
对于$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.<br/>
The same applies to $2.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论