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:
- Using INNER JOIN.
- 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:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...