How to Find Records From One Table Which Don’t Exist in Another MySQL

MySQL is a free and open-source relational database management system written in C and C++ that is extremely popular among developers. Like other relational database management systems, MySQL provides a variety of rich features to create databases and tables, insert data in them, and further manipulate them as the system evolves.

In this article, we will be looking at how we can find records from one table that don’t exist in another table in MySQL. Understanding this will allow developers to easily manipulate data in their existing tables.

Efficiently Discovering Missing Records in MySQL Tables

Let us start by creating some sample tables and inserting data into them. We will create two tables, EMPLOYEE and MANAGER tables whereby the EMPLOYEE table will have more records than the MANAGER table.

CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

INSERT INTO EMPLOYEE VALUES (1, 'Clark', 'clark@some.com');
INSERT INTO EMPLOYEE VALUES (2, 'Dave', 'dave@some.com');
INSERT INTO EMPLOYEE VALUES (3, 'Ava', 'ava@some.com');


CREATE TABLE MANAGER (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

INSERT INTO MANAGER VALUES (2, 'Dave', 'dave@some.com');
INSERT INTO MANAGER VALUES (3, 'Ava', 'ava@some.com');

The following is the initial data in both the EMPLOYEE and MANAGER tables:

EMPLOYEE table data

MANAGER table data:

MANAGER table data

Now that we have the setup in place, let now go forward to see how we can find records from one table which don’t exist in the other table. We are now going to have a look at multiple methods to find the records which exist in only one table and not the other.

Method 1: Using Subquery

A subquery is a query that uses the result of another query to get the final result. There are multiple ways in which we can use subquery to get the desired result. I will show you two of those.

In the following query, we first select the employee ids that are present in the MANAGER table and then use NOT IN clause to exclude them in the outer query.

SELECT * FROM EMPLOYEE
WHERE empId NOT IN 
(
  SELECT empId FROM MANAGER
);

Output:

Using subquery output

Now let’s take a look at one other form of using subquery. In the following query, we first select only those records in the inner query that are present in both the tables by comparing the empId in the WHERE clause. Later we discard these records from the EMPLOYEE table by utilising the NOT EXISTS clause.

SELECT * FROM EMPLOYEE
WHERE NOT EXISTS 
(
  SELECT * FROM MANAGER
  WHERE EMPLOYEE.empId=MANAGER.empId
);

Output:

Using subquery output

Explanation: The output consists of employee records where the empId is not present in the MANAGER table, based on the provided SQL query.

Method 2: Using LEFT OUTER JOIN

The left join returns all the records in the left table whether they are matched or not. Using this, we can filter out the records for which the fields from the right table are NULL.

The following query employs the same to filter out the records for which MANAGER.empId is NULL.

SELECT EMPLOYEE.* FROM EMPLOYEE
LEFT OUTER JOIN MANAGER
ON EMPLOYEE.empId=MANAGER.empId
WHERE MANAGER.empId IS NULL;

Output:

Using LEFT OUTER JOIN output

Explanation: The output consists of employee records from the EMPLOYEE table where the empId does not match any empId in the MANAGER table, using a LEFT OUTER JOIN and filtering for NULL values in the MANAGER table.

Example to Identifying Non-Matching Records Between Two MySQL Tables

Let’s now use the concepts we have learned in this article in a technical example. First, let’s create the table and insert some data inside it. The following query creates two tables, named PHYSICS and COMPUTER_SCIENCE,let’s and inserts records in it.

-- create
CREATE TABLE PHYSICS (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  exp INTEGER NOT NULL
);


CREATE TABLE COMPUTER_SCIENCE (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  exp INTEGER NOT NULL
);

-- insert
INSERT INTO PHYSICS VALUES (0001, 'Clark', 3);
INSERT INTO PHYSICS VALUES (0090, 'Dave', 1);
INSERT INTO PHYSICS VALUES (0503, 'Steve', 2);
INSERT INTO PHYSICS VALUES (0009, 'Aria', 5);

-- insert
INSERT INTO COMPUTER_SCIENCE VALUES (0011, 'Alex', 0);
INSERT INTO COMPUTER_SCIENCE VALUES (0090, 'Dave', 1);
INSERT INTO COMPUTER_SCIENCE VALUES (0900, 'Lucy', 3);
INSERT INTO COMPUTER_SCIENCE VALUES (1900, 'Simon', 3);
INSERT INTO COMPUTER_SCIENCE VALUES (0009, 'Aria', 5);

The following is the initial data in each of the tables:

PHYSICS Data:

PHYSICS data

COMPUTER_SCIENCE Data:

COMPUTER_SCIENCE data

Now let’s try to find all the professors that are in both the departments i.e. they are in the physics department as well as computer science department. We will make use of the subquery approach to solve this. In the inner query, we will select all the professors which are in the computer science department and later use the IN operator in the outer query to filter the data from the physics department table. The following is the query:

SELECT * FROM PHYSICS
WHERE id IN (
  SELECT id FROM COMPUTER_SCIENCE
);

Output:

Final data

Explanation: The output consists of records from the PHYSICS table where the id is found in the COMPUTER_SCIENCE table based on the provided SQL query.

Conclusion

In this article, we covered how we can find records from one table which are not present in the other table in MySQL. We had a chance to look at two different methods to go about doing this, first using subqueries and the other using LEFT OUTER JOIN. We also how we can use the concepts we learned in this article to a real-life situation through the technical example.