SQL Subquery with WHERE: A Comprehensive Guide 🎯

Welcome back to CodeYourCraft! Today, we're diving deep into the world of SQL, focusing on Subqueries with the WHERE clause. This tutorial is designed for both beginners and intermediates, so let's get started!

What are Subqueries? 📝

Subqueries are a powerful tool in SQL that allows you to nest one SELECT statement within another. They are used to extract data from a database based on the results of another query.

Understanding the WHERE Clause 💡

The WHERE clause is a filter in SQL that helps you select specific rows from a table based on a condition. Combined with subqueries, it can help you query complex data structures.

SQL Subquery with WHERE Example ✅

Let's take a practical example to understand this better. Suppose we have two tables, employees and departments, and we want to find employees from a specific department:

sql

-- employees table
id | name     | department_id
-----------------------------
1  | John     | 101
2  | Sarah    | 101
3  | Mike     | 102
4  | Alice    | 102

-- departments table
id | department_name
--------------------
101 | IT
102 | HR

To find employees from the IT department, we can use a subquery with the WHERE clause like this:

sql

SELECT * FROM employees
WHERE department_id = (
    SELECT id FROM departments WHERE department_name = 'IT'
);

This query works by first executing the subquery, which returns the id of the IT department (101). Then, the main query selects all rows from the employees table where the department_id matches the value returned by the subquery.

Advanced Subquery Example 💡

Now let's take it a step further. Suppose we want to find the average salary of employees in each department:

sql

SELECT department_id, AVG(salary) as avg_salary
FROM employees
WHERE department_id = (
    SELECT id FROM departments
)
GROUP BY department_id;

In this example, the subquery simply returns all department IDs. The main query then calculates the average salary for each department using the GROUP BY clause.

Quiz Time! 🎯

Stay tuned for more SQL tutorials at CodeYourCraft! 💡