Is there any SQL function or way to do this?-CodePudding

Is there any way we can do this in SQL?

Les say I have table Tablename_1 as below:

id	col1
1	data1
2	data2
3	data3
4	data4
5	data5

I want to check if the data I have in my where clause is present in the table or not for example, where clause is as:

where id in (1,3,5,7);

Then I wish to output as below:

id	data_result
1	YES
3	YES
5	YES
7	NO

CodePudding user response：

There are a few ways to do this.

One option is to provide your IDs as a table-valued constructor (VALUES() clause) instead of a WHERE clause. Then you can LEFT JOIN from this new "table" to create your result.

This is the MySql version (Postgresql needs to remove the row keywords):

select a.n, case when t1.id IS NULL then 'N' else 'Y' end as data_result
from (values row(1), row(3),row(5),row(7)) as a(n)
left join tablename_1 t1 on a.n = t1.id

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=028e5a984e5ed2969247e025bc8776be

You can also do this in a CTE via UNION:

WITH base as (
    SELECT 1 as n UNION SELECT 3 UNION SELECT 5 UNION SELECT 7
)
SELECT base.n, case when t1.id IS NULL then 'N' else 'Y' end as data_result
FROM base
LEFT JOIN tablename_1 t1 on base.n = t1.id

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ef2a88f6f89bf4101d7d651b4440ac28

This works the same in both databases, but as you can see means building a up lot more code per value in your list.

A final option is creating a dynamic numbers table. Then you can again LEFT JOIN these tables together to find out the Yes/No result for all possible values in the dynamic table and then still put your desired values in a WHERE clause to only show those results. The trick here is it requires you to have some idea of the possible scope for your values up front, and can make things a little slower if your guess is too far off.

Any of these could also be written to use an EXISTS() clause, which can often perform better. I just find the JOIN easier to write and reason about.

CodePudding user response：

You could also use exists with the values approach from Joel

Something like

select v.*,
case when exists(select 1 from Tablename_1 as t where t.id=v.id) then 
    'Yes' 
else    
    'No' 
end 
from (values (1),(3),(5),(7)) as v(id)

CodePudding user response：

You can use ANY.

select Tablename_1.id, 
       case 
            when Tablename_1.id = ANY (ARRAY[1, 3,5,7]) 
            then 'YES' 
            else 'NO' end  
           as data_result 
from Tablename_1;

further reading: IN vs ANY operator in PostgreSQL