How to create a select clause using a subquery-CodePudding

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?

CodePudding user response：

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)

CodePudding user response：

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

CodePudding user response：

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.