c155b14c2d78186871bc89d3dbf2d4ec.jpeg

# Chapter 7: Subqueries ## 7.1 Introduction * A subquery is a `SELECT` statement inside another SQL statement. * Subqueries can be nested inside `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statements, or inside another subquery. * Subqueries are usually added within the `WHERE` clause of an SQ...

# Chapter 7: Subqueries ## 7.1 Introduction * A subquery is a `SELECT` statement inside another SQL statement. * Subqueries can be nested inside `SELECT`, `INSERT`, `UPDATE`, or `DELETE` statements, or inside another subquery. * Subqueries are usually added within the `WHERE` clause of an SQL statement. ```sql SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT column_name FROM table_name WHERE condition); ``` ## 7.2 Types of Subqueries ### 7.2.1 Based on the number of rows returned 1. **Scalar subquery**: Returns zero or one row. 2. **Column subquery**: Returns one column and multiple rows. 3. **Row subquery**: Returns multiple columns and one row. 4. **Table subquery**: Returns multiple columns and multiple rows. ### 7.2.2 Based on the way of execution 1. **Non-correlated subquery**: The subquery can be executed independently of the outer query. 2. **Correlated subquery**: The subquery depends on the outer query for its values. ## 7.3 Scalar Subquery * A scalar subquery returns a single value (i.e., one column and one row). * It can be used anywhere a single value is expected. ### Example Find the products whose price is higher than the average price. ```sql SELECT product_name FROM products WHERE price > (SELECT AVG(price) FROM products); ``` ## 7.4 Column Subquery * A column subquery returns a single column and multiple rows. * It is often used with operators like `IN`, `NOT IN`, `ANY`, `ALL`. ### Example Find the customers who have placed orders. ```sql SELECT customer_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders); ``` ## 7.5 Row Subquery * A row subquery returns a single row and multiple columns. * It is used to compare multiple columns at once. ### Example Find the employee with the highest salary and department ID. ```sql SELECT employee_name FROM employees WHERE (salary, department_id) = (SELECT MAX(salary), department_id FROM employees); ``` ## 7.6 Table Subquery * A table subquery returns multiple columns and multiple rows. * It is used as a virtual table within the outer query. ### Example Find the average salary for each department. ```sql SELECT department_id, AVG(salary) AS avg_salary FROM (SELECT department_id, salary FROM employees) AS dept_salaries GROUP BY department_id; ``` ## 7.7 Non-Correlated Subquery * A non-correlated subquery (also known as an independent subquery) can be executed on its own. * The outer query uses the result of the subquery. ### Example Find products that belong to the same category as 'Electronics'. ```sql SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics'); ``` ## 7.8 Correlated Subquery * A correlated subquery depends on the outer query for its values. * The subquery is executed once for each row in the outer query. ### Example Find customers who have placed more orders than the average number of orders. ```sql SELECT customer_name FROM customers c WHERE (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) > (SELECT AVG(order_count) FROM (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) AS order_counts); ``` ## 7.9 `EXISTS` and `NOT EXISTS` * `EXISTS` and `NOT EXISTS` are used to test for the existence of rows in a subquery. * They return `TRUE` if any row is found, and `FALSE` otherwise. ### Example with `EXISTS` Find customers who have placed orders. ```sql SELECT customer_name FROM customers c WHERE EXISTS (SELECT 1 FROM orders WHERE customer_id = c.customer_id); ``` ### Example with `NOT EXISTS` Find customers who have not placed any orders. ```sql SELECT customer_name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders WHERE customer_id = c.customer_id); ``` ## 7.10 Subqueries in `INSERT`, `UPDATE`, and `DELETE` Statements ### 7.10.1 `INSERT` with Subquery * Insert data into a table using results from a subquery. ```sql INSERT INTO table_name (column1, column2,...) SELECT column1, column2,... FROM another_table WHERE condition; ``` **Example**: Insert customers from a temporary table into the main customers table. ```sql INSERT INTO customers (customer_name, email) SELECT customer_name, email FROM temp_customers WHERE city = 'New York'; ``` ### 7.10.2 `UPDATE` with Subquery * Update data in a table based on the result of a subquery. ```sql UPDATE table_name SET column1 = (SELECT value1 FROM another_table WHERE condition) WHERE condition; ``` **Example**: Update the price of products based on the average price in their category. ```sql UPDATE products SET price = (SELECT AVG(price) FROM products p WHERE p.category_id = products.category_id) WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics'); ``` ### 7.10.3 `DELETE` with Subquery * Delete data from a table based on the result of a subquery. ```sql DELETE FROM table_name WHERE column_name IN (SELECT column_name FROM another_table WHERE condition); ``` **Example**: Delete customers who have not placed any orders. ```sql DELETE FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders); ``` ## 7.11 Common Table Expressions (CTEs) * A CTE is a temporary named result set that you can reference within a single SQL statement. * CTEs are defined using the `WITH` clause. * They make complex queries more readable and maintainable. ```sql WITH CTE_name AS ( SELECT column1, column2 FROM table_name WHERE condition ) SELECT column1, column2 FROM CTE_name WHERE condition; ``` ### Example Find the average salary for each department using a CTE. ```sql WITH DeptAvgSalaries AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) SELECT d.department_name, das.avg_salary FROM departments d JOIN DeptAvgSalaries das ON d.department_id = das.department_id; ``` ## 7.12 Guidelines for Using Subqueries * **Readability**: Use subqueries judiciously to avoid overly complex queries. * **Performance**: Be aware of the performance implications, especially with correlated subqueries. * **Alternatives**: Consider using joins or CTEs as alternatives for better performance and readability. * **Testing**: Always test subqueries thoroughly to ensure they return the expected results. * **Naming**: Use meaningful aliases for tables and subqueries to improve readability.