PostgreSQL - 使用特定查询得到错误的结果

Question

我的SQL查询有一点问题。在我的数据库中，有3个表格table1，table2，table3和一些字段。在这种情况下，每个表只有最多3个字段（id，field1，field2）是相关的，我试图在下面的查询中保持简单。

我有以下查询：

SELECT t1.field1, t1.field2 
FROM table1 t1, table2 t2, table3 t3 
WHERE t3.field1 = 'param1' 
    AND t3.id = t1.t3_id 
    AND t1.field2 = 'param2' 
    OR t1.field2 IS NULL 
    AND t2.field1 = 'param3' 
    AND t2.id = t1.t2_id 

出于某种原因，我总是得到错误的结果，所以我决定加入

SELECT t2.id

和

AND t2.id = 1

测试它

SELECT t1.field1, t1.field2, t2.id 
FROM table1 t1, table2 t2, table3 t3 
WHERE t3.field1 = 'param1' 
    AND t3.id = t1.t3_id 
    AND t1.field2 = 'param2' 
    OR t1.field2 IS NULL 
    AND t2.field1 = 'param3' 
    AND t2.id = t1.t2_id 
    AND t2.id = 1 

在我的结果集

现在，仍然有条目与t2.id！= 1，我怎样才能避免这种情况？

Answer 1

这是导致混淆的OR。把它放在括号内：

SELECT t1.field1, t1.field2, t2.id 
FROM table1 t1, table2 t2, table3 t3 
WHERE t3.field1 = 'param1' 
    AND t3.id = t1.t3_id 
    AND (t1.field2 = 'param2' OR t1.field2 IS NULL) 
    AND t2.field1 = 'param3' 
    AND t2.id = t1.t2_id 
    AND t2.id = 1 

重新写了现代化的，明确的JOIN语法：

SELECT t1.field1, t1.field2, t2.id 
FROM table1 t1 
JOIN table2 t2 ON t2.id = t1.t2_id 
JOIN table3 t3 ON t3.id = t1.t3_id 
WHERE t3.field1 = 'param1' 
    AND (t1.field2 = 'param2' OR t1.field2 IS NULL) 
    AND t2.field1 = 'param3' 
    AND t2.id = 1