How to Querying Two Tables For Duplicate Values in SQL?

In this article, we will see how to write SQL queries to get duplicate values from two tables.

We can perform the given task using two methods:

1. Using INNER JOIN.
2. Using WHERE clause

Now, for the demonstration follow the below steps:

Step 1: Create a database

we can use the following command to create a database called geeks.

Query:

CREATE DATABASE geeks;

Step 2: Use database

Use the below SQL statement to switch the database context to geeks:

Query:

USE geeks;

Step 3: Table definition

We have two tables named ‘demo_table1’ and ‘demo_table2’ in our geek’s database.

Query(demo_table1):

CREATE TABLE demo_table1(
ID int,
NAME VARCHAR(20),
AGE INT,
CITY VARCHAR(20) );

Query(demo_table2):

CREATE TABLE demo_table2(
ID int,
NAME VARCHAR(20),
AGE int);

Step 4: Insert data into a table

Query(demo_table1):

INSERT INTO demo_table1 VALUES
(11,'Romy',23,'Delhi'),
(23,'Rahul',23,'Delhi'),
(31,'Nikhil',24,'Punjab'),
(46,'Ranvir',23,'Punjab'),
(52,'Samiksha',23,'Banglore'),
(61,'Ashtha',24,'Banglore'),
(77,'Tannu',30,'Patna'),
(89,'Girish',30,'Patna');

Query(demo_table2):

INSERT INTO demo_table2 VALUES
(31,'Fanny',25 ),
(77,'Prem', 30),
(15,'Preeti',21),
(46,'Samita',32),
(09,'Rajan',45);

Step 5: View the content

Execute the below query to see the content of the table

Query(demo_table1):

SELECT * FROM demo_table1;

Output:

Query(demo_table2):

SELECT * FROM demo_table2;

Output:

STEP 6: SQL query to get duplicates from two tables

Method 1:

INNER JOIN: It is a keyword used for querying two tables to get the records having matching values in both the table.

Syntax:

SELECT Column_name
FROM table1 INNER JOIN
table2 ON condition;

For demonstration, we will write SQL query that will return duplicate ID values from both the tables

Query:

SELECT demo_table1.ID
FROM demo_table1
INNER JOIN demo_table2
ON demo_table1.ID=demo_table2.ID;

If two tables have the same column name, the table name should be used before the name of column name like table_name.column_name to differentiate the column of both the tables.

Output:

Method 2: By using where clause to compare column values

Syntax:

SELECT column_name from table_name WHERE condition;

Query:

SELECT demo_table1.ID
FROM demo_table1, demo_table2
WHERE demo_table1.ID=demo_table2.ID;

Output: