Nested Queries

The school administration is pleased with our recent achievements and is eager to move forward.

As they begin their summer break, we have the chance to explore new methods and improve our SQL skills.

Our focus will be on nested queries, a key concept in SQL.

Let's look at an example from our company database. As the company grows, we've added a new department table. This changes how our database works.

Let's check out the new table's structure to see its role in our database:

The new table contains departments, their types, and budgets. And in the employee table, as you may recall, there is information about the department each employee belongs to.

Let's consider an example where we use nested queries to retrieve those employees who work in departments with budgets of $500,000 or less:


              1234567
            
SELECT *
FROM employees
WHERE department IN (
  SELECT name 
  FROM department
  WHERE budget <= 500000
)

We use a WHERE clause to specify that the department name should be present in the table we retrieve with the inner query.

Let's break down this query into two separate ones to see how it works. First, let's retrieve the names of departments with budgets of $500,000 or less (our inner query):


              123
            
SELECT name 
FROM department
WHERE budget <= 500000

Now, considering the obtained result, let's write the second query to retrieve information about the employees from these departments:


              12345
            
SELECT * 
FROM employees
WHERE department IN (
  'Marketing', 'Human Resources', 'Finance', 'Customer Service', 'Legal'
)

The result of this query will be the same as the first query.

Here's a step-by-step breakdown:

The inner query gets the names of the departments we need;
The WHERE clause checks if these departments are in the table from the inner query;
We get the result we want.

Here's the basic syntax for an inner query:

When using nested queries, keep these important points in mind:

Single Result in Subquery: ensure the nested query returns only one column. If used in a comparison, it should ideally return a single value. This is crucial for operators like =, >, <, >=, <=, <>;
Data Types: the data type of the returned column should match the column you're comparing it to, to avoid errors;
Optimization: nested queries can be slow, especially if run for each row in the main query;
Using the IN Operator: nested queries often use the IN operator to check for values in a subset. You can also use comparison operators for more complex conditions.

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 1

Intermediate SQL

Nested Queries

1. What is a nested query in SQL?

2. Which SQL operator is commonly used with nested queries to check for values in a subset?

3. Why is it important for the data type of the column returned by a nested query to match the column it is compared to?