Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

SQL Query to Exclude Records if it Matches an Entry in Another Table

  • Last Updated : 28 Oct, 2021

In this article, we will see, how to write the SQL Query to exclude records if it matches an entry in another table. We can perform the above function using the NOT IN operator in SQL. For obtaining the list of values we can write the subquery.

NOT IN operators acts as a negation of In operator and return the results excluding the items present in the specified list.

NOT IN Syntax:

SELECT * FROM table_name WHERE column_name NOT IN (list);

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(
NAME VARCHAR(20),
AGE INT,
CITY VARCHAR(20) );

Query(demo_table2):

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

Step 4: Insert data into a table

Query(demo_table1):

INSERT INTO demo_table1 VALUES
('Romy',23,'Delhi'),
('Rahul',23,'Delhi'),
('Nikhil',24,'Punjab'),
('Ranvir',23,'Punjab'),
('Samiksha',23,'Banglore'),
('Ashtha',24,'Banglore'),
('Tannu',30,'Patna'),
('Girish',30,'Patna'),
('Ram', 20 , 'Patna'),
('Raj', 12, 'Delhi');

Query(demo_table2):

INSERT INTO demo_table2 VALUES
('Fanny',25 ),
('Prem', 30), 
('Preeti',21),
('Samita',32),
('Rahul',23),
('Ranvir',23);

Step 5: View the content

Execute the below query to see the content of the table

Query:

SELECT * FROM demo_table1;

Output:

Query:

SELECT * FROM demo_table2;

Output:

Step 6: Exclude data from demo_table1 based on matches found in demo_table2

For the demonstration, exclude the data from demo_table1 whose values in the NAME column match the entries in the Name column of demo_table2.

Query:

SELECT * FROM demo_table1 WHERE NAME NOT IN (SELECT NAME FROM demo_table2);

Output:

We can see in the image that two entries are excluded as the values matches from entry in demo_table2.

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up

Start Your Coding Journey Now!