Difference between using With IN AND WITH = when using subqueries?

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 WHERE and 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 AND or OR. EXISTS , (assuming the subquery is not empty) will return True, otherwise, False (no rows are returned).
ex:

SELECT *
FROM musicians
WHERE name = 'Peter' OR name = 'Phil' OR name= 'Steve' OR name='Tony' OR name='Mike';

vs.

SELECT *
FROM musicians
WHERE name IN ('Peter', 'Phil', 'Steve', 'Tony', 'Mike');

and,

SELECT *
FROM students
WHERE EXISTS (SELECT * FROM students WHERE student_id = course_id);
2 Likes

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!

1 Like