How To Compare Two Query Results In Mysql

In this tutorial, you will learn how to compare two query results in MySQL. This is useful when you need to find differences between two sets of data or when you need to identify records that exist in one data set but not in the other. The following steps will guide you through the process of comparing query results in MySQL.

Step 1: Create Sample Data

Before we start comparing query results, let us create two tables with sample data. The following SQL script creates tables customers_1 and customers_2, and inserts sample data into these tables.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE customers_1 (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL
);
 
INSERT INTO customers_1 (email, name) VALUES
  ('[email protected]', 'Name 1'),
  ('[email protected]', 'Name 2'),
  ('[email protected]', 'Name 3');
 
CREATE TABLE customers_2 (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL
);
 
INSERT INTO customers_2 (email, name) VALUES
  ('[email protected]', 'Name 1'),
  ('[email protected]', 'Name 3'),
  ('[email protected]', 'Name 4');

Step 2: Find common records

To find records that exist in both the customers_1 and customers_2 tables, you can use the INNER JOIN clause. The following query lists the common records in both tables based on the email column:

1
2
3
SELECT * FROM customers_1
INNER JOIN customers_2
ON customers_1.email = customers_2.email;

The output for this query would be:

+----+--------------------+--------+----+--------------------+--------+
| id | email              | name   | id | email              | name   |
+----+--------------------+--------+----+--------------------+--------+
|  1 | [email protected] | Name 1 |  1 | [email protected] | Name 1 |
|  3 | [email protected] | Name 3 |  2 | [email protected] | Name 3 |
+----+--------------------+--------+----+--------------------+--------+

Step 3: Find records exclusive in each table

To find the records that exist exclusively in each table, you can use the LEFT JOIN and RIGHT JOIN clauses with a WHERE clause to filter out the common records. These two queries demonstrate how to get records exclusive in each table:

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Records exclusive in customers_1
SELECT customers_1.*
FROM customers_1
LEFT JOIN customers_2
ON customers_1.email = customers_2.email
WHERE customers_2.email IS NULL;
 
-- Records exclusive in customers_2
SELECT customers_2.*
FROM customers_1
RIGHT JOIN customers_2
ON customers_1.email = customers_2.email
WHERE customers_1.email IS NULL;

The output for each exclusive record query would be:

-- Exclusive in customers_1
+----+--------------------+--------+
| id | email              | name   |
+----+--------------------+--------+
|  2 | [email protected] | Name 2 |
+----+--------------------+--------+

-- Exclusive in customers_2
+----+--------------------+--------+
| id | email              | name   |
+----+--------------------+--------+
|  3 | [email protected] | Name 4 |
+----+--------------------+--------+

Full code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Create tables and insert data
CREATE TABLE customers_1 ( ... );
INSERT INTO customers_1 ( ... );
 
CREATE TABLE customers_2 ( ... );
INSERT INTO customers_2 ( ... );
 
-- Compare queries
-- Common records
SELECT * FROM customers_1 ...;
 
-- Exclusive records
SELECT customers_1.* FROM customers_1 ...;
SELECT customers_2.* FROM customers_1 ...;

Conclusion

Comparing query results in MySQL is an essential skill when working with data sets that require comparison, identification of commonalities or finding exclusive records. In this tutorial, you learned how to use INNER JOIN, LEFT JOIN, and RIGHT JOIN with WHERE clauses to compare and analyze two different tables.