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

The fields might not be exactly alike. may be one of the fields is char(10) and the other is char(20) and they both have the string "TEST" in them. They might "look" the same.

If the database you are working on supports "INTERSECT", try this query and see how many are perfectly matching results.

select field1, field2 from table1
intersect 
select field1, field2 from table2

To get the results you are expecting, this query should give you 22 rows.

Answer 2

MINUS works on the same principle as it does in the set operations. Suppose if you have set A and B, A = {1,2,3,4}; B = {3,5,6} then, A-B = {1,2,4}

If A = {1,3,5} and B = {2,4,6} then, A-B = {1,3,5}. Here the count(A) before and after the MINUS operation will be the same, as it does not contain any overlapping terms with set B.

On similar lines, may be the result set obtained in query 2 may not have matching terms with the result of query1. Hence you are still getting 28 instead of 6 rows.

Hope this helps.

Answer 3

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) 

Answer 4

something like this:

select field1, field2, . field_n
 from tables
 MINUS
 select field1, field2, . field_n
 from tables;

Answer 5

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 6

It returns the difference records in the upper query which are not contained by the second query.

In your case for example A={1,2,3,4,5...28} AND B={29,30} then A-B={1,2,3....28}