How to create a select clause using a subquery

Question

I have the following sql statement:

WITH subquery AS ( select distinct id from a_table where some_field in (1,2,) ) select id from another_table where id in subquery;

Edit

JOIN is not an option (this is just a reduced example of a bigger query)

But that obviously does not work. The id field exists in both tables (with a different name, but values are the same: numeric ids). Basically what I want to do is filter by the result of the subquery, like a kind of intersection.

Any idea how to write that query in a correct way?

... IN (SELECT id FROM subquery) ... But I would recommend to rewrite it as a JOIN. — sticky bit
– sticky bit, Commented Nov 9, 2021 at 18:05
But does that line execute the select for every row on another_table? I'm aware of the JOIN option, but that's not possible (that just a reduced example of another query). — Antonio Gamiz Delgado
– Antonio Gamiz Delgado, Commented Nov 9, 2021 at 18:09
Depends on what the optimizer "thinks" is best. But usually no. The CTE might even be materialized. — sticky bit
– sticky bit, Commented Nov 9, 2021 at 18:10
For a mere lookup I wouldn't join, but use IN or EXISTS. You only got the syntax wrong. sticky bit's IN clause should work just fine for you. — Thorsten Kettner
– Thorsten Kettner, Commented Nov 9, 2021 at 18:14

sticky bit · Accepted Answer · 2021-11-09 18:21:32Z

You need a subquery for the second operand of IN that SELECTs from the CTE.

... IN (SELECT id FROM subquery) ...

But I would recommend to rewrite it as a JOIN.

BobbyW · Accepted Answer · 2021-11-09 18:10:30Z

Are you able to join on ID and then filter on the Where clause?

select a.id from a.table inner join b.table on a.id = b.id where b.column in (1,2)

Stu · Accepted Answer · 2021-11-09 18:12:51Z

Since you only want the id from another_table you can use exists

with s as ( select id from a_table where some_field in (1,2) ) select id from another_table t where exists ( select * from s where s.id=t.id )

But the CTE is really redundant since all you are doing is

select id from another_table t where exists ( select * from a_table a where a.id=t.id and a.some_field in (1,2) )

Collectives™ on Stack Overflow

How to create a select clause using a subquery

Edit

3 Answers 3

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

Edit

3 Answers 3

Comments

1 Comment

Comments

Related