SQL - Sub Queries

Hello, aspiring SQL enthusiasts! I'm thrilled to be your guide on this exciting journey into the world of SQL subqueries. As someone who's been teaching computer science for many years, I can tell you that subqueries are like the secret sauce that can take your SQL skills from good to great. So, let's dive in and unravel the mysteries of subqueries together!

SQL Subqueries

What are Subqueries?

Imagine you're making a sandwich. The bread is your main query, but what makes it truly delicious are the fillings inside - that's your subquery! In SQL terms, a subquery is a query nested inside another query. It's like asking a question within a question, allowing you to perform complex operations that might not be possible with a single query.

Let's look at a simple example:

SELECT employee_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Marketing');

In this query, the part inside the parentheses is our subquery. It's finding the department_id for the Marketing department, which is then used by the outer query to find all employees in that department.

Rules to be followed

Before we dive deeper, let's go over some important rules for using subqueries:

1. Subqueries must be enclosed in parentheses.
2. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for comparison.
3. An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY.
4. Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.

Here's a handy table summarizing these rules:

Rule	Description
Parentheses	Subqueries must be enclosed in parentheses
Single Column	Subquery SELECT usually returns only one column
No ORDER BY	ORDER BY can't be used in a subquery
Multiple Rows	Use multiple value operators for subqueries returning multiple rows

Subqueries with the SELECT Statement

Subqueries are most commonly used with the SELECT statement. They can be incredibly powerful for retrieving data based on dynamic conditions. Let's look at a more complex example:

SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

This query selects all products with a price higher than the average price of all products. The subquery calculates the average price, and the main query uses this result to filter the products.

Here's another example that uses a subquery in the SELECT clause:

SELECT 
    employee_name,
    salary,
    (SELECT AVG(salary) FROM employees) as avg_salary
FROM employees;

This query not only retrieves each employee's name and salary but also includes the average salary across all employees in each row. It's like having a mini-report for each employee!

Subqueries with the INSERT Statement

Subqueries can also be used with INSERT statements to populate a table with data from another table. Here's an example:

INSERT INTO high_salary_employees (employee_id, employee_name, salary)
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) * 1.5 FROM employees);

This query inserts into the high_salary_employees table all employees whose salary is more than 1.5 times the average salary. It's like creating a VIP list of your top earners!

Subqueries with the UPDATE Statement

Subqueries can make your UPDATE statements much more powerful. Let's look at an example:

UPDATE products
SET price = price * 1.1
WHERE category_id = (SELECT category_id FROM categories WHERE category_name = 'Electronics');

This query increases the price of all products in the 'Electronics' category by 10%. The subquery finds the category_id for 'Electronics', which is then used by the main query to update the correct products.

Subqueries with the DELETE Statement

Finally, let's see how subqueries can be used with DELETE statements:

DELETE FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE last_order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
);

This query deletes all orders from customers who haven't placed an order in the last year. The subquery finds all such customers, and the main query deletes their orders.

And there you have it, folks! We've journeyed through the land of SQL subqueries, from the basic concepts to more advanced applications. Remember, practice makes perfect, so don't be afraid to experiment with these queries on your own databases.

As I always tell my students, SQL is like a Swiss Army knife - and subqueries are that hidden blade that can get you out of tricky situations. So keep exploring, keep querying, and most importantly, keep learning!

Credits: Image by storyset