Understanding SQL Subqueries: A complete guide with examples

Detailed explanation and examples of SQL subquery

What is SQL subquery?

A subquery, also known as an internal query or a nested query, refers to a query embedded in another SQL query. It is enclosed in brackets, executed before the external query, and provides results for the external query for further processing.

Subquery type

1. Single-line subquery: Returns single-line results, usually used in combination with comparison operators such as = and .

Example: Find the name of the highest paid employee:

 <code class="sql">SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);</code>

2. Multi-line subquery: Returns multi-line results, usually used in combination with operators such as IN , ANY , and ALL .

Example: Find employees working in a department with more than 5 employees:

 <code class="sql">SELECT name FROM employees WHERE departmentid IN (SELECT departmentid FROM departments WHERE members > 5);</code>

3. Related subquery: Reference columns in external query and execute repeatedly for each row of external query.

Example: Find employees who are paid more than their average salary in their department:

 <code class="sql">SELECT name, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE departmentid = e.departmentid);</code>

4. Nested subquery: A subquery containing another subquery.

Example: Find departments with an average salary of more than $50,000:

 <code class="sql">SELECT departmentname FROM departments WHERE departmentid IN ( SELECT departmentid FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) );</code>

Subquery application scenarios

1. Data filtering: Filter data rows according to specific conditions.

 <code class="sql">SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);</code>

2. Data comparison: Compare data in different tables.

 <code class="sql">SELECT name FROM employees WHERE departmentid = (SELECT id FROM departments WHERE name = 'HR');</code>

3. Data aggregation: Perform advanced aggregation operations.

 <code class="sql">SELECT departmentid, (SELECT COUNT(*) FROM employees WHERE departmentid = d.id) AS employeecount FROM departments d;</code>

4. Dynamic update: Dynamically update or delete data rows based on subqueries.

 <code class="sql">UPDATE Employees SET Bonus = 500 WHERE Salary > (SELECT AVG(Salary) FROM Employees);</code>

Subquery advantages

1. Modularity: Decompose complex queries into smaller, more manageable units.
2. Readability: Improve the clarity of query logic.
3. Flexibility: Can be used in SELECT , WHERE , FROM and HAVING clauses.

Subquery limitations

1. Performance: Subqueries, especially related subqueries, may result in performance degradation after multiple executions.
2. Complexity: Deeply nested subqueries can be difficult to read and debug.
3. Optimization Challenge: Sometimes, using connections can achieve higher efficiency.

Summarize

Subqueries are powerful tools in SQL for decomposing complex logic into manageable units to enable dynamic data analysis and data operations. However, alternatives such as subqueries and connections need to be weighed for optimal query performance.

The above is the detailed content of Understanding SQL Subqueries: A complete guide with examples. For more information, please follow other related articles on the PHP Chinese website!