How to Work with Subqueries in SQL

                     How to Work with Subqueries in SQL
               



Tópico:
                     How to Work with Subqueries in SQL
               
Categoria: Tutoriais | FreeCodeCamp Premium
Idioma Principal: Português (Conteúdo de Tecnologia)

Conteúdo do Tutorial / Guia Passo a Passo:
-------------------------------------------------------------------------
Whenever you see a query nested inside another query in SQL, that's a subquery. A subquery is also known as an inner query while the one that contains it is called the main or outer query.

Subqueries are used to provide the main query with additional data in the form of a derived column or derived table, or they can filter the rows returned by the main query.

Subqueries can be quite difficult to understand, especially for beginners who are just starting out in SQL. This article will help simplify this concept so that it is much easier to understand. By the end, you should be able to use subqueries more easily to solve problems.

Table of Contents

• Prerequisites

• How Subqueries Work

• Execution Order

• Types of Subqueries

• Non-correlated Subqueries

• Correlated Subqueries

• Conclusion

Prerequisites:

Subqueries are an advanced SQL concept, so it's important to have a solid understanding of the basics of SQL: SELECT, FROM, WHERE, JOINS, the CASE statement, and the proper order for query execution.

How Subqueries Work

Let's start by considering an example of a query with a subquery.

Código Selecionar Expandir

SELECT *
FROM registration
WHERE student_id
IN (SELECT
id
FROM student
WHERE location = 'Lagos')

The above query has two parts: the main query and the subquery.

This is the main query:

Código Selecionar Expandir

SELECT *
FROM registration
WHERE student_id
IN (...)

Notice that there is currently nothing in the brackets of the main query.

The part of the query that's enclosed in the brackets is the subquery. It's used to filter the rows returned by the main query. Let's look at the subquery code now:

Código Selecionar Expandir

SELECT
id
FROM student
WHERE location = 'Lagos'

Execution Order

When you run the whole query in your management system like this:

Código Selecionar Expandir

SELECT *
FROM registration
WHERE student_id
IN (SELECT
id
FROM student
WHERE location = 'Lagos')

You'll get the result below:

The result is showing the registration details of all students from Lagos. But to get this result, SQL follows an execution order which we'll discuss below.

When you execute the whole query, behind the scenes, the subquery is evaluated first:

Código Selecionar Expandir

SELECT
id
FROM student
WHERE location = 'Lagos'

The subquery retrieves the IDs of students from Lagos from the student table. You get a result like this:

Behind the scenes, these

Código Selecionar Expandir

IDsreturned by the subquery are provided to the main query, transforming the query to look like this:

Código Selecionar Expandir

SELECT *
FROM registration
WHERE student_id
IN('STU1', 'STU13','STU2','STU4','STU23','STU27')

The main query compares each value in its

Código Selecionar Expandir

student_idcolumn with the

Código Selecionar Expandir

IDsreturned by the subquery. If a match is found, it returns the registration details of that student. Otherwise, the record is ignored.

You might wonder, "Why not just retrieve the

Código Selecionar Expandir

IDsfrom the student table and directly pass them to the main query instead of using a subquery?"

Well, that's hardcoding. While the query will work at the moment, later when there are new students and you rerun the query, you'll only get details of the old students for whom you manually passed

Código Selecionar Expandir

IDsto the main query. It'll exclude the new ones.

The subquery approach is dynamic: it continuously queries the student table to ensure that the results are always current.

Types of Subqueries

There are two types of subqueries based on dependen

... [O tutorial continua no link abaixo] ...