Interview Questions and Answers
Difference between IN and IF EXISTS?
IN vs. EXISTS Comparison Chart
The following comparison chart explains their main differences in a quick manner:
SN | IN Operator | EXISTS Operator |
---|---|---|
1. | It is used to minimize the multiple OR conditions. | It is used to check the existence of data in a subquery. In other words, it determines whether the value will be returned or not. |
2. | It compares the values between subquery (child query) and parent query. | It does not compare the values between subquery and parent query. |
3. | It scans all values inside the IN block. | It stops for further execution once the single positive condition is met. |
4. | It can return TRUE, FALSE, or NULL. Hence, we can use it to compare NULL values. | It returns either TRUE or FALSE. Hence, we cannot use it to compare NULL values. |
5. | We can use it on subqueries as well as with values. | We can use it only on subqueries. |
6. | It executes faster when the subquery result is less. | It executes faster when the subquery result is large. It is more efficient than IN because it processes Boolean values rather than values itself. |
7. | Syntax to use IN clause: SELECT col_names FROM tab_name WHERE col_name IN (subquery); | Syntax to use EXISTS clause: SELECT col_names FROM tab_name WHERE [NOT] EXISTS (subquery); |
Post a Comment
0 Comments