PostGreSQL: outer join with a constant instead of a table

Question

Here is my problem, I have a query looking like:

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';

The problem is that if t3 doesn't contains 'titi', then the SELECT would return no lines at all.

What I would want is that when t3 doesn't contains TITI, the SELECT returns f3bis as NULL

I was thinking about the following query instead (forget about the 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';

That would make sure that if 'titi' doesn't exist in t3, then I would get a NULL. But the query doesn't seem to work.

Can you help me?

Thanks.

Answer 1

I found the answer on Wikipedia

The effect of outer joins can also be obtained using correlated subqueries. For example

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

can also be written as

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

So the answer to my question is the query:

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';