What are MINUS queries?

Question

I am currently watching videos for database testing. I do not understand the term MINUS queries. Can you explain what are MINUS queries?

Answer 1

A Minus Query is a Query that simply uses "-" operator of SQL to subtract one result set from another result set to evaluate the result set difference.If there is no difference, there is no remaining result set. If there is a difference, the resulting rows will be displayed.

Comments

Thank you for that.

Answer 2

A MINUS query is a query which uses MINUS (-) operator to subtract one result set from another.

These queries are mainly used to find any potential difference between the data and if such difference is found in the result sets, then the resulting rows are displayed.

If there is no difference in both the result sets, then no result is displayed for MINUS query.

MINUS query data testing technique is very popular in software testing services for validating the data in ETL testing and ETL mapping specifications.

The basic technique is to use source-minus-target and target-minus-source queries to find any differences in the data.

These queries can be used on either of the database : Source or target.

Comments

Thank you Rajeev

Answer 3

What is a Minus Query?

The data testing method “Minus Query” is one of the most preferred methods used by QA testing companies for ETL testing. It is used qa services to validate that the ETL mapping specifications are correctly implemented.

This query uses MINUS operator in SQL for subtracting two sets and evaluate the set difference. The difference will be displayed in the rows and in case of no difference, no result set will be displayed.

The MINUS operator returns the rows from first SELECT statement which are not returned by the second SELECT statement.

How Do You Test with Minus Queries?
The Minus Queries are performed as the source-minus-target and target-minus-source. This query is used to make sure that no duplicate data exists in two datasets and the unnecessary columns are removed before loading the data.

The MINUS query will return the records that exist in Dataset1 and not in Dataset2 and display them in blue shaded area.

Condition for using Minus Statement: The SELECT statements in MINUS query should have same data types and same number of fields in result sets.

Syntax

The syntax for the MINUS operator in SQL is:

SELECT expression1, expression2, ... expression_nFROM tables [WHERE conditions] MINUS SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];

 

Example - With Single Expression

SQL MINUS operator with one field and same data type:

SELECT supplier_id FROM suppliers MINUS SELECT supplier_id FROM orders;