Nested Queries

Excellent news!

The school administration expresses satisfaction with our recent performance and signals readiness to progress further.

As they embark on their summer recess and we enter a period of flexibility, we seize the opportunity to delve into novel methodologies and enhance our interaction with SQL.

Specifically, we aim to explore nested queries, a fundamental concept within SQL operations.

To elucidate this concept, let's consider an illustrative example drawn from our organizational database. With the company's expansion, a new entity, namely department, is incorporated. This addition augments our data structure, influencing the dynamics of our database operations.

Let's examine the structure of this new table to better understand its role within our evolving database architecture:

As you can see, 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.

Here's how our query will look:


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

As you can see, this syntax is quite intuitive. 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

As you can see, we obtained 5 departments in response. Remember this table.

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 identical to the result we obtained with the first query.

So, let's break down step by step what we're doing:

The inner query retrieves the names of departments we need.
Then, using a WHERE clause, we establish a certain criterion that checks whether the departments are present in the table we retrieved with the inner query.
We obtain the desired result.

The syntax for using an inner query will look as follows:

But there are several important points to consider when using inner queries:

Single Result in Subquery: The nested query should return only one column, but it can also be restricted to a single value if used in a comparison condition. This is particularly important for operators such as =, >, <, >=, <=, <>;
Data Types: Ensure that the data type of the returned column matches the data type of the column to which the condition is applied to avoid type incompatibility errors;
Optimization: Nested queries can be inefficient, especially if they are executed for each row of the main query;
Using the IN Operator: Nested queries are often used with the IN operator to check for the presence of values in a subset of data. However, comparison operators (>, <, >=, <=, <>) can also be used for more complex conditions.

In the following chapters, we'll practice with inner queries together and also learn some new nuances about their usage.

Everything was clear?

Thanks for your feedback!

Section 2. Chapter 1

Intermediate SQL

Nested Queries