Minus statement 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 rows2 with the same 2 columns in query 2.

when I create query1 query minus query 2, it should only show lines 28-22 = 6. But it displays all 28 rows returned by query1.

Please inform.

Answer 1

Try using EXCEPT instead of MINUS. For example: Let's look at the case when you want to know what tasks are in a table that has not been assigned to you (so basically you are trying to find which tasks can be available).

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

This will return all tasks that were not assigned to you. Hope this helps.

Answer 2

If MINUS will not work for you, the main form you want is the main query in the external selection and the variant of the other query in the 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 3

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

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

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

To get the expected results, this query should contain 22 lines.

Answer 4

something like that:

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

Answer 5

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

If A = {1,3,5} and B = {2,4,6} then AB = {1,3,5}. Here the counter (A) before and after the MINUS operation will be the same, since it does not contain matching terms with the set B.

In similar lines, there may be a result set obtained in query 2, may not have matching terms with the result of query1. Therefore, you still get 28 instead of 6 lines.

Hope this helps.

Answer 6

It returns difference records in the top query that are not contained in the second query.

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