Eu gostaria de saber o seguinte:
Estou planejando usar isso na minha aplicação (por exemplo - PHP), mas don'não quero executar várias consultas ao banco de dados, que opções tenho para obter dados de várias tabelas em uma única consulta?
Nota: Eu estou escrevendo isto porque eu gostaria de poder fazer um link para um guia bem escrito sobre as inúmeras perguntas que eu constantemente me deparo na fila PHP, para que eu possa fazer um link para isto para mais detalhes quando eu postar uma resposta.
As respostas cobrem o seguinte:
Esta resposta cobre:
mysql> create table colors(id int(3) not null auto_increment primary key,
-> color varchar(15), paint varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | varchar(15) | YES | | NULL | |
| paint | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into colors (color, paint) values ('Red', 'Metallic'),
-> ('Green', 'Gloss'), ('Blue', 'Metallic'),
-> ('White' 'Gloss'), ('Black' 'Gloss');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from colors;
+----+-------+----------+
| id | color | paint |
+----+-------+----------+
| 1 | Red | Metallic |
| 2 | Green | Gloss |
| 3 | Blue | Metallic |
| 4 | White | Gloss |
| 5 | Black | Gloss |
+----+-------+----------+
5 rows in set (0.00 sec)
A tabela de marcas identifica as diferentes marcas dos carros que podem ser vendidas no estaleiro.
mysql> create table brands (id int(3) not null auto_increment primary key,
-> brand varchar(15));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from brands;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| brand | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into brands (brand) values ('Ford'), ('Toyota'),
-> ('Nissan'), ('Smart'), ('BMW');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from brands;
+----+--------+
| id | brand |
+----+--------+
| 1 | Ford |
| 2 | Toyota |
| 3 | Nissan |
| 4 | Smart |
| 5 | BMW |
+----+--------+
5 rows in set (0.00 sec)
A tabela de modelos cobrirá diferentes tipos de carros, será mais simples para isto utilizar diferentes tipos de carros em vez de modelos reais.
mysql> create table models (id int(3) not null auto_increment primary key,
-> model varchar(15));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from models;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| model | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into models (model) values ('Sports'), ('Sedan'), ('4WD'), ('Luxury');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from models;
+----+--------+
| id | model |
+----+--------+
| 1 | Sports |
| 2 | Sedan |
| 3 | 4WD |
| 4 | Luxury |
+----+--------+
4 rows in set (0.00 sec)
E finalmente, para amarrar todas estas outras mesas, a mesa que amarra tudo junto. O campo de identificação é na verdade o número de lote único usado para identificar carros.
mysql> create table cars (id int(3) not null auto_increment primary key,
-> color int(3), brand int(3), model int(3));
Query OK, 0 rows affected (0.01 sec)
mysql> show columns from cars;
+-------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| color | int(3) | YES | | NULL | |
| brand | int(3) | YES | | NULL | |
| model | int(3) | YES | | NULL | |
+-------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1),
-> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from cars;
+----+-------+-------+-------+
| id | color | brand | model |
+----+-------+-------+-------+
| 1 | 1 | 2 | 1 |
| 2 | 3 | 1 | 2 |
| 3 | 5 | 3 | 1 |
| 4 | 4 | 4 | 2 |
| 5 | 2 | 2 | 3 |
| 6 | 3 | 5 | 4 |
| 7 | 4 | 1 | 3 |
| 8 | 2 | 2 | 1 |
| 9 | 5 | 2 | 3 |
| 10 | 4 | 5 | 1 |
+----+-------+-------+-------+
10 rows in set (0.00 sec)
Isso nos dará dados suficientes (espero) para cobrir os exemplos abaixo de diferentes tipos de adesões e também dados suficientes para fazê-los valer a pena.
Então, entrando no assunto, o chefe quer saber As identificações de todos os carros esportivos que ele tem.
Esta é uma simples junção de duas mesas. Temos uma mesa que identifica o modelo e a mesa com o estoque disponível nela. Como você pode ver, os dados na coluna "modelo" da tabela "carros" estão relacionados com a coluna "modelos" da tabela "carros" que temos. Agora, nós sabemos que a tabela de modelos tem um ID de 1
para Sports
, então vamos escrever o join.
select
ID,
model
from
cars
join models
on model=ID
Então esta pergunta parece boa, certo? Nós identificamos as duas tabelas e contém as informações que precisamos e usamos um join que identifica corretamente em que colunas juntar.
ERROR 1052 (23000): Column 'ID' in field list is ambiguous
Oh, não! Um erro na nossa primeira consulta! Sim, e é uma ameixa. Veja, a consulta tem de facto as colunas certas, mas algumas delas existem em ambas as tabelas, por isso a base de dados fica confusa sobre o que realmente queremos dizer com coluna e onde. Existem duas soluções para resolver isto. A primeira é simples e agradável, podemos usar `tableName.columnName' para dizer ao banco de dados exatamente o que queremos dizer, assim:
select
cars.ID,
models.model
from
cars
join models
on cars.model=models.ID
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
| 2 | Sedan |
| 4 | Sedan |
| 5 | 4WD |
| 7 | 4WD |
| 9 | 4WD |
| 6 | Luxury |
+----+--------+
10 rows in set (0.00 sec)
O outro é provavelmente mais utilizado e é chamado de aliasing de mesa. As tabelas neste exemplo têm nomes simples e curtos, mas digitando algo como KPI_DAILY_SALES_BY_DEPARTMENT
provavelmente envelheceria rapidamente, então uma maneira simples é apelidar a tabela desta forma:
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
Agora, de volta ao pedido. Como você pode ver, temos as informações que precisamos, mas também temos informações que foram't solicitadas, então precisamos incluir uma cláusula de onde na declaração para obter apenas os carros esportivos, como foi solicitado. Como eu prefiro o método do alias da tabela ao invés de usar os nomes da tabela repetidamente, eu vou me ater a ele a partir deste ponto.
Claramente, precisamos de adicionar uma cláusula de where à nossa consulta. Podemos identificar os carros desportivos por ID=1
ou model='Sports'
. Como o ID é indexado e a chave primária (e por acaso é menos digitada), vamos utilizar isso na nossa consulta.
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
where
b.ID=1
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)
Bingo! O chefe está feliz. Claro que, sendo um chefe e nunca sendo feliz com o que pediu, ele olha a informação, depois diz Eu também quero as cores.
Ok, então temos uma boa parte da nossa pergunta já escrita, mas precisamos usar uma terceira tabela que é cores. Agora, nossa principal tabela de informações carros
armazena o ID da cor do carro e este link de volta para a coluna de ID de cores. Assim, de forma semelhante ao original, podemos juntar uma terceira tabela:
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 3 | Sports |
| 8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)
Raios, embora a tabela tenha sido correctamente unida e as colunas relacionadas tenham sido ligadas, esquecemo-nos de puxar a actual informação da nova tabela que acabámos de ligar.
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
where
b.ID=1
+----+--------+-------+
| ID | model | color |
+----+--------+-------+
| 1 | Sports | Red |
| 8 | Sports | Green |
| 10 | Sports | White |
| 3 | Sports | Black |
+----+--------+-------+
4 rows in set (0.00 sec)
Certo, isso'é o chefe que nos deixa em paz por um momento. Agora, para explicar um pouco disto com mais detalhes. Como você pode ver, a cláusula `from' em nossa declaração liga nossa tabela principal (eu frequentemente uso uma tabela que contém informações ao invés de uma tabela de dimensões ou de consulta). A consulta funcionaria tão bem com as tabelas todas trocadas, mas faz menos sentido quando voltamos a esta consulta para lê-la dentro de alguns meses, por isso muitas vezes é melhor tentar escrever uma consulta que seja agradável e fácil de entender - coloque-a de forma intuitiva, use um travessão agradável para que tudo fique o mais claro possível. Se você continuar a ensinar os outros, tente instilar essas características em suas consultas - especialmente se você estará solucionando problemas. É inteiramente possível continuar ligando mais e mais tabelas desta maneira.
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
Embora eu tenha esquecido de incluir uma tabela onde talvez queiramos juntar mais de uma coluna na declaração de "adesão", aqui está um exemplo. Se a tabela de models
tivesse modelos específicos da marca e portanto também tivesse uma coluna chamada brand
que se ligasse de volta à tabela de marcas
no campo ID
, isso poderia ser feito desta forma:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
and b.brand=d.ID
where
b.ID=1
Você pode ver, a consulta acima não só liga as tabelas unidas à tabela principal de cars
, mas também especifica as uniões entre as tabelas já unidas. Se isto foi't feito, o resultado é chamado de join cartesiano - que é dba falar mal. Um join cartesiano é aquele onde as linhas são retornadas porque a informação não't diz ao banco de dados como limitar os resultados, então a consulta retorna todas as linhas que se encaixam no critério.
Então, para dar um exemplo de um join cartesiano, vamos executar a seguinte consulta:
select
a.ID,
b.model
from
cars a
join models b
+----+--------+
| ID | model |
+----+--------+
| 1 | Sports |
| 1 | Sedan |
| 1 | 4WD |
| 1 | Luxury |
| 2 | Sports |
| 2 | Sedan |
| 2 | 4WD |
| 2 | Luxury |
| 3 | Sports |
| 3 | Sedan |
| 3 | 4WD |
| 3 | Luxury |
| 4 | Sports |
| 4 | Sedan |
| 4 | 4WD |
| 4 | Luxury |
| 5 | Sports |
| 5 | Sedan |
| 5 | 4WD |
| 5 | Luxury |
| 6 | Sports |
| 6 | Sedan |
| 6 | 4WD |
| 6 | Luxury |
| 7 | Sports |
| 7 | Sedan |
| 7 | 4WD |
| 7 | Luxury |
| 8 | Sports |
| 8 | Sedan |
| 8 | 4WD |
| 8 | Luxury |
| 9 | Sports |
| 9 | Sedan |
| 9 | 4WD |
| 9 | Luxury |
| 10 | Sports |
| 10 | Sedan |
| 10 | 4WD |
| 10 | Luxury |
+----+--------+
40 rows in set (0.00 sec)
Bom Deus, isso'é feio. No entanto, no que diz respeito à base de dados, é exactamente o que foi pedido. Na consulta, nós pedimos o ID
de carros
e o modelo
de modelos
. No entanto, porque não especificamos como se juntar às tabelas, a base de dados tem correspondência entre todas as linhas da primeira tabela e todas as linhas da segunda tabela.
Ok, então o chefe está de volta, e ele quer mais informações novamente. Eu quero a mesma lista, mas também incluir 4WDs nela.
Isto, no entanto, dá-nos uma grande desculpa para olharmos para duas maneiras diferentes de conseguirmos isto. Poderíamos adicionar outra condição à cláusula "onde" como esta:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
or b.ID=3
Embora o acima descrito funcione perfeitamente bem, vamos olhar para ele de forma diferente, esta é uma ótima desculpa para mostrar como uma consulta union
irá funcionar.
Nós sabemos que o seguinte irá devolver todos os carros desportivos:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
E o seguinte devolveria todos os 4WD:
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3
Assim, ao adicionar uma cláusula de "união de todos" entre eles, os resultados da segunda consulta serão anexados aos resultados da primeira consulta.
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=1
union all
select
a.ID,
b.model,
c.color
from
cars a
join models b
on a.model=b.ID
join colors c
on a.color=c.ID
join brands d
on a.brand=d.ID
where
b.ID=3
+----+--------+-------+
| ID | model | color |
+----+--------+-------+
| 1 | Sports | Red |
| 8 | Sports | Green |
| 10 | Sports | White |
| 3 | Sports | Black |
| 5 | 4WD | Green |
| 7 | 4WD | White |
| 9 | 4WD | Black |
+----+--------+-------+
7 rows in set (0.00 sec)
Como você pode ver, os resultados da primeira consulta são retornados primeiro, seguidos pelos resultados da segunda consulta.
Neste exemplo, teria sido muito mais fácil simplesmente utilizar a primeira consulta, mas as consultas union
podem ser ótimas para casos específicos. Elas são uma ótima maneira de retornar resultados específicos de tabelas de tabelas que são't facilmente unidas - ou para esse fim completamente tabelas não relacionadas. Existem algumas regras a seguir, no entanto.
union
e union all
. Uma consulta union
irá remover duplicatas, enquanto uma consulta union all
não irá. Isso significa que há um pequeno impacto de performance quando se utiliza union
sobre union all
mas os resultados podem valer a pena - eu ganhei'mas não especulei sobre esse tipo de coisa.
Nesta nota, pode valer a pena notar algumas notas adicionais aqui.ordem por
mas você pode'não utilizar mais o pseudônimo. Na consulta acima, anexar um ordem por a.ID
resultaria em um erro - no que diz respeito aos resultados, a coluna é chamada ID
ao invés de a.ID
- mesmo que o mesmo alias tenha sido utilizado em ambas as consultas.Holden
à tabela de marcas.
Eu também adicionei uma linha em cars
que tem o valor color
de 12
- que não tem nenhuma referência na tabela de cores.
Ok, o chefe está de volta, ladrando pedidos - *Eu quero uma contagem de cada marca que carregamos e o número de carros nela! - Típico, nós só chegamos a uma seção interessante da nossa discussão e o chefe quer mais trabalho.
Rightyo, então a primeira coisa que precisamos fazer é obter uma lista completa de possíveis marcas.select
a.brand
from
brands a
+--------+
| brand |
+--------+
| Ford |
| Toyota |
| Nissan |
| Smart |
| BMW |
| Holden |
+--------+
6 rows in set (0.00 sec)
Agora, quando juntamos isto à mesa dos nossos carros, obtemos o seguinte resultado:
select
a.brand
from
brands a
join cars b
on a.ID=b.brand
group by
a.brand
+--------+
| brand |
+--------+
| BMW |
| Ford |
| Nissan |
| Smart |
| Toyota |
+--------+
5 rows in set (0.00 sec)
O que é, claro, um problema - nós estamos'não vendo nenhuma menção à adorável marca Holden
que eu adicionei.
Isto é porque um join procura por linhas correspondentes em tabelas ambos. Como não há dados nos carros que são do tipo Holden
é't retornado. Aqui é onde podemos utilizar um 'outter' join. Isto irá retornar todos os resultados de uma tabela, sejam eles combinados na outra tabela ou não:
select
a.brand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand
+--------+
| brand |
+--------+
| BMW |
| Ford |
| Holden |
| Nissan |
| Smart |
| Toyota |
+--------+
6 rows in set (0.00 sec)
Agora que temos isso, podemos adicionar uma adorável função agregada para fazer uma contagem e tirar o chefe das nossas costas por um momento.
select
a.brand,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
group by
a.brand
+--------+--------------+
| brand | countOfBrand |
+--------+--------------+
| BMW | 2 |
| Ford | 2 |
| Holden | 0 |
| Nissan | 1 |
| Smart | 1 |
| Toyota | 5 |
+--------+--------------+
6 rows in set (0.00 sec)
E com isso, longe os patrões se esquivam.
Agora, para explicar isso com mais detalhes, as uniões externas podem ser do tipo "esquerda" ou "direita". A Esquerda ou Direita define qual tabela está fortemente incluída. Uma "junção externa esquerda" incluirá todas as linhas da tabela da esquerda, enquanto (você adivinhou) uma "junção externa direita" traz todos os resultados da tabela da direita para os resultados.
Algumas bases de dados permitirão um full outer join
que trará de volta os resultados (sejam ou não combinados) das tabelas ambas, mas isto é't suportado em todas as bases de dados.
Agora, eu provavelmente acho que neste momento, você está se perguntando se você pode ou não fundir tipos de join em uma consulta - e a resposta é sim, você pode absolutamente.
select
b.brand,
c.color,
count(a.id) as countOfBrand
from
cars a
right outer join brands b
on b.ID=a.brand
join colors c
on a.color=c.ID
group by
a.brand,
c.color
+--------+-------+--------------+
| brand | color | countOfBrand |
+--------+-------+--------------+
| Ford | Blue | 1 |
| Ford | White | 1 |
| Toyota | Black | 1 |
| Toyota | Green | 2 |
| Toyota | Red | 1 |
| Nissan | Black | 1 |
| Smart | White | 1 |
| BMW | Blue | 1 |
| BMW | White | 1 |
+--------+-------+--------------+
9 rows in set (0.00 sec)
Então, por que não são esses os resultados esperados? É porque embora tenhamos selecionado a junção externa de carros para marcas, ela foi't especificada na junção para cores - de modo que a junção particular só trará de volta resultados que combinam em ambas as tabelas. Aqui está a consulta que funcionaria para obter os resultados que esperávamos:
select
a.brand,
c.color,
count(b.id) as countOfBrand
from
brands a
left outer join cars b
on a.ID=b.brand
left outer join colors c
on b.color=c.ID
group by
a.brand,
c.color
+--------+-------+--------------+
| brand | color | countOfBrand |
+--------+-------+--------------+
| BMW | Blue | 1 |
| BMW | White | 1 |
| Ford | Blue | 1 |
| Ford | White | 1 |
| Holden | NULL | 0 |
| Nissan | Black | 1 |
| Smart | White | 1 |
| Toyota | NULL | 1 |
| Toyota | Black | 1 |
| Toyota | Green | 2 |
| Toyota | Red | 1 |
+--------+-------+--------------+
11 rows in set (0.00 sec)
Como podemos ver, temos duas juntas externas na consulta e os resultados estão a chegar como esperado.
Agora, e quanto a esses outros tipos de junções que você pergunta? E quanto às Intersecções?
Bem, nem todas as bases de dados suportam a intersecção
mas praticamente todas as bases de dados lhe permitirão criar uma intersecção através de uma junção (ou uma bem estruturada onde a declaração, no mínimo).
Uma Intersecção é um tipo de join algo similar a um "sindicato" como descrito acima - mas a diferença é que ela somente retorna linhas de dados que são idênticas (e eu quero dizer idênticas) entre as várias consultas individuais unidas pelo sindicato. Apenas as linhas que são idênticas em todos os aspectos serão devolvidas.
Um exemplo simples seria como tal:
select
*
from
colors
where
ID>2
intersect
select
*
from
colors
where
id<4
Enquanto uma consulta normal union
retornaria todas as linhas da tabela (a primeira consulta retornaria qualquer coisa acima de ID>2
e a segunda qualquer coisa com ID<4
) o que resultaria em um conjunto completo, uma consulta intersectaria apenas a linha correspondente a id=3
pois atende a ambos os critérios.
Agora, se o seu banco de dados não suporta uma consulta intersect
, o acima pode ser facilmente acomodado com a seguinte consulta:
select
a.ID,
a.color,
a.paint
from
colors a
join colors b
on a.ID=b.ID
where
a.ID>2
and b.ID<4
+----+-------+----------+
| ID | color | paint |
+----+-------+----------+
| 3 | Blue | Metallic |
+----+-------+----------+
1 row in set (0.00 sec)
Se você deseja realizar uma intersecção entre duas tabelas diferentes usando uma base de dados que não'não suporta inerentemente uma consulta de intersecção, você precisará criar uma junção em todas as colunas das tabelas.
Ok, agora o patrão entrou de novo em cena - Quero uma lista de todos os nossos carros com a marca e um total de quantos dessa marca temos! Esta é uma grande oportunidade para usar o próximo truque no nosso saco de guloseimas SQL - a subconsulta. Se você não está familiarizado com o termo, uma subconsulta é uma consulta que corre dentro de outra consulta. Há muitas maneiras diferentes de usá-las. Para o nosso pedido, vamos primeiro juntar uma simples consulta que listará cada carro e a marca:
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
Agora, se quiséssemos simplesmente ter uma contagem de carros ordenada por marca, poderíamos, naturalmente, escrever isto:
select
b.brand,
count(a.ID) as countCars
from
cars a
join brands b
on a.brand=b.ID
group by
b.brand
+--------+-----------+
| brand | countCars |
+--------+-----------+
| BMW | 2 |
| Ford | 2 |
| Nissan | 1 |
| Smart | 1 |
| Toyota | 5 |
+--------+-----------+
Então, devemos ser capazes de simplesmente adicionar na função de contagem à nossa consulta original, certo?
select
a.ID,
b.brand,
count(a.ID) as countCars
from
cars a
join brands b
on a.brand=b.ID
group by
a.ID,
b.brand
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 1 | Toyota | 1 |
| 2 | Ford | 1 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 5 | Toyota | 1 |
| 6 | BMW | 1 |
| 7 | Ford | 1 |
| 8 | Toyota | 1 |
| 9 | Toyota | 1 |
| 10 | BMW | 1 |
| 11 | Toyota | 1 |
+----+--------+-----------+
11 rows in set (0.00 sec)
Infelizmente, não, nós podemos' não podemos fazer isso. A razão é que quando adicionamos o ID do carro (coluna a.ID) temos que adicioná-lo ao grupo por - então agora, quando a função de contagem funciona, há apenas um ID igualado por ID. É aqui que podemos usar uma subconsulta - na verdade, podemos fazer dois tipos completamente diferentes de subconsulta que retornarão os mesmos resultados que precisamos para isso. O primeiro é simplesmente colocar a subconsulta na cláusula `select'. Isto significa que cada vez que obtivermos uma linha de dados, a subconsulta irá sair, obter uma coluna de dados e, em seguida, colocá-la em nossa linha de dados.
select
a.ID,
b.brand,
(
select
count(c.ID)
from
cars c
where
a.brand=c.brand
) as countCars
from
cars a
join brands b
on a.brand=b.ID
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 2 | Ford | 2 |
| 7 | Ford | 2 |
| 1 | Toyota | 5 |
| 5 | Toyota | 5 |
| 8 | Toyota | 5 |
| 9 | Toyota | 5 |
| 11 | Toyota | 5 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 6 | BMW | 2 |
| 10 | BMW | 2 |
+----+--------+-----------+
11 rows in set (0.00 sec)
E Bam!, isto nos serviria. Mas se você notou, esta subconsulta terá que ser executada para cada linha de dados que retornamos. Mesmo neste pequeno exemplo, temos apenas cinco Marcas de carro diferentes, mas a subconsulta correu onze vezes, pois temos onze filas de dados que estamos retornando. Então, neste caso, ele não'não parece ser a maneira mais eficiente de escrever código. Para uma abordagem diferente, vamos rodar uma subconsulta e fingir que ela é uma tabela:
select
a.ID,
b.brand,
d.countCars
from
cars a
join brands b
on a.brand=b.ID
join
(
select
c.brand,
count(c.ID) as countCars
from
cars c
group by
c.brand
) d
on a.brand=d.brand
+----+--------+-----------+
| ID | brand | countCars |
+----+--------+-----------+
| 1 | Toyota | 5 |
| 2 | Ford | 2 |
| 3 | Nissan | 1 |
| 4 | Smart | 1 |
| 5 | Toyota | 5 |
| 6 | BMW | 2 |
| 7 | Ford | 2 |
| 8 | Toyota | 5 |
| 9 | Toyota | 5 |
| 10 | BMW | 2 |
| 11 | Toyota | 5 |
+----+--------+-----------+
11 rows in set (0.00 sec)
Ok, então temos os mesmos resultados (ordenados ligeiramente diferentes - parece que a base de dados queria retornar resultados ordenados pela primeira coluna que escolhemos desta vez) - mas os mesmos números corretos.
Então, qual'é a diferença entre os dois - e quando devemos usar cada tipo de subconsulta? Primeiro, vamos ter certeza que entendemos como essa segunda consulta funciona. Nós selecionamos duas tabelas na cláusula from' da nossa consulta, e então escrevemos uma consulta e dissemos ao banco de dados que era de fato uma tabela - com a qual o banco de dados está perfeitamente satisfeito. Pode haver alguns benefícios em utilizar este método (assim como algumas limitações). O mais importante é que esta subconsulta correu *once*. Se a nossa base de dados contivesse um grande volume de dados, poderia haver uma grande melhoria em relação ao primeiro método. No entanto, como estamos usando isso como uma tabela, temos que trazer linhas extras de dados - para que eles possam realmente ser unidos de volta às nossas linhas de dados. Também temos de ter a certeza de que existem *enough* linhas de dados se vamos usar uma simples junção como na consulta acima. Se você se lembrar, o join só vai puxar as linhas que têm dados correspondentes em *ambos* lados do join. Se formos't cuidado, isto pode resultar em dados válidos que não serão devolvidos da tabela dos nossos carros se houver't uma linha correspondente nesta subconsulta. Agora, olhando para a primeira subconsulta, também há algumas limitações. porque estamos puxando os dados de volta para uma única linha, podemos *ONLY* puxar de volta uma linha de dados. As subconsultas utilizadas na cláusula
select' de uma consulta frequentemente utilizam apenas uma função agregada como sum',
count', max' ou outra função agregada similar. Elas não't *t *têm*, mas muitas vezes é assim que elas são escritas. Então, antes de continuarmos, vamos dar uma rápida olhada onde mais podemos utilizar uma subconsulta. Podemos utilizá-lo na cláusula
where` - agora, este exemplo é um pouco elaborado como na nossa base de dados, existem melhores maneiras de obter os seguintes dados, mas como é apenas para um exemplo, vamos dar uma olhada:
select
ID,
brand
from
brands
where
brand like '%o%'
+----+--------+
| ID | brand |
+----+--------+
| 1 | Ford |
| 2 | Toyota |
| 6 | Holden |
+----+--------+
3 rows in set (0.00 sec)
Isto nos devolve uma lista de IDs e nomes de marcas (a segunda coluna é apenas adicionada para nos mostrar as marcas) que contêm a letra o
no nome.
Agora, podemos usar os resultados desta consulta em uma cláusula onde esta:
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
where
a.brand in
(
select
ID
from
brands
where
brand like '%o%'
)
+----+--------+
| ID | brand |
+----+--------+
| 2 | Ford |
| 7 | Ford |
| 1 | Toyota |
| 5 | Toyota |
| 8 | Toyota |
| 9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)
Como você pode ver, mesmo que a subconsulta estivesse devolvendo as três identificações de marca, nossa tabela de carros só tinha entradas para duas delas. Neste caso, para mais detalhes, a subconsulta está funcionando como se tivéssemos escrito o seguinte código:
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
where
a.brand in (1,2,6)
+----+--------+
| ID | brand |
+----+--------+
| 1 | Toyota |
| 2 | Ford |
| 5 | Toyota |
| 7 | Ford |
| 8 | Toyota |
| 9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)
Mais uma vez, você pode ver como uma subconsulta versus entradas manuais mudou a ordem das linhas ao retornar da base de dados. Enquanto discutimos as subconsultas, vamos ver o que mais podemos fazer com uma subconsulta:
select', algumas na cláusula
from' e outras na cláusula `where' - lembre-se que cada uma que você coloca está tornando sua consulta mais complexa e provavelmente levará mais tempo para ser executada.
Se você precisar escrever algum código eficiente, pode ser benéfico escrever a consulta de várias maneiras e ver (seja cronometrando-a ou usando um plano de explicação) qual é a consulta ideal para obter seus resultados. A primeira maneira que funciona pode nem sempre ser a melhor forma de o fazer.Pensei em adicionar mais alguns pedaços, para dicas e truques que surgiram.
Uma pergunta que eu vejo surgir um pouco, é Como eu consigo linhas não correspondentes em duas tabelas e eu vejo a resposta mais comumente aceita como algo como o seguinte (baseado na tabela de nossos carros e marcas - que tem Holden listado como uma marca, mas não aparece na tabela de carros):
select
a.ID,
a.brand
from
brands a
where
a.ID not in(select brand from cars)
E sim vai funcionar.
+----+--------+
| ID | brand |
+----+--------+
| 6 | Holden |
+----+--------+
1 row in set (0.00 sec)
No entanto, é não eficiente em alguma base de dados. Aqui está um link para uma questão de Stack Overflow perguntando sobre isso, e aqui está um excelente artigo em profundidade se você quiser entrar no nitty gritty.
A resposta curta é, se o otimizador não lidar com isso eficientemente, pode ser muito melhor usar uma consulta como a seguinte para obter linhas não combinadas:
select
a.brand
from
brands a
left join cars b
on a.id=b.brand
where
b.brand is null
+--------+
| brand |
+--------+
| Holden |
+--------+
1 row in set (0.00 sec)
Ahhh, outro antigo mas bom - o antigo Você pode't especificar tabela de destino 'marcas' para atualização na cláusula FROM.
O MySQL não permitirá que você execute uma consulta update...
com um subseleto na mesma tabela. Agora, você pode estar pensando, por que não simplesmente colocá-la na cláusula "where right"? Mas e se você quiser atualizar apenas a linha com a data max()
entre um monte de outras linhas? Você pode't exatamente fazer isso em uma cláusula where.
update
brands
set
brand='Holden'
where
id=
(select
id
from
brands
where
id=6);
ERROR 1093 (HY000): You can't specify target table 'brands'
for update in FROM clause
Então, nós podemos'não podemos fazer isso eh? Bem, não exactamente. Há uma alternativa que um número surpreendentemente grande de utilizadores não't sabe - embora inclua algum hackery ao qual terá de prestar atenção.
Você pode enfiar a subquisição dentro de outra subquisição, o que coloca uma lacuna suficiente entre as duas consultas para que funcione. No entanto, note que pode ser mais seguro colar a consulta dentro de uma transação - isto evitará que qualquer outra alteração seja feita nas tabelas enquanto a consulta estiver em execução.
update
brands
set
brand='Holden'
where id=
(select
id
from
(select
id
from
brands
where
id=6
)
as updateTable);
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1 Changed: 0 Warnings: 0