How to Compare Output of Two Queries in SQL

SQL Server stands out as a robust and widely adopted Relational Database Management System (RDBMS) across diverse software industries. When working with SQL Server, a common task involves comparing the results of two different queries. This capability is crucial for data validation, identifying discrepancies, and ensuring data consistency. This article delves into effective methods for comparing SQL Server query outputs, leveraging Azure Data Studio for practical demonstrations across Windows, Mac, and Linux environments.

Setting Up Your Database

Before diving into query comparisons, let’s establish a basic database and table structure using SQL Server.

First, create a database named GEEKSFORGEEKS:

CREATE DATABASE GEEKSFORGEEKS;

Next, switch to the newly created database to perform operations within it:

USE GEEKSFORGEEKS;

Creating Tables for Comparison

Now, let’s create a table named Authors with a primary key ID. This table will store author information, including their skills and post count.

CREATE TABLE Authors (
    ID INT NOT NULL PRIMARY KEY,
    AuthorName VARCHAR(255),
    Age INT,
    Skillsets VARCHAR(255),
    NumberOfPosts INT
);

In SQL, explicitly specifying NOT NULL enforces that a column must contain values. If not specified, columns default to accepting NULL values.

Populate the Authors table with sample records to demonstrate query comparison techniques.

To compare SQL query outputs, we will primarily utilize the EXCEPT, UNION, and INTERSECT operators. Let’s explore each of these in detail.

Using EXCEPT to Find Differences

The EXCEPT operator in SQL is used to retrieve rows from the first query that are not present in the result set of the second query. It functions similarly to the set difference operation in mathematics.

Example 1: Finding Authors Not Skilled in Java

This query aims to find authors from the Authors table who do not have ‘java’ in their skillsets.

SELECT * from Authors
EXCEPT
SELECT * from Authors WHERE Skillsets LIKE 'java%';

Output:

Explanation:

The first SELECT statement retrieves all records from the Authors table. The second SELECT statement filters authors with skillsets starting with ‘java’. The EXCEPT operator then returns only those records from the first query that are not found in the second query’s results. In essence, it shows authors who do not possess Java skills according to the Skillsets column.

The EXCEPT operator is analogous to the MINUS operator in Oracle SQL.

Example 2: Reversing the Query Order

Consider reversing the order of the queries in the EXCEPT operation:

SELECT * from Authors WHERE Skillsets LIKE 'java%'
EXCEPT
SELECT * from Authors;

In this case, the first query selects authors with ‘java’ skills, and the second query selects all authors. Applying EXCEPT here will yield no results because all authors with ‘java’ skills are indeed present in the set of all authors. This demonstrates how the order of queries matters significantly when using EXCEPT.

The EXCEPT operator is particularly useful for identifying discrepancies between datasets or verifying data consistency across different queries. An empty result set from an EXCEPT query can indicate that the result sets of the two queries are identical.

Note: The EXCEPT operator requires that both queries being compared have the same number of columns in their result sets and that the corresponding columns have compatible data types.

Combining Results with UNION

The UNION operator is used to combine the result sets of two or more SELECT queries into a single result set. It eliminates duplicate rows, providing a distinct set of combined records.

Let’s create a second table, Authors2021, and populate it with author data, potentially overlapping with the Authors table.

Example 3: Combining Authors from Two Tables

This query combines author names, ages, skillsets, and post counts from both the Authors and Authors2021 tables.

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

Output:

Explanation:

The UNION operator combines the rows from both SELECT statements. If there are identical rows in both tables, UNION intelligently removes the duplicates, ensuring each unique record appears only once in the final result set. This is useful when you need a consolidated list of data from multiple sources without redundancy.

Use Cases: UNION is ideal for scenarios like merging customer lists from different regions, consolidating product catalogs from various suppliers, or creating a unified view from partitioned tables.

Including All Results with UNION ALL

The UNION ALL operator is similar to UNION, but it includes all rows from all queries, including duplicates. It simply concatenates the result sets without attempting to remove any redundant rows.

Example 4: Combining All Authors with Duplicates

Using UNION ALL instead of UNION in the previous example will include duplicate records if they exist in both tables.

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

Output:

As you can see in the output, duplicate records are preserved when using UNION ALL. In this case, if an author exists in both Authors and Authors2021 tables with identical information, they will appear twice in the UNION ALL result.

Key Considerations for UNION and UNION ALL:

1. Column Compatibility: Both SELECT statements must retrieve the same number of columns, and the corresponding columns should have compatible data types.
2. Clause Usage: GROUP BY and HAVING clauses can be applied to individual SELECT statements within a UNION or UNION ALL operation, but not directly to the combined result set.
3. Ordering Results: ORDER BY clause can be used to sort the final combined result set, but it cannot be applied to individual SELECT statements within the union.

Finding Common Records with INTERSECT

The INTERSECT operator returns only the common rows that exist in the result sets of both queries. It is essentially the opposite of UNION in terms of its primary function.

Example 5: Identifying Common Authors

This query finds authors who are present in both the Authors and Authors2021 tables based on matching AuthorName, Age, Skillsets, and NumberOfPosts columns.

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

Output:

Explanation:

The INTERSECT operator compares the result sets and returns only the rows that are identical in both. In this example, it identifies authors who have the same information across both the Authors and Authors2021 tables.

Use Cases: INTERSECT is valuable for scenarios like finding customers who purchased products from two different categories, identifying students enrolled in both mathematics and physics courses, or pinpointing common entries across different logs or datasets.

Conclusion

Comparing the output of two queries in SQL Server is a fundamental skill for data professionals. By mastering operators like EXCEPT, UNION, UNION ALL, and INTERSECT, you gain powerful tools to analyze, validate, and manipulate data effectively. These operators enable you to identify differences, combine datasets, and find commonalities, enhancing your ability to work with and understand your SQL Server databases. Practice using these constructs with varied and complex queries to solidify your understanding and expand your SQL proficiency.