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) );

How to Add value to 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');

How to check Data inside the Table?

SELECT * FROM Geektab1;

Output

Geektab1 Table

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 Geektab2;

Output

Geektab2 Table

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 ;

Query

SELECT * FROM Geektab1
INTERSECT
SELECT * FROM Geektab2 ;

Output

output

Syntax (EXCEPT)

SELECT * FROM table1

EXCEPT

SELECT * FROM table2 ;

Query

SELECT * FROM Geektab1
EXCEPT
SELECT * FROM Geektab2 ;

Output

output

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.