EXISTS: is used to return a Boolean value
JOIN: returns a whole other table
EXISTSis only used to test if a sub query returns results, and short circuits as soon as it does.
JOINis used to extend a result set by combining it with additional fields from another table to which there is a relation. SO
EXISTSis fast and
JOINis slow comparatively. Let see it with an example
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT * FROM BOOKS WHERE EXISTS
SELECT NewSeqCol FROM MYTABLE
SELECT * FROM BOOKS
JOIN MYTABLE ON BOOKS.id = MYTABLE.NewSeqCol
above are the result sets returned from both of the queries , as you can see from the elapsed time durations difference is clear.