Find whether given conditions based records are matching or not in sql-CodePudding

I have table and wanted to find whether the given condition matches or not with existing table records. I tried with notexists but not helping.

create table user1(id number,name varchar2(20),age number);

insert into user1 values(1,'user1',1);
insert into user1 values(1,'user11',11);
insert into user1 values(2,'user2',2);
insert into user1 values(2,'user22',22);
insert into user1 values(3,'user3',3);
insert into user1 values(4,'user4',4);

select id,age,is_exists from user1 where id = 1 and age in (1,11,111);

**Expected output:**
id,age,is_exists
----------------
1,1,true
1,11,true
1,111,false

CodePudding user response：

You need some kind of table for the three set of values; which you can build using select ... union all. Then use left join or exists:

with cte as (
    select 1 as id, 1 as age from dual union all
    select 1,       11       from dual union all 
    select 1,       111      from dual
)
select cte.*, case when exists (
    select 1
    from user1
    where user1.id = cte.id and user1.age = cte.age
) then 'yes' else 'no' end is_exists
from cte

CodePudding user response：

SQL is designed to return a subset or transformed set of data from the underlying database. For example, FROM specifies that you want all columns and rows from a particular table. The expression list in the SELECT statement narrows down the number of columns you need from this table. The WHERE statement narrows down the number of rows you receive.

Every row in the result set is based on one or more existing rows in the original data set. In either case the maximum result is always the data that exists in the table.

Because you want one row for the age in the result set, you need one row for the age in the query source. If you don't have a table, creating a temporary one with a series of UNION is a good approach.