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

Recently I’m working on a Windows Based Project in which I have to Select records from a table, which don’t exist in another table.

There are many different ways of doing this task, with varying efficiency, and the relative size of your two database tables.

Let’s take an example.
I have 2 tables Users and UserEducation in SQL DB. UserEducation has some of the records of the Users i.e educational information of uses.

Now I want is to select all the records from Users 1, which don’t exist in UserEducation i.e I want all users who don’t enter the education information for showing the popup on the website to “Complete their profile”.

find records from one table which don't exist in another SqlSQL 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 above SQL query, we select all rows from Users and for each row, we try to find a row in UserEducation with the same value for the Id column. If no row found, then we just leave the UserEducation portion of our result empty.

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)

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)

 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

If you have any queries or doubt please comment.

The post [Solved]- find records from one table which don’t exist in another Sql appeared first on Software Development | Programming Tutorials.



Read More Articles