SQL(subquery)

Subqueries are a powerful feature in SQL that allow you to use a query result as part of another query. Depending on how and where you use subqueries, they can be categorized into several types. Here are the main types:

1. Single-Row Subquery Returns only one row of results. Commonly used with comparison operators like =, <, >, <=, >=. Example: sql Copy code SELECT name FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 10);
2. Multi-Row Subquery Returns multiple rows as results. Used with operators like IN, ANY, ALL. Example: sql Copy code SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');
3. Correlated Subquery Refers to columns in the outer query and executes once for every row processed by the outer query. Commonly used in filtering and calculations. Example: sql Copy code SELECT name FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);
4. Nested Subquery A subquery inside another subquery. Example: sql Copy code SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE location = 'London'));
5. Scalar Subquery Returns a single value (a single row and column). Can be used in SELECT, WHERE, or FROM clauses. Example: sql Copy code SELECT name, (SELECT department_name FROM departments WHERE department_id = e.department_id) AS department_name FROM employees e;
6. Derived Table Subquery A subquery used in the FROM clause, essentially treated as a temporary table. Example: sql Copy code SELECT dept_name, AVG(salary) FROM (SELECT department_id, salary FROM employees WHERE salary > 5000) AS high_salaries JOIN departments ON high_salaries.department_id = departments.department_id GROUP BY dept_name;
7. Exists/Not Exists Subquery Checks for the existence or non-existence of rows. Commonly used in conditional logic. Example: sql Copy code SELECT name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.location = 'London');
8. Inline View Subquery Acts like a view created on-the-fly, usually used in the FROM clause. Example: sql Copy code SELECT avg_salary FROM (SELECT AVG(salary) AS avg_salary FROM employees WHERE department_id = 10); Summary of Key Uses Filters: WHERE, HAVING, EXISTS. Calculations: Nested or scalar subqueries for calculated columns. Joins: Derived tables or inline views for complex joins. Dynamic Conditions: IN, ANY, ALL, and correlated subqueries.