The EXISTS predicate - interview questions

 The EXISTS predicate in SQL is used to check if a subquery returns any rows. It's commonly used in conditional statements to test for the existence of rows in a subquery. Here are some interview questions related to the EXISTS predicate:

1. Basic Understanding:

• Question: What does the EXISTS predicate do in an SQL query?
• Answer: The EXISTS predicate checks if a subquery returns any rows. If the subquery returns one or more rows, EXISTS evaluates to TRUE; otherwise, it evaluates to FALSE.

2. Usage in Filtering:

• Question: How can you use the EXISTS predicate to filter results in a SELECT statement?
• Answer: You can use EXISTS in the WHERE clause to filter rows based on whether a related subquery returns any rows. For example:

SELECT *
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
);

3. Difference from IN:

• Question: What is the difference between EXISTS and IN? When would you prefer one over the other?
• Answer: EXISTS checks if any row is returned by the subquery and stops processing once it finds a match. IN compares a column's value to a list of values or the result of a subquery. EXISTS is generally preferred for correlated subqueries, especially when the subquery involves complex joins or large datasets, as it can be more efficient.

4. Correlated Subqueries:

• Question: What is a correlated subquery, and how is it related to the EXISTS predicate?
• Answer: A correlated subquery is a subquery that references columns from the outer query. The EXISTS predicate is often used with correlated subqueries, where the subquery is evaluated for each row processed by the outer query.

5. Performance Considerations:

• Question: How does the performance of EXISTS compare to JOIN when filtering results?
• Answer: EXISTS can be more efficient than a JOIN when you only need to check for the existence of rows, rather than combining data from multiple tables. EXISTS stops processing as soon as it finds a match, which can reduce the number of rows processed, especially with large datasets.

6. NOT EXISTS:

• Question: How does NOT EXISTS work, and how is it different from using EXISTS?
• Answer: NOT EXISTS returns TRUE if the subquery returns no rows. It is the opposite of EXISTS, which returns TRUE if the subquery returns one or more rows. NOT EXISTS is used to filter out rows where a subquery returns results.

7. Handling NULL Values:

• Question: How does the EXISTS predicate handle NULL values in the subquery?
• Answer: The EXISTS predicate is not affected by NULL values. It only checks for the existence of rows in the subquery. If the subquery returns any rows, EXISTS evaluates to TRUE, regardless of whether those rows contain NULL values.

8. Combining with Other Predicates:

• Question: Can you combine EXISTS with other predicates like AND, OR, or NOT in a WHERE clause? Provide an example.
• Answer: Yes, EXISTS can be combined with other predicates. For example:

SELECT *
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
)
AND e.salary > 50000;

9. Nested EXISTS:

• Question: Can you nest EXISTS predicates? What would be the purpose?
• Answer: Yes, you can nest EXISTS predicates to check for multiple levels of existence. This is useful when you need to verify the existence of related data at multiple levels. For example:

SELECT *
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND EXISTS (
        SELECT 1
        FROM locations l
        WHERE l.location_id = d.location_id
        AND l.city = 'New York'
    )
);

10. Comparison with ANY and ALL:

• Question: How does EXISTS differ from ANY and ALL?
• Answer: EXISTS checks for the existence of any rows in a subquery. ANY compares a value against any value returned by the subquery, and ALL compares a value against all values returned by the subquery. EXISTS is a predicate and returns a boolean, while ANY and ALL are used with comparison operators.