PostGreSQL:使用常量而不是表进行外连接

PostGreSQL:使用常量而不是表进行外连接

这是我的问题,我有一个如下查询:

INSERT INFO table (f1, f2, f3)
    SELECT t1.f1bis, t2.f2bis, t3.f3bis
    FROM   t1, t2, t3
    WHERE  t1.f1 = 'toto' AND
           t2.f2 = 'tata' AND
           t3.f3 ILIKE 'titi';

问题是,如果 t3 不包含“titi”,那么 SELECT 将根本不返回任何行。

我想要的是当 t3 不包含 TITI 时,SELECT 返回 f3bis 作为 NULL

我正在考虑以下查询(忘记 INSERT):

SELECT t1.f1bis, t2.f2bis, t3.f3bis
FROM   t1, t2,
       ((SELECT 'titi' AS f3) AS dummytable)
           LEFT OUTER JOIN
       t3
           ON dummy_table.f3 = t3.f3
WHERE  t1.f1 = 'toto' AND
       t2.f2 = 'tata';

这样可以确保如果 t3 中不存在“titi”,则我将获得 NULL。但查询似乎不起作用。

你能帮助我吗?

谢谢。

答案1

我找到了答案维基百科

外连接的效果也可以通过相关子查询来实现。例如

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName 
FROM   employee LEFT OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

也可以写成

SELECT employee.LastName, employee.DepartmentID,
  (SELECT department.DepartmentName 
    FROM department
   WHERE employee.DepartmentID = department.DepartmentID ) AS DepartmentName
FROM   employee

所以我的问题的答案是:

SELECT t1.f1bis, t2.f2bis,
       (SELECT t3.f3bis
        FROM   t3
        WHERE  t3.f3 = 'titi'
       ) AS f3bis
FROM   t1, t2
WHERE  t1.f1 = 'toto' AND
       t2.f2 = 'tata';

相关内容