[SOLVED]- FIND RECORDS FROM ONE TABLE WHICH DON’T EXIST IN ANOTHER SQL
FIND RECORDS FROM ONE TABLE WHICH DON’T EXIST IN ANOTHER SQL
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.
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 NULLIn 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