Show the Rows That Are Different Between Two Tables or Queries

Structured Query Language (SQL) is a common database language for building, managing, and retrieving data from relational databases such as MySQL, Oracle, and others.
Here we are going to see how to get the Rows That Are Different Between Two Tables or Queries. 

Here, we will first create a database named “geeksdb” then we will create two tables “Geektab1” and “GeekTab2” in that database. After, that we will execute our query on that tables.

Creating Database :

create geeksdb;

To use this database :

use geeksdb;

Create table Geektab1 in the database :

Create Table Geektab1 (
   ID int,
 NAME varchar (25) );

Add value into the table :

INSERT INTO Geektab1 VALUES (1, 'Neha');
INSERT INTO Geektab1 VALUES (2, 'Ankit');
INSERT INTO Geektab1 VALUES (3, 'Khushi');
INSERT INTO Geektab1 VALUES (4, 'Mahesh');

This is our data inside the table :

SELECT * FROM DEPARTMENT;

ID	NAME
1	Neha
2	Ankit
3	Khushi
4	Mahesh

Create table Geektab2 in the database :

Create Table Geektab2 (
ID int,
NAME varchar (25) );

Add value into the table :

INSERT INTO Geektab1 VALUES (1, 'Neha');
INSERT INTO Geektab1 VALUES (2, 'Ankit');
INSERT INTO Geektab1 VALUES (3, 'Khushi');

This is our data inside the table :

SELECT * FROM DEPARTMENT;

ID	NAME
1	Neha
2	Ankit
3	Khushi
5	Komal

We could use the below keywords in SQL to get the Rows That Are Different Between the Two Tables :

• INTERSECT – Will show us which rows are shared by these two tables.
• EXCEPT – Will show us all the first table’s rows that aren’t in the second table.

We will see if the tables are similar or if there are any variations using these two queries.

Syntax (INTERSECT) :

SELECT * FROM table1
INTERSECT
SELECT * FROM table2 ;

Example :

SELECT * FROM Geektab1
EXCEPT
SELECT * FROM Geektab2 ;

Output :

ID	NAME
1	Neha
2	Ankit
3	Khushi

Syntax (EXCEPT) :

SELECT * FROM table1
EXCEPT
SELECT * FROM table2 ;

Example :

SELECT * FROM Geektab1
EXCEPT
SELECT * FROM Geektab2 ;

Output :

ID	NAME
4	Mahesh

The tables are similar if the number of rows in the first query (INTERSECT) is the same. 

Similarly, if the results of the second query (EXCEPT) are empty, they are equal to the results of a similar query.

My Personal Notes arrow_drop_up

Save