Skip to content
Related Articles

Related Articles

Improve Article
Compare and Find Differences Between Two Tables in SQL
  • Last Updated : 23 Apr, 2021

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;
IDSALARYNAMEDEPT_ID
134000ANURAGUI DEVELOPERS
233000HARSHBACKEND DEVELOPERS
336000SUMITBACKEND DEVELOPERS
436000RUHIUI DEVELOPERS
537000KAEUI 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;
IDSALARYNAMEDEPT_ID
134000ANURAGUI DEVELOPERS
233000HARSHBACKEND DEVELOPERS
336000SUMITBACKEND DEVELOPERS
436000RUHIUI DEVELOPERS
537000KAEUI DEVELOPERS
637000REHABACKEND 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.

My Personal Notes arrow_drop_up
Recommended Articles
Page :