How to optimise SQL query without using NOT IN-CodePudding

I have this Student table:

Id   companyId    status
----------------------------------------
101  1001         In-Progress
102  1001         In-Progress
103  1001         Final
104  1002         In-Progress   
105  1003         Pending With Company 
106  1003         In-Progress
107  1004         In-Progress
108  1004         In-Progress 
109  1005         In-Progress
110  1005         Completed
111  1006         In-Progress
112  1006         Canceled
113  1007         In-Progress
114  1007         Pending with Student

I want output with these conditions:

Status is "In-Progress"
If have repeated companyId then other allowed staus is Completed, Canceled (Basically only allowed combination of the staus is In-Progress, Completed and Canceled)

With above condition o/p will look like this:

Id   companyId    status
--------------------------------
104  1002         In-Progress   
107  1004         In-Progress
108  1004         In-Progress 
109  1005         In-Progress
111  1006         In-Progress

We can achieve this by using NOT IN

SELECT * 
FROM student
WHERE status = 'In-Progress' 
  AND companyId NOT IN (SELECT companyId FROM student 
                        WHERE status IN ('Final', 'Pending With Company ', 'Pending with Student'));

But I'm looking for a solution without using NOT IN.

CodePudding user response：

You can use not exists

SELECT * 
  FROM student a
 WHERE a.status = 'In-Progress' 
   AND NOT EXISTS (SELECT null
                     FROM student b
                    WHERE a.companyid=b.companyid
                      AND b.status not in ('In progress','Completed','Cancelled')
                   )

CodePudding user response：

you can achieve it using left join and choosing not match rows:

   SELECT * FROM student a LEFT JOIN 
   ( SELECT companyId FROM student WHERE status in
   ('Final','Pending With Company ','Pending with Student')) b
   on a.companyid=b.companyId 
   where b.companyId is null
   and a.status = 'In-Progress'

CodePudding user response：

WITH status as (
   select 1 as s,'In-Progress' d union all
   select 100 as s,'Final' d union all
   select 100 as s,'Pending With Company' d union all
   select 100 as s,'Pending with Student' d union all
   select 1 as s,'Cancelled' d union all
   select 1 as s,'Completed'
)
SELECT * FROM (
   SELECT 
      s.Id, 
      s.CompanyID, 
      s.status,
      MAX(status.s) OVER (partition by s.CompanyId) m
   FROM student s
   LEFT jOIN status ON s.status = status.d
) x where x.m=1 and x.status='In-Progress'

Remember, above is simply not using NOT IN. Discussion about this query not being performant is different.