Minus operator in sql

Question

I am trying to create a sql query with minus.

I have query1 which returns 28 rows with 2 columns I have query2 which returns 22 row2 with same 2 columns in query 2.

when I create a query query1 minus query 2 it should have only show the 28-22=6 rows. But it showing up all the 28 rows returned by query1.

Please advise.

Answer 1

Try using EXCEPT instead of MINUS. For Example: Lets consider a case where you want to find out what tasks are in a table that haven't been assigned to you(So basically you are trying to find what tasks could be available to do).

SELECT TaskID, TaskType
FROM Tasks
EXCEPT
SELECT TaskID, TaskType
FROM Tasks
WHERE Username = 'Vidya'

That would return all the tasks that haven't been assigned to you. Hope that helps.

Answer 2

If MINUS won't work for you, the general form you want is the main query in the outer select and a variation of the other query in a not exists clause.

select <insert list of fields here>
from mytable a
join myothertable b 
on b.aId = a.aid
where not exists (select * from tablec c where a.aid = c.aid)