Uma subquery é uma consulta aninhada em outra consulta, isto é, uma consulta dentro de outra.
Query significa consulta em português e subquery, logicamente, subconsulta. Para realizar uma query usamos select em sql.
Este tutorial se concentra nos três tipos de subquery:
- subquery escalares
- subquery de linha
- subquery que aparecem na frente o “from” do select
As Tabelas que Trabalharemos
Iremos trabalhar com as tabelas country_stats e countries que estão dentro do banco de dados nation. Esse banco nós baixamos já pronto e preenchido nesse artigo: MariaDB e MySQL: Baixando Exemplo de Banco de Dados.
Estrutura da Tabela country_stats
MariaDB [nation]> desc country_stats; +------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | country_id | int(11) | NO | PRI | NULL | | | year | int(11) | NO | PRI | NULL | | | population | int(11) | YES | | NULL | | | gdp | decimal(15,0) | YES | | NULL | | +------------+---------------+------+-----+---------+-------+
Estrutura da Tabela countries
MariaDB [nation]> desc countries; +---------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+----------------+ | country_id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | area | decimal(10,2) | NO | | NULL | | | national_day | date | YES | | NULL | | | country_code2 | char(2) | NO | UNI | NULL | | | country_code3 | char(3) | NO | UNI | NULL | | | region_id | int(11) | NO | MUL | NULL | | +---------------+---------------+------+-----+---------+----------------+
Subquery Escalar
Uma subconsulta escalar retorna um único valor e pode ser usada em uma instrução SQL em que um valor literal ou de coluna única é usado.
A consulta abaixo que retorna todos os registros, todo o conteúdo, da tabela countries.
select * from countries
Já a query abaixo retorna apenas um valor: o país com maior área da tabela countries. Para isso foi usada a função max().
Essa query retorna um único valor: a maior área.
select max(area) from countries;
E deixá-las assim:
select *
from countries
where area = (
select max(area)
from countries
);
Vamos praticar:
select name, area from countries where area = (select max(area) from countries);
MariaDB [nation]> select name, area from countries where area = (select max(area) from countri es); +--------------------+-------------+ | name | area | +--------------------+-------------+ | Russian Federation | 17075400.00 | +--------------------+-------------+
Subquery de Linha
Uma subconsulta de linha retorna uma linha. 🙂 por exemplo, a query abaixo retorna uma linha, ou seja, mais de um valor. GDP é a mesma coisa que PIB(produto interno bruto).
select avg(population), avg(gdp) from country_stats where year = 2018;
MariaDB [nation]> select avg(population), avg(gdp) from country_stats where year = 2018; +-----------------+-------------------+ | avg(population) | avg(gdp) | +-----------------+-------------------+ | 40433995.4615 | 459285934925.5659 | +-----------------+-------------------+
Podemos usar essa query para encontrar países cuja população é maior que a média em 2018. Para calcular a média, como vimos acima, usamos a função avg().
select name,population, gdp, year from countries inner join country_stats using (country_id) where year=2018 and (population) > (select avg(population) from country_stats where year=2018);
MariaDB [nation]> select name,population, gdp, year from countries inner join country_stats using (country_id) where year=2018 and (population) > (40433995.4615); +--------------------------------------+------------+----------------+------+ | name | population | gdp | year | +--------------------------------------+------------+----------------+------+ | Argentina | 44494502 | 518475000000 | 2018 | | Bangladesh | 161356039 | 274025000000 | 2018 | | Brazil | 209469333 | 1868630000000 | 2018 | | China | 1392730000 | 13608200000000 | 2018 | | The Democratic Republic of the Congo | 84068091 | 47227535291 | 2018 | | Colombia | 49648685 | 330228000000 | 2018 | | Germany | 82927922 | 3996760000000 | 2018 | | Algeria | 42228429 | 180689000000 | 2018 | | Egypt | 98423595 | 250895000000 | 2018 | | Spain | 46723749 | 1426190000000 | 2018 | | Ethiopia | 109224559 | 84355462494 | 2018 | | France | 66987244 | 2777540000000 | 2018 | | United Kingdom | 66488991 | 2825210000000 | 2018 | | Indonesia | 267663435 | 1042170000000 | 2018 | | India | 1352617328 | 2726320000000 | 2018 | | Italy | 60431283 | 2073900000000 | 2018 | | Japan | 126529100 | 4970920000000 | 2018 | | Kenya | 51393010 | 87908262520 | 2018 | | South Korea | 51635256 | 1619420000000 | 2018 | | Mexico | 126190788 | 1223810000000 | 2018 | | Myanmar | 53708395 | 71214803378 | 2018 | | Nigeria | 195874740 | 397270000000 | 2018 | | Pakistan | 212215030 | 312570000000 | 2018 | | Philippines | 106651922 | 330910000000 | 2018 | | Russian Federation | 144478050 | 1657550000000 | 2018 | | Sudan | 41801533 | 40851536134 | 2018 | | Thailand | 69428524 | 504993000000 | 2018 | | Turkey | 82319724 | 766509000000 | 2018 | | Tanzania | 56318348 | 57437073927 | 2018 | | Uganda | 42723139 | 27476945526 | 2018 | | Ukraine | 44622516 | 130832000000 | 2018 | | United States | 327167434 | 20494100000000 | 2018 | | Vietnam | 95540395 | 244948000000 | 2018 | | South Africa | 57779622 | 368288000000 | 2018 | +--------------------------------------+------------+----------------+------+
Subqueries que Aparecem na Frente o “from” do Select
Veja:
select * from minha_tabela
Todo mundo sabe que na frente de from digitamos um nome de uma tabela, no caso acima a tabela é minha_tabela.
Sim, o comum é colocarmos logo após o “from” o nome de uma tabela. Mas saiba que o resultado de uma query pode ser considerada também como uma tabela. A essa tabela obtida como resultado de uma consulta chamamos de “tabela virtual“.
Então, como uma consulta com select retorna um conjunto de resultados que é o mesmo que uma tabela virtual, você pode usar como subquery para o from.
Uma subqyery que aparece na frente de from é comumente chamada de tabela derivada. Uma tabela derivada deve ter um alias(apelido) ou você receberá um erro de sintaxe.
Vamos ver uma exemplo:
select avg(region_area) from (select sum(area) region_area from countries group
by region_id) meu_resultado;
MariaDB [nation]> select avg(region_area) from (select sum(area) region_area from countries group by region_id) meu_resultado; +------------------+ | avg(region_area) | +------------------+ | 5958252.276000 | +------------------+
Acima, primeiro, a subquery retorna a área total de cada região e, em seguida, a consulta a área média de todas as regiões.