When working on ‘Exists and Not Exists’ Lesson under Subqueries, we learn that using With IN will give the same results as With Exists (the latter being more efficient).
But on trying to use With = instead of With IN, the results were significantly different. I was curious to understand why?
Do you have a link to the lesson?
“=” is used with
IN can be used with
WHERE as well to see if an expression matches anything in a list of values. I think it’s easier to use “IN” rather than multiple
EXISTS , (assuming the subquery is not empty) will return True, otherwise, False (no rows are returned).
WHERE name = 'Peter' OR name = 'Phil' OR name= 'Steve' OR name='Tony' OR name='Mike';
WHERE name IN ('Peter', 'Phil', 'Steve', 'Tony', 'Mike');
WHERE EXISTS (SELECT * FROM students WHERE student_id = course_id);
Thanks for your reply lisalisaj.
IN as I now understand is essentially ‘=’ with multiple OR criteria.
When applied to the lesson on this link, SQL was giving a count of 50 results with IN, but only 19 when using ‘=’ with WHERE clause. Thanks once again!