SQL Subqueries: Queries within Queries
Shah Jahan
Data Analyst & Power BI Specialist | Expertise in Python, SQL, Excel, Google Sheets, Pandas, NumPy | Data Visualization Dashboards
What are SQL Subqueries
An SQL subquery is a query nested within another query, enabling you to execute complex operations by breaking them down into smaller, more manageable components. These subqueries can appear in various parts of a SQL statement, such as the SELECT, FROM, WHERE, or HAVING clauses, depending on the specific use case.
Source: deepdecide.com
Types of SQL Subqueries
Let's explore the different types of subqueries and delve into each with examples:
1. Scalar Subquery
A scalar subquery returns a single value and can be used in places where an expression is expected to produce a single value.
Examples
SELECT column1, (SELECT MAX(column2) FROM table2) AS max_value
FROM table1;
In this query, the subquery (SELECT MAX(column2) FROM table2) calculates the maximum value of column2 from table2 and returns it as max_value.
Example 1: Retrieving Customer Information with Total Orders
SELECT customer_id,
customer_name,
(SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id) AS total_orders
FROM customers;
Example 2: Finding the Highest Salary for Each Employee
SELECT employee_id,
employee_name,
(SELECT MAX(salary) FROM salaries WHERE salaries.employee_id = employees.employee_id) AS highest_salary
FROM employees;
Example 3: Calculating the Age of Employees
SELECT employee_id,
employee_name,
(SELECT YEAR(NOW()) - YEAR(date_of_birth)) AS age
FROM employees;
Example 4: Determining the Total Price of All Orders
SELECT (SELECT SUM(price) FROM order_items) AS total_price;
Example 5: Counting Employees in Each Department
SELECT department_id,
department_name,
(SELECT COUNT(*) FROM employees WHERE employees.department_id = departments.department_id) AS num_employees
FROM departments;
2. Row Subquery
A row subquery returns a single row of data and can be used in places where a single row is expected.
Example
SELECT column1, column2
FROM table1
WHERE (column1, column2) = (SELECT column1, column2 FROM table2);
Here, the subquery (SELECT column1, column2 FROM table2) returns a single row, which is then compared with each row of table1 to filter the results.
Example 1: Finding Customers with Specific Orders
SELECT customer_id,
customer_name
FROM customers
WHERE (customer_id, customer_name) IN
(SELECT customer_id, customer_name FROM orders WHERE total_amount > 1000);
Example 2: Selecting Employees with Highest Salaries
SELECT employee_id,
employee_name
FROM employees
WHERE (employee_id, employee_name) =
(SELECT employee_id, employee_name FROM salaries ORDER BY salary DESC LIMIT 1);
Example 3: Retrieving Products with the Most Sales
SELECT product_id,
product_name
FROM products
WHERE (product_id, product_name) =
(SELECT product_id, product_name FROM sales ORDER BY quantity_sold DESC LIMIT 1);
Example 4: Identifying Employees with Birthdays Today
SELECT employee_id,
employee_name
FROM employees
WHERE (EXTRACT(MONTH FROM date_of_birth), EXTRACT(DAY FROM date_of_birth)) =
(SELECT EXTRACT(MONTH FROM CURRENT_DATE), EXTRACT(DAY FROM CURRENT_DATE));
Example 5: Filtering Orders Placed in the Last Month
SELECT order_id,
order_date
FROM orders
WHERE (EXTRACT(MONTH FROM order_date), EXTRACT(YEAR FROM order_date)) =
(SELECT EXTRACT(MONTH FROM DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)),
EXTRACT(YEAR FROM DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)));
领英推荐
3. Table Subquery
A table subquery returns a result set of rows and columns, just like a regular table, and can be used in places where a table or view is expected.
Example
SELECT column1
FROM (SELECT column1, column2 FROM table2) AS subquery_table
WHERE column2 = 'value';
The subquery (SELECT column1, column2 FROM table2) generates a temporary table subquery_table, which is then used in the outer query to filter rows based on the condition column2 = 'value'.
Example 1: Filtering Products with Sales Greater Than Average
SELECT product_id,
product_name
FROM products
WHERE product_id IN
(SELECT product_id FROM sales WHERE total_sales > (SELECT AVG(total_sales) FROM sales));
Example 2: Retrieving Employees in Departments with More Than Two Employees
SELECT employee_id,
employee_name
FROM employees
WHERE department_id IN
(SELECT department_id FROM
(SELECT department_id, COUNT(*) AS num_employees FROM employees GROUP BY department_id) AS dept_count
WHERE num_employees > 2);
Example 3: Finding Orders Placed by High-Value Customers
SELECT order_id,
order_date
FROM orders
WHERE customer_id IN
(SELECT customer_id FROM
(SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id) AS customer_spend
WHERE total_spent > 1000);
Example 4: Identifying Employees with No Assigned Tasks
SELECT employee_id,
employee_name
FROM employees
WHERE employee_id NOT IN (SELECT DISTINCT employee_id FROM tasks);
Example 5: Selecting Products with No Sales
SELECT product_id,
product_name
FROM products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM sales);
4. Correlated Subquery
A correlated subquery depends on the outer query for its values and executes once for each row processed by the outer query.
Example
SELECT column1
FROM table1 t1
WHERE column2 = (SELECT MAX(column2) FROM table1 WHERE t1.column3 = table1.column3);
In this correlated subquery, the inner query (SELECT MAX(column2) FROM table1 WHERE t1.column3 = table1.column3) is executed for each row of table1 in the outer query.
Example 1: Finding Employees with Salaries Higher Than Average
SELECT employee_id,
employee_name
FROM employees e_outer
WHERE salary >
(SELECT AVG(salary) FROM employees e_inner WHERE e_outer.department_id = e_inner.department_id);
Example 2: Selecting Orders Placed by High-Value Customers
SELECT order_id,
order_date
FROM orders o_outer
WHERE total_amount >
(SELECT AVG(total_amount) FROM orders o_inner WHERE o_outer.customer_id = o_inner.customer_id);
Example 3: Identifying Customers with More Orders Than Average
SELECT customer_id,
customer_name
FROM customers c_outer
WHERE (SELECT COUNT(*) FROM orders WHERE customer_id = c_outer.customer_id) >
(SELECT AVG(num_orders) FROM (SELECT COUNT(*) AS num_orders FROM orders GROUP BY customer_id) o_inner);
Example 4: Selecting Employees with Higher Tenure Than Average
SELECT employee_id,
employee_name
FROM employees e_outer
WHERE (SELECT DATEDIFF(CURDATE(), hire_date)) >
(SELECT AVG(DATEDIFF(CURDATE(), hire_date)) FROM employees e_inner WHERE e_outer.department_id = e_inner.department_id);
Example 5: Retrieving Orders with Total Amounts Higher Than the Average of the Previous Month
SELECT order_id,
order_date
FROM orders o_outer
WHERE total_amount >
(SELECT AVG(total_amount) FROM orders o_inner WHERE MONTH(o_outer.order_date) = MONTH(o_inner.order_date) - 1);
Best Practices for Using SQL Subqueries
When working with subqueries, it's essential to follow best practices to ensure efficiency and readability:
Conclusion
SQL subqueries serve as powerful tools for breaking down complex operations into manageable components, allowing for more flexible and expressive queries. By understanding the various types of subqueries and their practical applications, you can leverage their capabilities to extract valuable insights and manipulate data with ease.
#SqlNestedQueries #SqlNestedQueriesExamples #SqlSubqueryExample #SqlSubqueryInSelect #NestedQueriesInSqlExamplesPdf #SqlNestedQueriesOracle #TypesOfSubqueriesInSql #CorrelatedSubqueryInSql #QueryWithinAQuery #WriteSqlQueryInsideSqlQuery #QueryingAQueryInSql #NestedQueryInSql #SqlQueriesWithinQueriesW3schools #SqlQueriesWithinQueriesExamples #SqlSubquery #SubqueryInSqlW3schools #SqlSubqueryInSelect #SqlQueriesWithinQueriesOracle #MultipleSubqueriesInSqlWithExamples #TypesOfSubqueriesInSql #Join3SubqueriesInSQL #BestPracticeForSubqueriesInSQL #SubqueriesFasterThanCTE #WhySubqueryIsFasterThanJoin #SqlSubqueryExamples #SqlSubqueriesOracle #SubqueryInMysql #ComponentsOfASubquery #SqlMultipleSubqueriesInSelectStatement