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

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.

Syntax: NOT IN 

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 the 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 match from entry in demo_table2.

Alternate Method:

Alternatively, to exclude rows from demo_table1 whose values in the NAME column match the rows in the NAME column in demo_table2, we can perform it with left join.

As we know, the left join returns all the rows in the left table and only matches rows in the right table, so the right table column's values will be NULL if they don't have matching rows. Hence, we will this concept of the left join with a condition that the right table's column value is null.

The query for the above content using Left Join and Condition:

select  t1.* from demo_table1 t1 left join demo_table2 t2 on t1.name = t2.name where t2.name is null

This returns, the required data set.

romy421kumari

Follow

Improve

Article Tags :

• SQL
• SQL-Server
• SQL-Query