Ik zou graag het volgende willen weten:
Ik ben van plan dit te gebruiken in mijn (bijvoorbeeld - PHP) applicatie, maar wil niet meerdere queries tegen de database uitvoeren, welke opties heb ik om gegevens uit meerdere tabellen in een enkele query te krijgen?
Opmerking: ik schrijf dit omdat ik graag een link zou willen hebben naar een goed geschreven gids over de vele vragen die ik voortdurend tegenkom in de PHP wachtrij, zodat ik hiernaar kan linken voor verdere details wanneer ik een antwoord post.
De antwoorden gaan over het volgende:
Dit antwoord behandelt:
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)
De merkenlijst geeft de verschillende merken aan van de auto's die mogelijk verkocht kunnen worden.
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)
De model-tabel zal verschillende types van auto's bevatten, het zal eenvoudiger zijn om verschillende autotypes te gebruiken in plaats van echte automodellen.
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)
En tenslotte, om al deze andere tabellen te verbinden, de tabel die alles samenbrengt. Het ID-veld is eigenlijk het unieke lotnummer dat gebruikt wordt om auto's te identificeren.
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)
Dit geeft ons genoeg data (hoop ik) om de onderstaande voorbeelden van verschillende types van joins te behandelen en ook genoeg data om ze de moeite waard te maken.
Dus om in de kern te komen, de baas wil de ID's weten van alle sportauto's die hij heeft.
Dit is een eenvoudige twee tabel join. We hebben een tabel die het model identificeert en de tabel met de beschikbare voorraad erin. Zoals je kunt zien, hebben de gegevens in de model
kolom van de cars
tabel betrekking op de modellen
kolom van de cars
tabel die we hebben. Nu, we weten dat de modellen tabel een ID van 1
heeft voor Sports
dus laten we de join schrijven.
select
ID,
model
from
cars
join models
on model=ID
Dus deze query ziet er goed uit, toch? We hebben de twee tabellen geïdentificeerd en bevatten de informatie die we nodig hebben en gebruiken een join die correct aangeeft op welke kolommen we moeten joinen.
ERROR 1052 (23000): Column 'ID' in field list is ambiguous
Oh nee! Een fout in onze eerste query! Ja, en het is een pruim. Zie je, de query heeft inderdaad de juiste kolommen, maar sommige bestaan in beide tabellen, dus de database raakt in de war over welke kolom we nu eigenlijk bedoelen en waar. Er zijn twee oplossingen om dit op te lossen. De eerste is lekker simpel, we kunnen tableName.columnName
gebruiken om de database precies te vertellen wat we bedoelen, zoals dit:
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)
De andere wordt waarschijnlijk vaker gebruikt en heet table aliasing
. De tabellen in dit voorbeeld hebben mooie korte eenvoudige namen, maar iets als KPI_DAILY_SALES_BY_DEPARTMENT
uittypen zou waarschijnlijk snel oud worden, dus een eenvoudige manier is om de tabel een bijnaam te geven zoals dit:
select
a.ID,
b.model
from
cars a
join models b
on a.model=b.ID
Nu, terug naar het verzoek. Zoals je ziet hebben we de informatie die we nodig hebben, maar we hebben ook informatie waar'niet om gevraagd is, dus moeten we een where clausule in het statement opnemen om alleen de Sportwagens te krijgen waar om gevraagd is. Aangezien ik de voorkeur geef aan de tabel alias-methode boven het steeds opnieuw gebruiken van de tabelnamen, zal ik het daar vanaf dit punt bij houden.
Het is duidelijk dat we een where clausule aan onze query moeten toevoegen. We kunnen Sportwagens identificeren door ID=1
of model='Sports'
. Omdat de ID geïndexeerd is en de primaire sleutel (en het is toevallig minder typerend), laten we dat in onze query gebruiken.
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! De baas is blij. Natuurlijk, omdat hij een baas is en nooit blij is met wat hij vraagt, bekijkt hij de informatie, en zegt dan ik wil de kleuren ook.
Okay, dus we hebben een groot deel van onze query al geschreven, maar we moeten nog een derde tabel gebruiken en dat zijn de kleuren. Nu, onze hoofd informatietabel auto's
slaat de autokleur ID op en dit linkt terug naar de kleuren ID kolom. Dus, op een vergelijkbare manier als in het origineel, kunnen we een derde tabel toevoegen:
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)
Verdorie, hoewel de tabel correct werd samengevoegd en de gerelateerde kolommen werden gelinkt, vergaten we de eigenlijke informatie uit de nieuwe tabel die we net gelinkt hebben, binnen te halen.
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)
Goed, dat'is de baas even van onze rug. Nu, om iets meer in detail uit te leggen. Zoals je kunt zien, koppelt de from
clausule in ons statement onze hoofdtabel (ik gebruik vaak een tabel die informatie bevat in plaats van een lookup of dimensie tabel. De query zou net zo goed werken met de tabellen omgewisseld, maar zou minder logisch zijn als we over een paar maanden terugkomen op deze query om hem te lezen, dus is het vaak het beste om te proberen een query te schrijven die mooi en gemakkelijk te begrijpen is - zet hem intuïtief neer, gebruik mooie inspringingen, zodat alles zo duidelijk mogelijk is. Als u anderen les gaat geven, probeer dan deze kenmerken in hun query's te leggen - vooral als u ze gaat troubleshooten.
Het is heel goed mogelijk om steeds meer tabellen op deze manier aan elkaar te koppelen.
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
Hoewel ik vergeten ben om een tabel op te nemen waarbij we meer dan één kolom in het join
statement zouden willen samenvoegen, volgt hier een voorbeeld. Als de tabel models
merkspecifieke modellen had en daarom ook een kolom brand
had die op het veld ID
terug linkte naar de tabel brands
, dan zou dat als volgt kunnen:
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
U ziet, de query hierboven koppelt niet alleen de samengevoegde tabellen aan de hoofdtabel auto's
, maar specificeert ook samenvoegingen tussen de reeds samengevoegde tabellen. Als dit niet is gedaan, wordt het resultaat een cartesian join genoemd - dat is dba spreek voor slecht. Een cartesian join is er een waarbij rijen worden geretourneerd omdat de informatie de database niet vertelt hoe de resultaten moeten worden beperkt, zodat de query alle rijen retourneert die aan de criteria voldoen.
Dus, om een voorbeeld te geven van een cartesiaanse join, laten we de volgende query uitvoeren:
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)
Goeie god, dat's lelijk. Maar wat de database betreft, is het precies wat er gevraagd werd. In de query vroegen we om de ID
uit cars
en het model
uit models
. Echter, omdat we niet hebben aangegeven hoe we de tabellen moeten samenvoegen, heeft de database elke rij uit de eerste tabel gematched met elke rij uit de tweede tabel.
Okay, dus de baas is terug, en hij wil weer meer informatie. Ik wil dezelfde lijst, maar zet er ook 4WDs in.
Dit geeft ons echter een goed excuus om te kijken naar twee verschillende manieren om dit te bereiken. We kunnen nog een voorwaarde aan de waar-clausule toevoegen, zoals deze:
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
Hoewel het bovenstaande perfect zal werken, laten we het anders bekijken, dit is een goed excuus om te laten zien hoe een union
query zal werken.
We weten dat het volgende alle Sportwagens zal teruggeven:
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
En het volgende zou alle 4WDs teruggeven:
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
Dus door er een union all
clausule tussen te zetten, worden de resultaten van de tweede query toegevoegd aan de resultaten van de eerste query.
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)
Zoals u kunt zien, worden de resultaten van de eerste query eerst geretourneerd, gevolgd door de resultaten van de tweede query.
In dit voorbeeld zou het natuurlijk veel eenvoudiger zijn geweest om gewoon de eerste query te gebruiken, maar union
queries kunnen geweldig zijn voor specifieke gevallen. Ze zijn een geweldige manier om specifieke resultaten terug te geven van tabellen uit tabellen die niet gemakkelijk samen te voegen zijn - of voor die kwestie volledig ongerelateerde tabellen. Er zijn echter een paar regels te volgen.
union
en union all
. Een union
query zal duplicaten verwijderen, terwijl een union all
dat niet zal doen. Dit betekent dat er een kleine performance hit is wanneer union
gebruikt wordt boven union all
, maar de resultaten kunnen het waard zijn - ik zal hier echter niet speculeren over dat soort dingen.
Op deze nota, is het misschien de moeite waard om hier enkele aanvullende opmerkingen te maken.order by
gebruiken, maar je kunt'niet meer de alias gebruiken. In de query hierboven zou het toevoegen van een order by a.ID
een fout opleveren - wat de resultaten betreft heet de kolom ID
en niet a.ID
- ook al is in beide queries dezelfde alias gebruikt.order by
statement hebben, en het moet het laatste statement zijn.
Voor de volgende voorbeelden, voeg ik een paar extra rijen toe aan onze tabellen.
Ik heb Holden
toegevoegd aan de merken tabel.
Ik heb ook een rij toegevoegd aan cars
die de color
waarde van 12
heeft - die geen referentie heeft in de kleuren tabel.
Oke, de baas is weer terug, en blaft verzoeken uit - *Ik wil een telling van elk merk dat we voeren en het aantal autos erin!
- Typisch, we komen net bij een interessant deel van onze discussie en de baas wil meer werk.
Het eerste wat we moeten doen is een complete lijst van mogelijke merken krijgen.select
a.brand
from
brands a
+--------+
| brand |
+--------+
| Ford |
| Toyota |
| Nissan |
| Smart |
| BMW |
| Holden |
+--------+
6 rows in set (0.00 sec)
Als we dit verbinden met onze auto tabel krijgen we het volgende resultaat:
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)
Wat natuurlijk een probleem is - we zien geen enkele vermelding van het mooie merk Holden
dat ik heb toegevoegd.
Dit komt omdat een join zoekt naar overeenkomende rijen in beide tabellen. Omdat er geen gegevens in auto's zijn die van het type Holden
zijn, worden ze niet teruggegeven. Dit is waar we een outer
join kunnen gebruiken. Deze zal alle resultaten uit de ene tabel teruggeven, ongeacht of ze in de andere tabel overeenkomen of niet:
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)
Nu we dat hebben, kunnen we een mooie aggregate functie toevoegen om een telling te krijgen en de baas even van onze rug te krijgen.
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)
En daarmee, weg is de baas.
Nu, om dit in wat meer detail uit te leggen, outer joins kunnen van het left
of right
type zijn. De Left of Right definieert welke tabel volledig wordt meegenomen. Een left outer join
neemt alle rijen van de tabel links op, terwijl (je raadt het al) een right outer join
alle resultaten van de tabel rechts in de resultaten opneemt.
Sommige databases staan een full outer join
toe die resultaten (al dan niet gematched) van beide tabellen terugbrengt, maar dit wordt niet in alle databases ondersteund.
Ik denk dat je je op dit moment afvraagt of je join types in een query kunt samenvoegen - en het antwoord is ja, dat kan absoluut.
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)
Dus, waarom is dat niet het verwachte resultaat? Dat komt omdat, hoewel we de outer join van auto's naar merken hebben geselecteerd, het niet was gespecificeerd in de join naar kleuren - dus die specifieke join zal alleen resultaten terugbrengen die in beide tabellen overeenkomen. Hier is de query die zou werken om de resultaten te krijgen die we verwachtten:
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)
Zoals we kunnen zien, hebben we twee outer joins in de query en de resultaten komen door zoals verwacht.
Nu, hoe zit het met die andere types van joins vraag je? Hoe zit het met intersecties?
Nou, niet alle databases ondersteunen de intersection
, maar vrijwel alle databases staan je toe om een intersection te maken door middel van een join (of op zijn minst een goed gestructureerd where statement).
Een intersectie is een type join dat enigszins lijkt op een union
zoals hierboven beschreven - maar het verschil is dat het alleen rijen data retourneert die identiek zijn (en dan bedoel ik ook echt identiek) tussen de verschillende individuele queries die door de union zijn samengevoegd. Alleen rijen die in elk opzicht identiek zijn worden geretourneerd.
Een eenvoudig voorbeeld zou als volgt zijn:
select
*
from
colors
where
ID>2
intersect
select
*
from
colors
where
id<4
Terwijl een normale union
query alle rijen van de tabel zou retourneren (de eerste query retourneert alles boven ID>2
en de tweede alles met ID<4
) wat zou resulteren in een volledige set, zou een intersect query alleen de rij retourneren die overeenkomt met id=3
omdat het aan beide criteria voldoet.
Nu, als uw database geen intersect
query ondersteunt, kan het bovenstaande gemakkelijk worden bereikt met de volgende query:
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)
Als u een intersectie over twee verschillende tabellen wilt uitvoeren met een database die geen inherente intersectie-query ondersteunt, moet u een join maken op elke kolom van de tabellen.
Okay, nu is de baas weer binnengevallen - Ik wil een lijst van al onze auto's met het merk en een totaal van hoeveel we er van dat merk hebben! Dit is een mooie gelegenheid om de volgende truc in onze zak met SQL goodies te gebruiken - de subquery. Als je niet bekend bent met de term, een subquery is een query die binnen een andere query loopt. Er zijn veel verschillende manieren om ze te gebruiken. Voor ons verzoek, laten we eerst een eenvoudige query samenstellen die elke auto en het merk opsomt:
select
a.ID,
b.brand
from
cars a
join brands b
on a.brand=b.ID
Als we gewoon een lijst van auto's willen, gesorteerd op merk, dan kunnen we natuurlijk dit schrijven:
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 |
+--------+-----------+
We kunnen dus gewoon de count functie toevoegen aan onze oorspronkelijke query, toch?
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)
Jammer genoeg, nee, dat kunnen we niet doen. De reden is dat wanneer we de ID van de auto toevoegen (kolom a.ID), we deze moeten toevoegen aan de groep door - dus nu, wanneer de telfunctie werkt, wordt er slechts één ID per ID gematcht.
Dit is waar we echter een subquery kunnen gebruiken - in feite kunnen we twee totaal verschillende soorten subquery's doen die dezelfde resultaten zullen opleveren die we hiervoor nodig hebben. De eerste is om de subquery simpelweg in de select
clausule te zetten. Dit betekent dat elke keer als we een rij met data krijgen, de subquery een kolom met data ophaalt en deze in onze rij met data plaatst.
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)
En bam!, dit zou genoeg zijn. Maar zoals je ziet, moet deze sub query voor elke rij data die we terugsturen, uitgevoerd worden. Zelfs in dit kleine voorbeeld, hebben we slechts vijf verschillende merken van auto's, maar de sub query liep elf keer als we elf rijen van gegevens die we terug te keren. Dus, in dit geval, lijkt het niet de meest efficiënte manier om code te schrijven. Voor een andere aanpak, laten we een subquery uitvoeren en doen alsof het een tabel is:
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)
Okay, dus we hebben dezelfde resultaten (iets anders gerangschikt - het lijkt erop dat de database resultaten wilde teruggeven gerangschikt op de eerste kolom die we deze keer kozen) - maar dezelfde juiste getallen.
Dus, wat's het verschil tussen de twee - en wanneer moeten we elk type subquery gebruiken? Laten we eerst zorgen dat we begrijpen hoe die tweede query werkt. We selecteerden twee tabellen in de van
clausule van onze query, en schreven toen een query en vertelden de database dat het in feite een tabel was in plaats daarvan - waar de database perfect blij mee is. Er kunnen enkele voordelen zitten aan het gebruik van deze methode (en ook enkele beperkingen). De belangrijkste is dat deze subquery eenmalig werd uitgevoerd. Als onze database een groot volume aan gegevens bevatte, zou er een enorme verbetering kunnen zijn ten opzichte van de eerste methode. Maar omdat we dit als een tabel gebruiken, moeten we extra gegevensrijen inbrengen - zodat ze daadwerkelijk kunnen worden samengevoegd met onze gegevensrijen. We moeten er ook zeker van zijn dat er voldoende rijen gegevens zijn als we een eenvoudige join gaan gebruiken zoals in de query hierboven. Als je je herinnert, zal de join alleen rijen terughalen die overeenkomende gegevens hebben aan beide kanten van de join. Als we niet voorzichtig zijn, kan dit resulteren in geldige gegevens die niet terugkomen van onze auto tabel als er geen overeenkomende rij was in deze subquery.
Nu, terugkijkend naar de eerste subquery, zijn er ook enkele beperkingen. omdat we gegevens terughalen in een enkele rij, kunnen we alleEN één rij gegevens terughalen. Subqueries gebruikt in de select
clausule van een query gebruiken heel vaak alleen een aggregate functie zoals sum
, count
, max
of een andere soortgelijke aggregate functie. Dat hoeft niet, maar zo worden ze vaak geschreven.
Dus, voordat we verder gaan, laten we even kijken waar we nog meer een subquery kunnen gebruiken. We kunnen het gebruiken in de where
clausule - nu, dit voorbeeld is een beetje gekunsteld omdat in onze database, er betere manieren zijn om de volgende gegevens te krijgen, maar aangezien het slechts een voorbeeld is, laten we eens kijken:
select
ID,
brand
from
brands
where
brand like '%o%'
+----+--------+
| ID | brand |
+----+--------+
| 1 | Ford |
| 2 | Toyota |
| 6 | Holden |
+----+--------+
3 rows in set (0.00 sec)
Dit geeft ons een lijst van merk ID's en Merknamen (de tweede kolom is alleen toegevoegd om ons de merken te tonen) die de letter o
in de naam bevatten.
Nu, we zouden de resultaten van deze query kunnen gebruiken in een where clause dit:
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)
Zoals u kunt zien, hoewel de subquery de drie merk-ID's teruggaf, had onze autotabel slechts gegevens voor twee van hen. In dit geval, voor meer detail, werkt de subquery alsof we de volgende code schreven:
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)
Nogmaals, je kunt zien hoe een subquery vs handmatige invoer de volgorde van de rijen heeft veranderd bij het terugkeren uit de database. Nu we het toch over subquery's hebben, laten we eens kijken wat we nog meer kunnen doen met een subquery:
select
regel, een paar in de from
regel en nog een paar in de where
regel - vergeet alleen niet dat elke subquery die u toevoegt uw query complexer maakt en het waarschijnlijk langer duurt om deze uit te voeren.
Als u efficiënte code moet schrijven, kan het nuttig zijn om de query op een aantal manieren te schrijven en te zien (door de query te timen of door een explain plan te gebruiken) welke de optimale query is om uw resultaten te krijgen. De eerste manier die werkt is niet altijd de beste manier.Ik dacht dat ik wat extra's zou toevoegen, voor tips en trucs die naar boven zijn gekomen.
Een vraag die ik vaak zie, is Hoe krijg ik niet-matchende rijen uit twee tabellen en ik zie het antwoord meestal geaccepteerd als iets als het volgende (gebaseerd op onze auto's en merken tabel - die Holden als merk heeft, maar niet voorkomt in de auto's tabel):
select
a.ID,
a.brand
from
brands a
where
a.ID not in(select brand from cars)
En ja het zal werken.
+----+--------+
| ID | brand |
+----+--------+
| 6 | Holden |
+----+--------+
1 row in set (0.00 sec)
Het is echter niet efficiënt in sommige databases. Hier is een link naar een Stack Overflow vraag die er naar vraagt, en hier is een uitstekend diepgaand artikel als je er dieper op in wilt gaan.
Het korte antwoord is, dat als de optimiser er niet efficiënt mee omgaat, het veel beter kan zijn om een query als de volgende te gebruiken om niet gematchte rijen te krijgen:
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, nog een oldie but goodie - de oude Je kunt geen doeltabel 'merken' specificeren voor update in FROM-clausule.
MySQL zal niet toestaan dat u een update...
query uitvoert met een subselectie op dezelfde tabel. Nu, zou je kunnen denken, waarom niet gewoon in de where clause? Maar wat als je alleen de rij met de max()
datum wilt updaten tussen een heleboel andere rijen? Dat kun je niet doen in een where clause.
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
Dus, we kunnen dat niet doen? Nou, niet precies. Er is een stiekeme workaround waar verrassend veel gebruikers niet van weten - hoewel er wel wat hackerij bij komt kijken waar je op moet letten.
U kunt de subquery binnen een andere subquery plakken, die genoeg van een kloof tussen de twee queries zet zodat het zal werken. Merk echter op dat het het veiligst is om de query in een transactie te plakken - dit voorkomt dat er andere wijzigingen in de tabellen worden gemaakt terwijl de query loopt.
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