Compare SQL Server Results of Two Queries

SQL Server is a versatile database, and it is the most used Relational Database that is used across many software industries. In this article, let us see the comparison of SQL Server Results of Two Queries briefly. By using Azure Data Studio, let us see the concepts by starting with creating the database, table creation, locks, etc., Azure Data Studio works well for Windows 10, Mac, and Linux environments. It can be installed from

Database creation :

Command to create the database.  Here GEEKSFORGEEKS is the db name

CREATE DATABASE GEEKSFORGEEKS;

Make the database active

USE GEEKSFORGEEKS;

Adding the tables to the database :

Creating a table with Primary Key. Here ID is a PRIMARY KEY meaning each author  will have their own ID

CREATE TABLE Authors (
  ID INT NOT NULL PRIMARY KEY,
  <other column name1> <datatype> <null/not null>,
  ..........
);

If explicitly “NOT NULL” is specified, that column should have values. If not specified, it is by default “NULL”.

Let us insert few records into the table and check the result contents of the table.

Author table contents

For Comparison of SQL queries, we can use “UNION, INTERSECT, EXCEPT” CONSTRUCTS. We can check one by one

EXCEPT : 

SQL EXCEPT works similar to the way of “-” operator in Maths. That is it returns the records from Left Query which are not present in the records from Right Query.

Example 1 :

 SELECT * from Authors 
 EXCEPT
 SELECT * from Authors WHERE Skillsets like 'java%' 

Output :

Except for construct results

Explanation :

The left query produces all 4 records of the table whereas the right query produces only the 1st and 4th records. Since the “EXCEPT” construct is used, the resultant of the above query will produce second and third records only (that are not available from the 2nd query).

“EXCEPT” construct is equivalent to the “MINUS” construct in oracle.

Example 2 :

SELECT * from Authors WHERE Skillsets like 'java%'  
 EXCEPT
 SELECT * from Authors 

Can you confirm what might be the output for this?

Yes, exactly, there is no output as the Left query contains only 1st and 4th rows and the right query contains all rows and when “EXCEPT” is applied, we are seeing only blank data.

Usually, “EXCEPT” is used to find out the results of the query that should be identical or not and when the result shows no data means, the query results are identical.

Note: EXCEPT can be placed between queries only when the resultant output of both the queries are the same. If they differ, we cannot apply “EXCEPT”

UNION :

Whenever there is a requirement like combining data across multiple tables or combining data of a single table that evolved from different queries, we can go for UNION. This is equivalent to “UNION” in Maths.

Let there be another table named “Authors2021” and have few records as follows:

Author2021 table contents

Example 3 :

SELECT AuthorName,Age,Skillsets,NumberOfPosts from Authors   
UNION
SELECT AuthorName,Age,Skillsets,NumberOfPosts from Authors2021

Output :

Union of two table result sets, here the common record is displayed only once thus avoiding duplicates

Explanation :

If you look at the output, the data from both the tables are combined and hence we can see the above output.

While Union is applied, if there are common records in both the tables, it will take the data from one table and thus avoid duplication. So the resultant of Union will be the combination of all records in both the tables. i.e the result set produced by 1st query is combined with the result set produced by the 2nd query.

If we check the output, we do not have duplicate records.

Usage: Whenever we need to show the cumulative output by avoiding duplicates, we can go for UNION.

UNION ALL :

This is similar to UNION but includes duplicate records and hence repetition of records are seen

Example 4 :

SELECT AuthorName,Age,Skillsets,NumberOfPosts from Authors    
UNION ALL
SELECT AuthorName,Age,Skillsets,NumberOfPosts from Authors2021

Output :

If you check the output, in 1st and eighth-row, retrieved column values are identical. Since we have used “Union ALL” the result is from both the tables including duplicates

Important Note to be considered while applying “UNION” and “UNION ALL”

1. Equal number of columns should be present and their order also should be maintained in the select statements
2. Group By and Having clause is possible on each select query but not on the whole resultset
3. Order By cannot be used with each individual statement  but with the entire resultset, it can be applied.

INTERSECT:

This Construct provides the commonly occurring records between two select queries. This is the opposite of UNION. UNION removes the duplicate and shows all the records, but INTERSECT shows only the common records. So, whenever there is a scenario of showing common records, we can use INTERSECT

 Example 5 :

SELECT AuthorName,Age,Skillsets,NumberOfPosts from Authors    
INTERSECT
SELECT AuthorName,Age,Skillsets,NumberOfPosts from Authors2021

Output :

Explanation :

While comparing both Author and Author2021 table contents, for the fieldnames like AuthorName, Age, Skillsets, NumberOfPosts, only above shown data is common and hence in the output, we are seeing this.

Conclusion :

By using UNION, UNION ALL, EXCEPT, and INTERSECT, we can compare two queries and get the necessary output as per our requirement. Given examples are simple to follow, we can have complex queries and can apply the mentioned constructs in them and can compare.

priyarajtt

Follow

Improve

Article Tags :

• SQL
• SQL-Query
• SQL-Server