How to Select All Records from One Table That Do Not Exist in Another Table in SQL?

We can get the records in one table that doesn’t exist in another table by using NOT IN or NOT EXISTS with the subqueries including the other table in the subqueries. In this let us see How to select All Records from One Table That Do Not Exist in Another Table step-by-step.

Creating a Database

Use the below command to create a database named GeeksforGeeks:

CREATE DATABASE GeeksforGeeks

Using the Database

To use the GeeksforGeeks database use the below command:

USE GeeksforGeeks

Creating a Table :

Create a table employee_details with  4 columns using the following SQL query: 

CREATE TABLE employee_details(
   emp_id VARCHAR(8),
   emp_name VARCHAR(20),
   emp_designation VARCHAR(20),
   emp_age INT);

CREATE TABLE employee_resigned(
   emp_id VARCHAR(8),
   emp_name VARCHAR(20),
   emp_designation VARCHAR(20),
   emp_age INT);

Verifying the table:

To view the description of the tables in the database using the following SQL query:

EXEC sp_columns employee_details;
EXEC sp_columns employee_resigned;

Inserting data into the Table 

Inserting rows into employee_details and employee_resigned tables using the following SQL query:

INSERT INTO employee_details VALUES
  ('E40001','PRADEEP','H.R',36),
  ('E40002','ASHOK','MANAGER',28),
  ('E40003','PAVAN KUMAR','ASST MANAGER',28),
  ('E40004','SANTHOSH','STORE MANAGER',25),
  ('E40005','THAMAN','GENERAL MANAGER',26),
('E40006','HARSH',' ANALYST',25),
  ('E40007','SAMHITH','GENERAL MANAGER',26),
('E40008','SAMEER','SENIOR ANALYST',25),
  ('E40009','RISABH','BUSINESS ANALYST',26);

INSERT INTO employee_resigned VALUES('E40001','PRADEEP','H.R',36),
  ('E40004','SANTHOSH','STORE MANAGER',25),
  ('E40005','THAMAN','GENERAL MANAGER',26);

Verifying the inserted data :

Viewing the table employee_details after inserting rows by using the following SQL query:

SELECT* FROM employee_details;
SELECT* FROM employee_resigned;

QUERIES:

• Query to find out the employee id and names of those who were not resigned using NOT EXISTS.

SELECT emp_id,emp_name  
FROM employee_details
WHERE NOT EXISTS
(SELECT *  
   FROM  employee_resigned
   WHERE employee_details.emp_id = employee_resigned.emp_id);

• Query to find out the employee details of those who were not resigned using NOT IN.

SELECT *
FROM employee_details
WHERE emp_id NOT IN
  (SELECT emp_id  
  FROM employee_resigned)