Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

How to Querying Two Tables For Duplicate Values in SQL?

  • Last Updated : 16 Nov, 2021

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:

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!