Can I use a table from the WITH clause in the IN clause?

Question

In attempt to write more readable SQL code by following this recommendation, I'm trying to use the WITH clause.

It works here:

WITH
    t AS
    (
        SELECT
            *
        FROM
            TABLE1
        WHERE
            COL1 = 'foo'
    )
SELECT
    *
FROM
    t
WHERE
    COL2 > 42

But it doesn't work in the following case:

WITH
    t AS
    (
        SELECT
            COL1
        FROM
            TABLE1
    )
SELECT
    *
FROM
    TABLE2
WHERE
    COL2 IN t

It returns the following error:

1) [Code: -206, SQL State: 42703] "T" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.22.29

Just in case it's not clear what I mean by the non-working query above, here is what:

SELECT
    *
FROM
    TABLE2
WHERE
    COL2 IN
    (
        SELECT
            COL1
        FROM
            TABLE1
    )

How can I use a table for the WITH clause in the IN clause?

score 3 · Accepted Answer · answered Jun 14 '18 at 11:31

3

"T" acts as a table so you have to select a column in order to compare it with "IN"

WITH
t AS
(
    SELECT
        COL1
    FROM
        TABLE1
)
SELECT
 *
FROM
TABLE2
WHERE
COL2 IN ( SELECT COL FROM t )

answered Jun 14 '18 at 11:31

kiran gadhe

733
3
11

score 1 · Answer 2 · answered Jun 14 '18 at 11:09

1

IN requires a subquery. You simply need:

WHERE COL2 IN (SELECT ? FROM t)

Note that you need to specify the column as well as the table.

answered Jun 14 '18 at 11:09

Gordon Linoff

1,242,037
58
646
786

But what if my subquery is huge? I'm trying to use the `WITH` clause to increase readability. – d_w Jun 14 '18 at 11:12
1

@r_a: then create a second CTE ("with" clause) – Jun 14 '18 at 11:18
2

The query behind `t` can be huge. The subquery you'll ned for the `IN` won't be huge, it's just that `SELECT ... FROM t` regardless of the size of the subquery behind `t`. But you'll need that `SELECT` as with any other table, view, etc. used in `IN`. – sticky bit Jun 14 '18 at 11:18
@a_horse_with_no_name: Could you please give an explicit example? – d_w Jun 14 '18 at 11:33

score 0 · Answer 3 · answered Jun 14 '18 at 11:53

0

I would use exists :

select t2.*
from table2 t2
where exists (select 1 
              from table1 t1
              where t1.COL1 = t2.COL2 and t1.COL1 = <whatever filter>
             );

answered Jun 14 '18 at 11:53

Yogesh Sharma

49,870
5
26
52

Can I use a table from the WITH clause in the IN clause?

3 Answers3