Compare and Find Differences Between Two Tables in SQL

Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, etc. Here we are going to see how to Compare and Find Differences Between Two Tables in SQL

Here, we will first create a database named “geeks” then we will create two tables “department_old” and “department_new” in that database. After, that we will execute our query on that table.

Creating Database:.

Use the below SQL statement to create a database called geeks:

CREATE geeks;

Using Database :

USE geeks;

Table Definition for department_old table:

CREATE TABLE department_old(
 ID int,
 SALARY int,
 NAME Varchar(20),
 DEPT_ID Varchar(255));

Add values into the table:

Use the below query to add data to the table:

INSERT INTO department_old VALUES (1, 34000, 'ANURAG', 'UI DEVELOPERS');
INSERT INTO department_old VALUES (2, 33000, 'HARSH', 'BACKEND DEVELOPERS');
INSERT INTO department_old VALUES (3, 36000, 'SUMIT', 'BACKEND DEVELOPERS');
INSERT INTO department_old VALUES (4, 36000, 'RUHI', 'UI DEVELOPERS');
INSERT INTO department_old VALUES (5, 37000, 'KAE', 'UI DEVELOPERS');

To verify the contents of the table use the below statement:

SELECT * FROM department_old;

ID	SALARY	NAME	DEPT_ID
1	34000	ANURAG	UI DEVELOPERS
2	33000	HARSH	BACKEND DEVELOPERS
3	36000	SUMIT	BACKEND DEVELOPERS
4	36000	RUHI	UI DEVELOPERS
5	37000	KAE	UI DEVELOPERS

The result from SQL Server Management Studio: 

Table Definition for department_new table:

CREATE TABLE department_new(
ID int,
SALARY int,
NAME Varchar(20),
DEPT_ID Varchar(255));

Add values into the table:

Use the below query to add data to the table:

INSERT INTO department_new VALUES (1, 34000, 'ANURAG', 'UI DEVELOPERS');
INSERT INTO department_new VALUES (2, 33000, 'HARSH', 'BACKEND DEVELOPERS');
INSERT INTO department_new VALUES (3, 36000, 'SUMIT', 'BACKEND DEVELOPERS');
INSERT INTO department_new VALUES (4, 36000, 'RUHI', 'UI DEVELOPERS');
INSERT INTO department_new VALUES (5, 37000, 'KAE', 'UI DEVELOPERS');
INSERT INTO department_new VALUES (6, 37000, 'REHA', 'BACKEND DEVELOPERS');

To verify the contents of the table use the below statement:

SELECT * FROM department_new;

ID	SALARY	NAME	DEPT_ID
1	34000	ANURAG	UI DEVELOPERS
2	33000	HARSH	BACKEND DEVELOPERS
3	36000	SUMIT	BACKEND DEVELOPERS
4	36000	RUHI	UI DEVELOPERS
5	37000	KAE	UI DEVELOPERS
6	37000	REHA	BACKEND DEVELOPERS

Output:

Comparing the Results of the Two Queries

Let us suppose, we have two tables: table1 and table2. Here, we will use UNION ALL to combine the records based on columns that need to compare. If the values in the columns that need to compare are the same, the COUNT(*) returns 2, otherwise the COUNT(*) returns 1.

Syntax:

SELECT column1, column2.... columnN
FROM
( SELECT table1.column1, table1.column2
 FROM table1
 UNION ALL
 SELECT table2.column1, table2.column2
 FROM table2
)  table1
GROUP BY column1
HAVING COUNT(*) = 1

Example:

Select ID from
( select * from department_old
UNION ALL
select * from department_new)
department_old
GROUP BY ID
HAVING COUNT(*) = 1

Output:

If values in the columns involved in the comparison are identical, no row returns.