Pandas IS IN - as a Oracle SQL Query

SELECT
	testtable1.id,
		CASE
			WHEN (testtable1.tn_id IN
		SELECT
			b."customer_id"
		FROM
			cu.customer b)
		 THEN 'Yes'
		ELSE 'No'
	END) BLACKLIST
FROM
	testschema.testtable1

It should check if the value is found in the other table. And then give a output "Yes" or "No" if its found or not.
I want to do this for conditions so there will be multiple "OR".

Thanks in advance.

Wrong usage of parenthesis; should be

SELECT testtable1.id,
       CASE
          WHEN testtable1.tn_id IN (SELECT b."customer_id" FROM cu.customer b)
          THEN 'Yes'
          ELSE 'No'
       END blacklist
  FROM testschema.testtable1

I don't know what you meant to say by

> I want to do this for conditions so there will be multiple "OR"

Or, the same could be written as:

SELECT testtable1.id,
   CASE
      WHEN exists (SELECT 1 FROM cu.customer b
        where b.customer_id=testtable1.tn_id)
      THEN 'Yes'
      ELSE 'No'
   END blacklist
FROM testschema.testtable1

And I'd avoid double quoting column names and table names, that is unless those columns and tables have been created in Oracle to be case-sensitive by double quoting the name in the create DDL. Unless object names are not double quoted on creation, Oracle has them in the data dictionary as uppercase.

There are several options to do it.
Sample data:

WITH
	tbl AS
		(	Select 'A1' "ID", 1 "TN_ID", 'C' "STATUS" From Dual Union All
			Select 'A2' "ID", 2 "TN_ID", 'A' "STATUS" From Dual Union All
			Select 'A3' "ID", 3 "TN_ID", 'B' "STATUS" From Dual Union All
			Select 'A4' "ID", 4 "TN_ID", 'B' "STATUS" From Dual Union All
			Select 'A5' "ID", 5 "TN_ID", 'A' "STATUS" From Dual 
		),
	cust AS
		(	Select 1 "CUSTOMER_ID", 'Name 1' "CUSTOMER_NAME" From Dual Union All
			Select 3 "CUSTOMER_ID", 'Name 3' "CUSTOMER_NAME" From Dual Union All
			Select 4 "CUSTOMER_ID", 'Name 4' "CUSTOMER_NAME" From Dual 
		)

1. your SQL (corrected a bit):

SELECT	t.id,
        CASE  	WHEN t.tn_id IN( SELECT CUSTOMER_ID FROM cust ) THEN 'Yes'
        ELSE 'No'
        END "BLACKLIST"
FROM	tbl t

2. using LEFT JOIN

SELECT	  t.ID, CASE Nvl(c.CUSTOMER_ID, 0) WHEN 0 THEN 'No' ELSE 'Yes' END "BLACKLIST"
FROM	  tbl t
LEFT JOIN cust c ON(c.CUSTOMER_ID = t.TN_ID)
ORDER BY  t.ID

3. using EXISTS

SELECT	  t.ID, CASE WHEN EXISTS(SELECT CUSTOMER_ID FROM cust WHERE CUSTOMER_ID = t.TN_ID) THEN 'Yes' ELSE 'No' END "BLACKLIST"
FROM	  tbl t
ORDER BY  t.ID 

All three returns the same:

ID BLACKLIST
-- ---------
A1 Yes       
A2 No        
A3 Yes       
A4 Yes       
A5 No