[Solved]- Find Records From One Table Which Don’t Exist In Another SQL

In this article, we will learn how to select all records from one table that do not exist in another table using SQL Server, or how to find records in one table that have no matching records in another table.

Recently, I've been working on a Windows-based project where I need to select records from a table that do not exist in another table.

There are various ways to accomplish this task, each with its own efficiency, depending on the size of your database tables.

Let's consider an example. I have two tables: Users and UserEducation in my SQL database. The UserEducation table contains some of the educational information of users.

Now, what I want is to select all records from the Users table which do not exist in the UserEducation table. In other words, I want to retrieve all users who have not entered their educational information, so that I can display a popup on the website prompting them to "Complete their profile".

SQL query to select record with ID not in another table

1.SQL QUERY Using LEFT JOIN

SELECT t1.Id, t1.name
FROM Users t1
LEFT JOIN UserEducation t2 ON t2.UserId = t1.Id
WHERE t2.UserId IS NULL

Generic Query

SELECT TABLE1.Id, TABLE1.Name, 
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.Id = TABLE2.Id
WHERE TABLE2.Id IS NULL

In the SQL query above, we select all rows from the Users table, and for each row, we attempt to find a corresponding row in the UserEducation table with the same value for the Id column. If no matching row is found, then the UserEducation portion of our result remains empty.

In short this query retrieves the Id and name of users from the Users table who do not have any corresponding entry in the UserEducation table. It's useful for finding users who haven't provided education information or for whom education information hasn't been recorded.

• SELECT t1.Id, t1.name: This part of the query specifies the columns to be selected from the tables involved. It selects the Id and name columns from the Users table (aliased as t1).
• FROM Users t1: This part specifies the table from which the data is being selected. In this case, it's selecting data from the Users table and aliasing it as t1.
• LEFT JOIN UserEducation t2 ON t2.UserId = t1.Id: This line indicates a LEFT JOIN operation, which combines rows from the Users table (t1) with rows from the UserEducation table (t2) based on a related column. The condition ON t2.UserId = t1.Id specifies that the UserId column in the UserEducation table should match the Id column in the Users table.
• WHERE t2.UserId IS NULL: This part filters the results of the join. It specifies that only rows where there is no corresponding entry in the UserEducation table should be selected. When a LEFT JOIN is used, if there is no matching row in the right table (UserEducation in this case), then the columns of the right table for that row will be NULL. Therefore, by checking t2.UserId IS NULL, it selects only those rows where no matching entry is found in UserEducation.

2.Using “Not In”, the shortest and quickest statement if your Table2 is very short

SELECT Id,name
FROM Users
WHERE Id NOT IN
(SELECT UserId
FROM UserEducation)

Generic Query

SELECT Id,name
FROM TABLE1
WHERE Id NOT IN
(SELECT Id
FROM TABLE2)

In simpler terms, this query retrieves the Id and name of users from the Users table who do not have any corresponding entry in the UserEducation table. It's essentially another way of achieving the same result as the previous query, using a different SQL construct (a subquery with the NOT IN operator instead of a LEFT JOIN with a condition checking for NULL values). Both queries aim to find users who haven't provided education information or for whom education information hasn't been recorded.

• SELECT Id, name: The query specifies the columns to be selected from the Users table. It selects the Id and name columns.
• FROM Users: Specifies the table from which the data is being selected. In this case, it's selecting data from the Users table.
• WHERE Id NOT IN (SELECT UserId FROM UserEducation): A subquery (also known as a nested query) that filters the results. The subquery (SELECT UserId FROM UserEducation) selects all the UserId values from the UserEducation table. The outer query then selects rows from the Users table where the Id is not found in the list of UserId values obtained from the subquery.

3.Alternate solution with NOT EXISTS:

SELECT Id,name
FROM Users
WHERE NOT EXISTS
(SELECT *
FROM UserEducation
WHERE UserEducation.UserId = Users.Id)

Generic Query

SELECT Id,name
FROM TABLE1
WHERE NOT EXISTS
(SELECT *
FROM TABLE2
WHERE TABLE2.Id = TABLE1.Id)

 sds

This query retrieves the Id and name of users from the Users table for whom there are no corresponding entries in the UserEducation table. Another way of achieving the same result as the previous queries, using a different SQL construct (a subquery with the NOT EXISTS operator instead of a LEFT JOIN or NOT IN subquery). The goal remains the same: to find users who haven't provided education information or for whom education information hasn't been recorded.

WHERE NOT EXISTS (SELECT * FROM UserEducation WHERE UserEducation.UserId = Users.Id): This line is a subquery (also known as a nested query) that filters the results. The subquery (SELECT * FROM UserEducation WHERE UserEducation.UserId = Users.Id) selects all columns from the UserEducation table where the UserId matches the Id from the outer Users table. The NOT EXISTS condition checks if there are no rows returned by this subquery for each row in the Users table.

4. Using EXCEPT

SELECT *
FROM Users
EXCEPT
SELECT a.*
FROM Users a
JOIN UserEducation b ON a.ID = b.UserId     

Generic Query    

SELECT *
FROM TABLE1
EXCEPT
SELECT a.*
FROM TABLE1 a
JOIN TABLE2 b ON a.Id = b.Id     

This query retrieves all records from the Users table that do not have corresponding entries in the UserEducation table. It effectively identifies users who have not provided education information or for whom education information hasn't been recorded. This approach differs from the previous ones in that it uses the EXCEPT operator, which directly compares entire rows between the two result sets to find the differences.

EXCEPT: This is a set operation in SQL that returns distinct rows that are present in the first query but not in the second query.

SELECT a. FROM Users a JOIN UserEducation b ON a.ID = b.UserId*: This part of the query joins the Users and UserEducation tables based on the ID column from Users and UserId column from UserEducation. It selects all columns (a.*) from the Users table (aliased as a).

If you have any queries or doubt please comment.