postgresql: A question on SELECT

I've a query like

SELECT t3 FROM tbl_join WHERE t2=3 AND t1 in (1,2,3);

and the output is:

 t3 
----
  1
  1
(2 rows)

It gives only two rows as entry for t3 is not present. Is it possible to mo开发者_如何学运维dify the SELECT query something like:

SELECT t3||0 FROM tbl_join WHERE t2=3 AND t1 in (1,2,3);

to get the result like

 t3 
----
  1
  1
  0
(3 rows)

I mean, is it possible to write a query where one can get output with no. of rows equal to the no. of arguments in in clause. If an entry is not present output should come as some constant value for the corresponding input

---

you want an outer join: something like

SELECT t.t3 
FROM (VALUES (1), (2), (3)) AS litvals 
    LEFT OUTER JOIN tbl_join as t 
    ON litvals.column1 = t.t1
WHERE t.t2=3

Edit: Hmm. when there is no corresponding row in tbl_join to match up with the VALUES, then t2 is NULL, which of course is not equal to 3. You could deal with this with a subselect:

SELECT t.t3 
FROM (VALUES (1), (2), (3)) AS litvals 
    LEFT OUTER JOIN (SELECT * FROM tbl_join WHERE t2 = 3) AS t 
    ON litvals.column1 = tbl_join.t1