这是我的问题,我有一个如下查询:
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';