10 Example of SubQuery in SQL

Hey there! Today, we're diving headfirst into the fascinating world of SQL subqueries. As someone who's spent countless hours exploring databases, almost 24 years now, I'm excited to show you how these versatile subqueries can supercharge your SQL game. Along with Joins, subquery is one of the must know concept for SQL developers. It's also slightly easier to understand then join, especially non-correlated subquery. Correlated subquery is  slightly harder to understand but don't worry these 10 example will ensure you know subquery well. 

10 Example of Subquery in SQL

Today, we're embarking on a thrilling journey deep into the heart of SQL subqueries. As a passionate data explorer, I've always been fascinated by the sheer versatility and power that subqueries bring to the table.

In this adventure, we're not just scratching the surface; we're diving headfirst into ten real-world scenarios where subqueries shine. These are the SQL gems that will elevate your data wrangling skills, making you the maestro of complex database queries.

So, fasten your seatbelts! It's time to unravel the mysteries of SQL subqueries through hands-on examples and discover how they can be your secret weapon for solving intricate data puzzles.

Example 1: Finding Max Salaries

This is a simple problem where you need to find the max salary for each department:

SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > (SELECT AVG(salary) FROM employees);

Here, I'm using a subquery within the HAVING clause to filter departments where the highest salary exceeds the average salary of all employees. Subqueries let us perform advanced aggregations.

Example 2: Top N Rows per Group

SELECT order_id, product_id, quantity
FROM order_details od
WHERE quantity >= ALL (SELECT MAX(quantity) FROM order_details WHERE order_id = od.order_id);

In this subquery gem, I'm fetching the rows with the highest quantity per order. The subquery within ALL ensures we pick the top rows within each group.

Example 3: Identifying Non-Matching Records

SELECT customer_name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Here, I'm using a subquery to find customers who haven't placed any orders. Subqueries are fantastic for identifying missing or non-matching records.

Example 4: Calculating Running Totals

SELECT order_id, order_date, total_amount,
       (SELECT SUM(total_amount)
        FROM orders o2
        WHERE o2.order_date <= o1.order_date) AS running_total
FROM orders o1;

Ever wondered how to calculate running totals? This subquery within the SELECT statement performs the magic, summing up total amounts for previous orders based on order date.

Example 5: Conditional Aggregations

SELECT department,
       (SELECT COUNT(*) FROM employees WHERE department

 = e.department AND salary > 50000) AS high_earners
FROM employees e
GROUP BY department;

In this subquery, I'm counting employees in each department who earn more than $50,000. Subqueries allow us to introduce conditional logic into our aggregations.

Example 6: Fetching Records from the Same Table

SELECT product_name, price
FROM products p1
WHERE price > (SELECT AVG(price) FROM products p2 WHERE p1.category = p2.category);

This query fetches products with prices higher than the average price within their respective categories. Subqueries make it easy to reference the same table within the query.

Example 7: Recursive Subqueries

WITH RECURSIVE manager_chain AS (
    SELECT employee_id, manager_id, employee_name
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name
    FROM employees e
    INNER JOIN manager_chain m ON e.manager_id = m.employee_id
)
SELECT * FROM manager_chain;

Recursive subqueries are a unique beast. Here, I'm using one to find the entire managerial hierarchy, starting from employees without managers and progressively climbing the chain.

Example 8: Finding Nth Highest Value

SELECT DISTINCT salary
FROM employees e1
WHERE 2 = (SELECT COUNT(DISTINCT salary) 
           FROM employees e2
           WHERE e2.salary >= e1.salary);

Ever needed to find the Nth highest value like the Nth highest salary? This subquery identifies the second-highest salary in the "employees" table.

Example 9: Correlated Subqueries

SELECT order_id, product_name
FROM order_details od
WHERE price > (SELECT AVG(price) FROM order_details WHERE order_id = od.order_id);

Correlated subqueries are linked to the outer query. Here, I'm using a correlated subquery to fetch order details with prices higher than the average price within the same order.

Example 10: Data Validation

INSERT INTO audit_log (user_id, action, timestamp)
SELECT user_id, 'DELETE', NOW()
FROM deleted_users
WHERE user_id NOT IN (SELECT user_id FROM active_users);

Subqueries aren't limited to SELECT statements. In this example, I'm using a subquery within an INSERT statement to log deleted users who were not in the list of active users. It's a powerful tool for data validation and auditing.

Conclusion: Mastering Subqueries

SQL subqueries are like Swiss Army knives in your database toolkit. They allow you to perform complex operations, create conditional logic, and even venture into recursion. With these 10 real-life examples, you're well on your way to mastering the art of subqueries in SQL.

So, the next time you're faced with a tricky SQL problem, remember that subqueries are your trusty companions, ready to tackle data challenges with finesse. 

Other Related Articles

Difference between CHAR and VARCHAR in SQL

10 Ways to use GROUP BY command in SQL With Examples

10 Ways to use GROUP BY command in SQL With Examples

How to remove duplicate values from a table? Example

Frequently Asked Questions

Q1: What exactly is a SQL subquery, and how does it differ from a regular query?

A SQL subquery is a query nested within another SQL query. It's like a queryception! While a regular query retrieves data directly from a table, a subquery is used to fetch data based on the results of another query.

Q2: What are some common use cases for SQL subqueries?

SQL subqueries are incredibly versatile. You can use them for filtering data, performing conditional aggregations, checking for existence, calculating rankings, and much more. The examples in the article showcase various use cases.

Q3: Are subqueries and JOINs interchangeable? When should I use one over the other?

Subqueries and JOINs serve different purposes. Use JOINs when you need to combine data from multiple tables based on shared columns. Use subqueries when you need to perform conditional filtering or calculations based on the results of another query.

Q4: Do subqueries impact query performance?

Yes, subqueries can affect performance, especially if not optimized. Each subquery typically involves an additional query execution, so excessive use of subqueries can lead to slower performance. It's essential to use subqueries judiciously and consider indexing.

Q5: Can subqueries be nested within other subqueries?

Absolutely! Subqueries can be nested within other subqueries to create complex queries. However, it's crucial to maintain query readability and avoid excessive nesting, which can make queries challenging to understand.

Q6: Are there any limitations or considerations when using subqueries in SQL?

Subqueries can be powerful, but they may not always be supported in certain contexts or databases. Additionally, extensive use of subqueries can lead to less readable code, so striking a balance between readability and complexity is important.

Q7: How can I become proficient in using subqueries in SQL?

Practice, practice, practice! Experiment with subqueries in real-world scenarios, study SQL tutorials and documentation, and participate in SQL communities and forums. Hands-on experience is the best way to become proficient.

Quiz Time

What is a subquery in SQL?

A) A query that retrieves data from multiple tables.

B) A query embedded within another query.

C) A query that joins two or more tables.

D) A query that aggregates data.

Which SQL operator is commonly used to create subqueries?

A) JOIN

B) UNION

C) FROM

D) IN

What is the purpose of a subquery?

A) To perform arithmetic calculations.

B) To combine data from two or more tables.

C) To retrieve data based on the result of another query.

D) To sort data in descending order.

You can answer in comments, ideally you should get all three correct but if you not sure, feel free to ask in comments.