What's the difference between 'not in' , 'not exists' and outer join in SQL | Performance comparison
#rctechlife
---not in return the first table data that are not found in the second table
When using “NOT IN”, the query performs nested full table scans.
--not exists will not return any data; it returns TRUE or FALSE values depend on the subquery values existence check.
#Not Exists Operator
Not Exists is recommended is such cases.
Whereas for “NOT EXISTS”, query can use an index within the sub-query.
--###left outer join return all records from the first left table & matched with second table & null values from right table
select * from tblAuthors
select * from tblBooks
select * from tblBooks books
where Author_id not in (select id from tblAuthors)
select * from tblBooks books
where not exists(select auth.Id from tblAuthors auth where books.Author_id=auth.id)
select * from tblBooks book
left outer join tblAuthors auth on book.Author_id=auth.Id
where book.Author_id is null
#trending sql
#rctechlife
