What is a subquery and when would you use one?
Explanation:
A subquery is a query nested within another SQL query. It is used to perform operations that need intermediary data processing. Subqueries can be found in various parts of a query, such as in the SELECT, WHERE, or FROM clauses, and they are executed before the outer query. Subqueries are useful when you need to perform a secondary query to filter, calculate, or aggregate data before using it in the main query.
Key Talking Points:
- Subqueries are nested queries used within another SQL query.
- They are executed before the outer query.
- Useful for filtering, calculating, or aggregating data.
- Can be placed in SELECT, WHERE, or FROM clauses.
NOTES:
Reference Table:
| Feature | Subquery | Join |
|---|---|---|
| Execution Order | Inner query executed before the outer | Both tables processed simultaneously |
| Performance | Can be less efficient for large data | Generally more efficient |
| Use Case | When intermediary calculations needed | When combining data from tables |
| Syntax Complexity | Often simpler | Can be complex with multiple joins |
Pseudocode:
Here's a simple example of a subquery used in a WHERE clause:
SELECT employee_id, name
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'Engineering'
);
In this example, the subquery finds the department_id for the 'Engineering' department, and then the outer query uses that result to find all employees in that department.
Follow-Up Questions and Answers:
-
Question: Can a subquery return multiple columns or rows, and how is it handled?
Answer: Yes, a subquery can return multiple columns or rows. If multiple columns are returned, the subquery is typically used in a FROM clause as a derived table. If multiple rows are returned, it can be used with operators like IN, EXISTS, or with comparison operators using ANY or ALL.
-
Question: What are correlated subqueries, and how do they differ from regular subqueries?
Answer: Correlated subqueries are those that refer to columns from the outer query. They are executed once for each row processed by the outer query. This differs from regular subqueries, which are independent and executed once before the outer query starts.
-
Question: How do you optimize queries with subqueries for better performance?
Answer: To optimize subqueries, you can:
- Ensure proper indexing on columns used in subqueries.
- Use JOINs instead of subqueries when appropriate, as they are generally more efficient.
- Avoid using subqueries in SELECT clauses if they can be rewritten to use joins.
- Use EXISTS instead of IN for better performance with large datasets.
This structured and detailed explanation provides a comprehensive understanding of subqueries, suitable for a data analyst preparing for a FAANG company interview.