Does MINUS QUERY validate each and Every record between the tables?

Question

As far my knowledge , MINUS Query will not validate each and Every column data in a table. It will validate only the # of records is matching from source with # of records in target.

Let s say

Source is having 10 20 30 Target is having 10 40 30

Select Column A from Source MINUS Select Column from Target will give 0 as output. Since there is a single record mismatch will not give 1 as output. Right. Please clarify on this. I knew, but it was become a argument. Thanks for your time.

If you are interested in *MINUS* made on the quantities of duplicates for unique rows, look for *MINUS ALL* or *EXCEPT ALL*. There is SO question asking for description of that clause https://stackoverflow.com/questions/28432720/trying-to-understand-except-all-in-sql-query — jangorecki, Mar 08 '16 at 01:04

score 0 · Answer 1 · answered Jun 05 '15 at 15:04

Did you try it? It's fairly easy to test:

WITH

Source  (COL) AS (
    SELECT 10 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 20 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 30 FROM SYSIBM.SYSDUMMY1
)

,Target (COL) AS (
    SELECT 10 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 40 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 30 FROM SYSIBM.SYSDUMMY1
)

SELECT COL FROM Source 
    MINUS
SELECT COL FROM Target

As you can see from the documentation:

EXCEPT or EXCEPT ALL Derives a result table by combining two other result tables (R1 and R2). If EXCEPT ALL is specified, the result consists of all rows that do not have a corresponding row in R2, where duplicate rows are significant. If EXCEPT is specified without the ALL option, the result consists of all rows that are only in R1, with duplicate rows in the result of this operation eliminated.
For compatibility with other SQL implementations, 
MINUS can be specified as a synonym for EXCEPT.

Therefore, EXCEPT (or MINUS) will only return the rows from the first table that do not have a match in the second table. In this case, you will get 20 back.

You might also add an example showing how it'll remove _all_ matching rows from the first table. — Clockwork-Muse, Jun 06 '15 at 14:07

score -1 · Answer 2 · answered Jun 05 '15 at 14:28

-1

I think you are wrong. Here minus clause will return 20 as record.

For more info read this:- http://www.toadworld.com/platforms/ibmdb2/w/wiki/7696.minus-clause.aspx

answered Jun 05 '15 at 14:28

Ankit Bajpai

13,128
4
25
40

Does MINUS QUERY validate each and Every record between the tables?

2 Answers2