check the existence of value in column

I want to select only that user which has not any packages from packages_table. (In my case: user_id = 4)

I have a package table:

create table package (pack_id, name) as
 select   1,name_1   from dual union all
select   2, name_2 from dual union all
select   3, name_3  from dual ;

user table:

create table user (user_id, pack_id) as
 select   1,  2,  from dual union all
select  1, 3 from dual union all
select  2, 1 from dual  union all
select  3, 3 from dual union all
select  3, 2 from dual union all
select  3, 1 from dual union all
select  4, 6 from dual ;

I TRY:

select id,pack
from 
(select id,decode(sign(sum(decode(pack_id,'1','2','3',1,0)))1,'Yes','No') pack
from user
group by id)
where pack = 'No'

but, it does't work, beacouse it brings users who has package from package_table

CodePudding user response：

You can use a simple NOT EXISTS clause to achieve the result -

SELECT user_id, pack_id
  FROM user U
 WHERE NOT EXISTS (SELECT NULL
                     FROM package P
                    WHERE P.pack_id = U.pack_id);

CodePudding user response：

You should use a left join and check if it's not exists. It should perform better than a subquery depending on your DB.

SELECT U.user_id, P.pack_id
  FROM user U
  LEFT JOIN package P on P.pack_id = U.pack_id
 WHERE P.pack_id IS NULL