Case when column a from tableX is present in column b of tableY

Below is my use case, i’m querying redshift tables, using case when but get error in case when statement. ERROR: Statement 2 is not valid. ERROR: syntax error at or near “b”

MY SQL query:

CREATE TEMP TABLE TABLE1 AS
(SELECT
COL1
,COL2
,COL3
FROM XYZ_TABLE
WHERE CONDITION1
AND CONDITION2);

CREATE TEMP TABLE TABLE2 AS
(SELECT DISTINCT
COL1
FROM ABC_TABLE
WHERE CONDITION1
AND CONDITION2);


SELECT
COL1
,COL2
,COL3
,CASE WHEN (a.COL1 IN b.COL1) THEN 1 ELSE 0 END AS IN_TABLE_B
FROM TABLE1 a
LEFT JOIN TABLE2 b
WHERE a.COL1 = b.COL1

What i want to achieve:

TABLE1
-----------------
ID   |  NAME   | COL1
-----------------------
123  |   A    | BLA
234  |   B    | BLAA
345  |   C    | BLAH
456  |   X    | XXX
567  |   N    | FLS


TABLE2
-----------------
ID   |  COL1   | COL2
-----------------------
123  |   SKLJF    | BLA
345  |   DKLUF    | BLAH
567  |   KKBDL    | FLS

DESIRED OUTPUT ( IS ID IN TABLE1 PRESENT IN TABLE2, IF YES THEN 1 ELSE 0 END AS COLUMN_NAME)

----------------------------------------------
ID   |  IN TABLE B   |
----------------------------------------------
123  |   1    | 
234  |   0    | 
345  |   1    | 
456  |   0    | 
567  |   1    | 

Answer

You wouldn’t use “IN” for this even though your desire mentions the word “in”

You’re performing a left join, which will have a value for b.ID where the join succeeded and a null where it failed, hence:

SELECT
a.ID
,CASE WHEN b.ID IS NULL THEN 0 ELSE 1 END AS IN_TABLE_B
FROM TABLE1 a
LEFT JOIN TABLE2 b
WHERE a.ID = b.ID