postgresql: A question on SELECT

Question

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 modify 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

It seems as if you assume, that the values in t3 are in the same order, as (t1 in (1, 2, 3)), and (1,1,0) would mean no t1=3 value. But if you don't order your result set, you don't know, to which value 1, 1 and 0 belong. — user unknown
– user unknown, Commented Jul 23, 2011 at 0:53

SingleNegationElimination · Accepted Answer · 2011-07-22 23:11:08Z

1

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

edited Jul 22, 2011 at 23:11

answered Jul 22, 2011 at 21:22

SingleNegationElimination

157k35 gold badges269 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mu is too short Over a year ago

They'll probably want a COALESCE in there too.

Mayank Over a year ago

This gives only 2 rows.SELECT t3 FROM (VALUES (1), (2), (3)) AS litvals JOIN tbl_join ON litvals.column1 = tbl_join.t1 WHERE t2=3; Am I missing something

Mayank Over a year ago

ohh... but...

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

is giving 2 rows :(

Collectives™ on Stack Overflow

postgresql: A question on SELECT

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related