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 “Beginner” 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 Beginner:
CREATE Beginner;
Using Database :
USE Beginner;
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.