Для новичка в SQL, объединения могут быть пугающими и несколько расстраивающими. Даже если вы думаете, что у вас есть запрос, неожиданный результат заставляет вас бросить руки, отдаваясь богам SQL. В этом уроке мы собираемся опираться на отличный совет по работе с MySQL и недавно опубликованным INNER JOIN и раскрыть реальную силу оператора SELECT.
Вступление
Вспомните свои самые ранние школьные дни, когда вы учились группировать предметы, изучали базовые математические навыки или даже писали. Если ваши школьные дни были чем-то похожими на мои, было множество упражнений, на которых были изображения вещей, и вы сгруппировали эти вещи вместе. Это могли быть утки и коровы, где вам приходилось считать каждого. Это мог быть поиск слов, когда вам выдавали блок, казалось бы, случайных букв, из которых нужно было найти список слов. Возможно, вы помните игру «Какой из них не похож на другой».
Эти базовые игры, которые все изучают в самые ранние школьные годы, — это те же самые навыки, которые вы используете, когда работаете с SELECT. У нас просто другой синтаксис, который мы должны использовать. Каждый раз, когда мы используем команду SELECT, мы просто группируем набор данных. Используя JOIN, мы просто группируем несколько наборов данных. Мы находим уток и коров, которые похожи или различаются, и помещаем их в массив, с которым мы можем работать на нашем логическом уровне. Это синтаксис, который сбивает с толку, но то, что мы делаем, это то же самое, что мы делали в самые ранние школьные годы.
«Каждый раз, когда мы используем команду SELECT, мы просто группируем набор данных. Используя JOIN, мы просто группируем несколько наборов данных. Мы находим уток и коров, которые похожи или различаются, и откладываем их в массив, с которым мы можем работать в нашем логическом слое. Это синтаксис, который смущает … «
В этом уроке я расскажу о нескольких вещах, но ни одно из них не является чрезвычайно трудным для применения на практике. Я использую MySQL в качестве своей базы данных, но по большей части я буду использовать простой синтаксис SELECT, который, по крайней мере, должен быть знаком независимо от вашего вида SQL. Для моего инструмента запросов я использую Sequel Pro для Mac, который является относительно быстрым и описательным для моих целей сегодня. Я использую очень простую структуру таблиц типов животных, ферм и диких животных. Для тех, кто играет дома, структура стола выглядит примерно так:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
—
— Table structure for table `animal_types`
—
CREATE TABLE `animal_types` (
`animal_type_id` int(11) NOT NULL AUTO_INCREMENT,
`animal_type_description` varchar(255) NOT NULL,
PRIMARY KEY (`animal_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
—
— Dumping data for table `animal_types`
—
INSERT INTO `animal_types` VALUES(1, ‘duck’);
INSERT INTO `animal_types` VALUES(2, ‘cow’);
INSERT INTO `animal_types` VALUES(3, ‘goose’);
INSERT INTO `animal_types` VALUES(4, ‘cat’);
INSERT INTO `animal_types` VALUES(5, ‘sheep’);
INSERT INTO `animal_types` VALUES(6, ‘horse’);
—
— Table structure for table `farm`
—
CREATE TABLE `farm` (
`animal_id` int(11) NOT NULL AUTO_INCREMENT,
`animal_type` int(11) NOT NULL,
`animal_description` varchar(255) NOT NULL,
`animal_name` varchar(255) NOT NULL,
PRIMARY KEY (`animal_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
—
— Dumping data for table `farm`
—
INSERT INTO `farm` VALUES(1, 2, ‘spotted’, ‘Mal’);
INSERT INTO `farm` VALUES(2, 4, ‘spotted’, ‘Zoe’);
INSERT INTO `farm` VALUES(3, 1, ‘solid color’, ‘Wash’);
INSERT INTO `farm` VALUES(4, 2, ‘solid color’, ‘Inara’);
—
— Table structure for table `wild`
—
CREATE TABLE `wild` (
`animal_id` int(11) NOT NULL AUTO_INCREMENT,
`animal_type` int(11) NOT NULL,
`animal_description` varchar(255) NOT NULL,
`animal_name` varchar(255) NOT NULL,
PRIMARY KEY (`animal_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
—
— Dumping data for table `wild`
—
INSERT INTO `wild` (`animal_id`, `animal_type`, `animal_description`, `animal_name`) VALUES(1, 2, ‘spotted’, ‘Jayne’);
INSERT INTO `wild` (`animal_id`, `animal_type`, `animal_description`, `animal_name`) VALUES(2, 6, ‘solid color’, ‘Kaylee’);
INSERT INTO `wild` (`animal_id`, `animal_type`, `animal_description`, `animal_name`) VALUES(3, 1, ‘spotted’, ‘Simon’);
INSERT INTO `wild` (`animal_id`, `animal_type`, `animal_description`, `animal_name`) VALUES(4, 3, ‘solid color’, ‘River’);
INSERT INTO `wild` (`animal_id`, `animal_type`, `animal_description`, `animal_name`) VALUES(5, 5, ‘solid color’, ‘Sheppard’);
|
Назад к основам : отказ от привычки
Так как это синтаксис, а не обязательно сложные концепции, мы должны сделать пару шагов назад и вспомнить некоторые основы. Существует множество различных комбинаций клавиш, которые вы можете использовать при написании SELECT, но много раз эти комбинации вызовут проблемы в будущем. Я склонен выписывать свои запросы, чтобы у меня не было двусмысленных имен столбцов, и я точно вижу, что я ожидаю получить.
Давайте сначала начнем с моей таблицы animal_types, в которую введено несколько типов животных. Если бы я хотел выделить все в этой таблице, я мог бы написать свой выбор следующим образом:
|
1
2
|
SELECT *
FROM animal_types
|
Который дал бы мне набор данных:
Проблема с использованием селектора «*» заключается в том, что через год я, вероятно, не вспомню, что я ожидаю от этого SELECT. Кроме того, когда я присоединяюсь к таблицам, у меня, вероятно, будут неоднозначные имена столбцов, а это означает, что существует несколько столбцов с одинаковыми именами, поэтому в SQL нет способа выделить результаты отдельно. Вместо этого, давайте будем конкретны, это займет немного больше времени, но исправит некоторые из этих проблем с помощью ярлыков:
|
1
2
3
4
|
SELECT animal_type_id,
animal_type_description
FROM animal_types
|
Что возвращает точные данные обратно, но я точно вижу, что я должен получить обратно:
Несмотря на то, что мы явно называем имена наших столбцов, мы можем пойти немного дальше, и это явно говорит о том, что эти столбцы принадлежат этой таблице. Это еще один ярлык, который мы используем постоянно, который добавляет немного больше времени на написание запросов, но в конечном итоге экономит время отладки позже, когда вы присоединяетесь к другим таблицам. Вместо нашего последнего запроса, давайте вместо этого явно префикс наших столбцов с префиксом таблицы:
|
1
2
3
4
|
SELECT att.animal_type_id,
att.animal_type_description
FROM animal_types att
|
Мы добавили префикс в таблицу animal_types att, который сообщает SQL, когда вы видите префикс u, понимаете, что он принадлежит таблице с именем animal_types. Наш набор данных выглядит точно так же снова:
Давайте также скажем SQL, как назвать наши столбцы, когда он дает нам наши результаты. Во многих случаях, когда вы работаете с JOIN или другими сложными запросами, два столбца будут иметь одинаковые имена, что затруднит их различение на уровне логики. Мне нравится явно указывать SQL, чтобы я доставлял имена моих столбцов желаемым образом, а не обязательно так, как они названы. Для этого мы будем использовать ключевое слово AS:
|
1
2
3
4
|
SELECT att.animal_type_id AS animal_type_id,
att.animal_type_description AS animal_type_description
FROM animal_types att
|
Что снова дает нам точно такой же набор данных, но мы снова были еще более явными в нашем запросе:
Есть еще один способ убедиться, что я говорю SQL, чтобы ВЫБРАТЬ именно то, что я хочу, и откуда я хочу, и это добавить префикс базы данных в мою таблицу. Языки программирования и различные инструменты позволяют очень легко подразумевать имя базы данных, но бывают случаи, когда вы можете вызывать другую базу данных в запросе. Когда вы добавляете префикс базы данных, вы исключаете возможность работы с одной базой данных из смеси.
|
1
2
3
4
|
SELECT att.animal_type_id AS animal_type_id,
att.animal_type_description AS animal_type_description
FROM tutorial.animal_types att
|
Опять же, точно такой же набор данных:
За исключением того, что сейчас мы говорим SQL явно искать в базе данных «tutorial» таблицу «animal_types». Опять же, это удобно, когда мы извлекаем информацию из нескольких баз данных в один выбор.
Суб-выбор : множественный выбор, связанный вместе
MySQL 4.1 добавил возможность использовать Sub-Selects. Предостережение о суб-выборе, прежде чем мы углубимся в них, используйте их с умом. План выполнения суб-выбора может сильно отличаться от того, что вы себе представляете, и поэтому, когда вы считаете, что используете INDEX, вы действительно работаете с полным сканированием таблицы. По моему опыту, вы можете оптимизировать ваши субвыборы так, чтобы они были такими же дружественными к производительности, как и естественное объединение, но когда вы имеете дело с оптимизацией, вам нужно обратить пристальное внимание на результаты EXPLAIN по вашему запросу. Чтобы получить больше информации,
взгляните на недавний учебник по оптимизации MySQL, который дает вам представление о планах выполнения запросов.
Что Sub-Select в основном выполняет инструкцию SELECT внутри инструкции SELECT. Давайте посмотрим на очень распространенное использование:
|
1
2
3
4
5
6
7
8
|
SELECT f.animal_id AS animal_id,
f.animal_type AS animal_type,
f.animal_description AS animal_description,
f.animal_name AS animal_name
FROM tutorial.farm f
WHERE f.animal_type IN (SELECT att.animal_type_id FROM tutorial.animal_types att where att.animal_type_id = 2)
|
Что дает нам набор данных:
В этом случае мы говорим, чтобы вернуть всех сельскохозяйственных животных, которые имеют животного типа 2. Давайте немного углубимся в это, и вместо этого посмотрим на животных типа 1 или 2.
|
1
2
3
4
5
6
7
8
|
SELECT f.animal_id AS animal_id,
f.animal_type AS animal_type,
f.animal_description AS animal_description,
f.animal_name AS animal_name
FROM tutorial.farm f
WHERE f.animal_type IN (SELECT att.animal_type_id FROM tutorial.animal_types att where att.animal_type_id = 2 OR att.animal_type_id = 1)
|
Что теперь дает нам результат:
Мы используем ключевое слово IN, чтобы сказать SQL, чтобы посмотреть на результаты нашего Sub-Select. В этом случае мы могли бы легко переписать это, чтобы не использовать Sub-Select и получить те же результаты:
|
1
2
3
4
5
6
7
8
|
SELECT f.animal_id AS animal_id,
f.animal_type AS animal_type,
f.animal_description AS animal_description,
f.animal_name AS animal_name
FROM tutorial.farm f
WHERE f.animal_type IN (1, 2)
|
Точные те же результаты, что и раньше:
Тем не менее, когда мы изменяем наш Sub-Select на что-то вроде:
|
1
2
3
4
5
6
7
8
|
SELECT f.animal_id AS animal_id,
f.animal_type AS animal_type,
f.animal_description AS animal_description,
f.animal_name AS animal_name
FROM tutorial.farm f
WHERE f.animal_type IN (SELECT att.animal_type_id FROM tutorial.animal_types att where att.animal_type_description LIKE «%duck%» OR att.animal_type_description LIKE «%cow%»)
|
Мы начинаем видеть силу:
Мы можем использовать дополнительный выбор для поиска и предоставления нам набора данных, чтобы ограничить результаты нашей фермы. Мы можем пойти немного дальше, также используя Sub-Select в нашем запросе SELECT. Скажем, например, что мы хотим получить animal_type_description в качестве дополнительного выбора в нашем основном запросе:
|
1
2
3
4
5
6
7
8
9
|
SELECT f.animal_id AS animal_id,
f.animal_type AS animal_type,
f.animal_description AS animal_description,
f.animal_name AS animal_name,
(SELECT att.animal_type_description FROM tutorial.animal_types att where att.animal_type_id = f.animal_type) AS description
FROM tutorial.farm f
WHERE f.animal_type IN (SELECT att.animal_type_id FROM tutorial.animal_types att where att.animal_type_description LIKE «%duck%» OR att.animal_type_description LIKE «%cow%»)
|
Который возвращает:
В этом запросе мы помещаем условие в наш Sub-Select, чтобы оно возвращало animal_type_description, когда оно соответствует f.animal_id. Это условие будет длиться в течение этого прохода и будет сброшено при следующем наборе данных. По сути, мы объединили наши таблицы, просто используя Sub-Select. Опять же, перечитайте предупреждение с начала этого раздела о производительности, но, как вы можете видеть, вы можете использовать дополнительный выбор, чтобы быстро присоединиться к таблице на основе условия, выполненного в дополнительном выборе.
INNER JOINS : Эксклюзивный клуб
С Sub-Selects вне пути, давайте перейдем к INNER JOIN. Я всегда видел соединения, обученные с красивыми кругами, которые показывают, где выбирается JOIN. Я предпочел бы думать о соединениях более естественным способом обучения, который я изучал в начальной школе группирования изображений. Я всегда представляю, что я хочу получить от своего запроса, прежде чем написать его. Когда я визуализирую эксклюзивные отношения, тогда я использую INNER JOIN.
Например, предположим, что я хочу видеть описание типа животных для моих сельскохозяйственных животных, но только когда существует идентификатор типа животного. Я мог бы написать ВНУТРЕННЕЕ СОЕДИНЕНИЕ, которое возвратило бы этот результат
|
01
02
03
04
05
06
07
08
09
10
|
SELECT f.animal_id AS farm_animal_id,
f.animal_type AS farm_animal_type,
f.animal_description AS farm_animal_description,
f.animal_name AS farm_animal_name,
att.animal_type_description AS description
FROM tutorial.farm f
INNER JOIN tutorial.animal_types att
ON f.animal_type = att.animal_type_id;
|
Который возвращает:
Потрясающие. Тем не менее, этот пример не показывает исключительность, которую мы получаем от нашего внутреннего соединения. Давайте сделаем то, что происходит каждый день с нашей структурой данных, и удалим запись из нашей таблицы поиска определения типов животных. Я собираюсь удалить описание «кошка»:
|
1
2
|
DELETE FROM animal_types
WHERE animal_type_description = «cat»
|
И теперь я хочу повторить мой оригинальный запрос:
|
01
02
03
04
05
06
07
08
09
10
|
SELECT f.animal_id AS farm_animal_id,
f.animal_type AS farm_animal_type,
f.animal_description AS farm_animal_description,
f.animal_name AS farm_animal_name,
att.animal_type_description AS description
FROM tutorial.farm f
INNER JOIN tutorial.animal_types att
ON f.animal_type = att.animal_type_id;
|
Который сейчас возвращает только три результата:
Все, что я сделал, это удалил атрибут из моей справочной таблицы, и «Zoe» все еще находится в моей таблице сельскохозяйственных животных:
|
1
2
|
SELECT f.animal_name as name
FROM tutorial.farm f
|
Но так как в таблице типов животных нет совпадений, я не получу результат от своих сельскохозяйственных животных.
Давайте поиграем в игру на соответствие с нашей таблицей диких животных, чтобы проверить концепцию немного подробнее:
|
01
02
03
04
05
06
07
08
09
10
|
SELECT w.animal_id AS wild_animal_id,
w.animal_type AS wild_animal_type,
w.animal_description AS wild_animal_description,
w.animal_name AS wild_animal_name,
att.animal_type_description AS description
FROM tutorial.wild w
INNER JOIN tutorial.animal_types att
ON w.animal_type = att.animal_type_id
|
Что дает нам результат:
Опять же, всегда помните, что INNER JOIN будет возвращать данные из двух таблиц, в которых есть совпадение в обеих таблицах. Когда вы сравниваете две картинки и делаете одну картинку из двух, вы будете только рисовать спички.
НАРУЖНЫЕ СОЕДИНЕНИЯ : Описательный клуб
ВНУТРЕННИЕ СОЕДИНЕНИЯ просты в использовании, но могут быть ограничивающими. Существует два типа объединений с MySQL, которые не имеют правил эксклюзивности, и это LEFT OUTER JOIN и RIGHT OUTER JOIN. Другие базы данных SQL также имеют FULL OUTER JOIN, который можно моделировать в MySQL с помощью UNION. Я склонен использовать исключительно ЛЕВЫЕ ВНЕШНИЕ СОЕДИНЕНИЯ, так как я считаю свои ВНЕШНИЕ СОЕДИНЕНИЯ добавлением описания к моим операторам SELECT.
Когда вы используете LEFT OUTER JOIN, вы, по сути, говорите, возьмите мне все из таблицы LEFT и присоедините все, что соответствует условию из таблицы RIGHT, не только:
|
01
02
03
04
05
06
07
08
09
10
|
SELECT f.animal_id AS farm_animal_id,
f.animal_type AS farm_animal_type,
f.animal_description AS farm_animal_description,
f.animal_name AS farm_animal_name,
att.animal_type_description AS description
FROM tutorial.farm f
LEFT OUTER JOIN tutorial.animal_types att
ON f.animal_type = att.animal_type_id
|
Дает нам результат:
Наша таблица ферм — это наша таблица LEFT, так как именно отсюда происходит мой естественный отбор, и она всегда будет возвращать результат из таблицы LEFT, даже если из нашей таблицы RIGHT ничего не совпадает. Давайте посмотрим на пример ПРАВИЛЬНОГО НАРУЖНОГО СОЕДИНЕНИЯ, использующего тот же базовый SELECT:
|
01
02
03
04
05
06
07
08
09
10
|
SELECT f.animal_id AS farm_animal_id,
f.animal_type AS farm_animal_type,
f.animal_description AS farm_animal_description,
f.animal_name AS farm_animal_name,
att.animal_type_description AS description
FROM tutorial.farm f
RIGHT OUTER JOIN tutorial.animal_types att
ON f.animal_type = att.animal_type_id
|
Что дает нам результат:
Который, на мой взгляд, не так полезен, как набор данных, потому что в итоге я получу пустые записи в моем левом наборе данных. Когда я пишу запрос, я, как правило, хочу знать все в моем LEFT без этих неприятных записей NULL, с которыми приходится иметь дело. ВНЕШНЕЕ НАРУЖНОЕ СОЕДИНЕНИЕ действительно пригодится, когда мы объединяем несколько таблиц, чтобы опираться на информацию. Давайте добавим в таблицу новую справочную таблицу для описания того, является ли животное полосатым, пятнистым или твердым:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
|
—
— Table structure for table `animal_descriptions`
—
CREATE TABLE `animal_descriptions` (
`animal_description_id` int(11) NOT NULL AUTO_INCREMENT,
`animal_description` varchar(255) NOT NULL,
PRIMARY KEY (`animal_description_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
—
— Dumping data for table `animal_descriptions`
—
INSERT INTO `animal_descriptions` VALUES(1, ‘Spotted’);
INSERT INTO `animal_descriptions` VALUES(2, ‘Striped’);
INSERT INTO `animal_descriptions` VALUES(3, ‘Solid Color’);
|
И после того, как я изменил значения в своих таблицах фермы и подстановочных знаков на целые числа, чтобы они соответствовали моим идентификаторам описания, я могу сделать несколько OUTER-соединений в моем исходном запросе:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
|
SELECT f.animal_id AS farm_animal_id,
f.animal_type AS farm_animal_type,
d.animal_description AS farm_animal_description,
f.animal_name AS farm_animal_name,
att.animal_type_description AS description
FROM tutorial.farm f
LEFT OUTER JOIN tutorial.animal_types att
ON f.animal_type = att.animal_type_id
LEFT OUTER JOIN tutorial.animal_descriptions d
on f.animal_description = d.animal_description_id
|
Мы говорим, дайте мне все названия сельскохозяйственных животных, и пока вы здесь, дайте мне тип животного, а также описание животного, даже если в ПРАВО не может быть совпадения с сельскохозяйственным животным. столы:
Бам! У нас есть то, что мы после. Единственным ограничением для объединения является ваше воображение и производительность вашего сервера базы данных. Чем больше вы нормализуете, тем больше у вас будет необходимость добавлять неисключительное описание к вашим результатам.
Объединение JOINS : смешивать и сочетать
Есть моменты, когда вам нужна эксклюзивность в объединении, но вы хотите описание от другого объединения. Не существует закона (за исключением Исландии, некоторых районов Новой Шотландии и Восточного Кентукки), согласно которому нельзя использовать как ВНУТРЕННИЕ, так и ВНЕШНИЕ соединения в одном и том же SELECT. Давайте на минутку посмотрим на наших диких животных:
У нас есть одно животное в полоску. Давайте удалим чередование из нашей новой таблицы описания животных, а затем просто используем LEFT OUTER JOINs для имитации нашего последнего примера с дикой стороны:
|
1
|
DELETE FROM tutorial.animal_descriptions where animal_description_id = ‘1’
|
И выбрать из наших диких животных так же, как мы делали наших сельскохозяйственных животных:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
|
SELECT w.animal_id AS wild_animal_id,
w.animal_type AS wild_animal_type,
d.animal_description AS farm_animal_description,
w.animal_name AS wild_animal_name,
att.animal_type_description AS description
FROM tutorial.wild w
LEFT OUTER JOIN tutorial.animal_types att
ON w.animal_type = att.animal_type_id
LEFT OUTER JOIN tutorial.animal_descriptions d
on w.animal_description = d.animal_description_id
|
Что дает нам значение NULL, где раньше использовалось наше описание полосатого животного:
Это все хорошо, пока вы не поймете, что ваша структура данных больше не повреждена из-за этого значения NULL. Давайте объединим наше описание OUTER join с эксклюзивностью INNER JOIN в нашем описании:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
|
SELECT w.animal_id AS wild_animal_id,
w.animal_type AS wild_animal_type,
d.animal_description AS farm_animal_description,
w.animal_name AS wild_animal_name,
att.animal_type_description AS description
FROM tutorial.wild w
LEFT OUTER JOIN tutorial.animal_types att
ON w.animal_type = att.animal_type_id
INNER JOIN tutorial.animal_descriptions d
on w.animal_description = d.animal_description_id
|
Который избавляется от значения NULL, которое может быть причиной ошибки в вашем приложении из-за проблемы целостности данных, а также тишины и покоя на выходных:
Мы также можем самостоятельно присоединиться к столу, который время от времени пригодится. То, что мы делаем при объединении таблиц, это получение информации из table.a и объединение ее в table.b. SQL на самом деле не волнует, является ли table.b той же таблицей, что и table.a. Вот пример:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
|
SELECT w.animal_id AS wild_animal_id,
w.animal_type AS wild_animal_type,
d.animal_description AS farm_animal_description,
same.animal_name AS wild_animal_name_from_same_table,
att.animal_type_description AS description
FROM tutorial.wild w
LEFT OUTER JOIN tutorial.animal_types att
ON w.animal_type = att.animal_type_id
INNER JOIN tutorial.animal_descriptions d
on w.animal_description = d.animal_description_id
LEFT OUTER JOIN tutorial.wild same
ON w.animal_id = same.animal_id
|
В этом примере мы объединяем одну и ту же дикую таблицу с идентификатором животного, чтобы получить его имя:
Хотя в этом примере результат может быть не таким интересным, подумайте, сколько раз в ваших приложениях вы создавали несколько таблиц для выражения отношения родитель-потомок. Вы можете легко вырезать структуру из нескольких таблиц, присоединив таблицу к себе, чтобы изменить смысл отношений.
Союзы : другой способ присоединиться
Даже имея в своем распоряжении инструменты ВНУТРЕННЕГО, ВНЕШНЕГО, СУБЩЕННОГО ВЫБОРА, мы обнаруживаем, что нам нужны еще дополнительные инструменты для получения правильного набора данных в разы. Иногда нам нужно получить информацию о диких животных, а также о наших сельскохозяйственных животных. У нас есть три изображения уток и коров, которые нужно объединить в одну. Введите СОЮЗ.
UNION — это комбинация нескольких запросов, которые объединяют наши наборы данных в один. Давайте возьмем ситуацию, когда мы хотим описание всех животных, будь то фермы или дикие. Мы можем просто выразить это, используя UNION:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
SELECT f.animal_id AS animal_id,
f.animal_type AS animal_type,
d.animal_description AS animal_description,
f.animal_name AS animal_name,
‘Farm’ AS domain,
att.animal_type_description AS description
FROM tutorial.farm f
LEFT OUTER JOIN tutorial.animal_types att
ON f.animal_type = att.animal_type_id
LEFT OUTER JOIN tutorial.animal_descriptions d
ON f.animal_description = d.animal_description_id
UNION ALL
SELECT w.animal_id AS animal_id,
w.animal_type AS animal_type,
d.animal_description AS animal_description,
w.animal_name AS animal_name,
‘Wild’ AS domain,
att.animal_type_description AS description
FROM tutorial.wild w
LEFT OUTER JOIN tutorial.animal_types att
ON w.animal_type = att.animal_type_id
LEFT OUTER JOIN tutorial.animal_descriptions d
ON w.animal_description = d.animal_description_id
|
В этом UNION единственное, что я не объяснил, это явный столбец «домен». Я просто говорю, что дайте мне «Ферму» или «Дикий» в зависимости от того, когда вы бежите. Я также использую UNION ALL вместо UNION, так как мне нужны все результаты, а не только отдельные значения. Наш сложный запрос дает нам ожидаемый набор данных, на который мы надеялись:
Мы также можем использовать дополнительный выбор в нашем СОЮЗЕ:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SELECT f.animal_id AS animal_id,
f.animal_type AS animal_type,
d.animal_description AS animal_description,
f.animal_name AS animal_name,
‘Farm’ AS domain,
att.animal_type_description AS description
FROM tutorial.farm f
LEFT OUTER JOIN tutorial.animal_types att
ON f.animal_type = att.animal_type_id
LEFT OUTER JOIN tutorial.animal_descriptions d
ON f.animal_description = d.animal_description_id
UNION ALL
SELECT w.animal_id AS animal_id,
w.animal_type AS animal_type,
d.animal_description AS animal_description,
w.animal_name AS animal_name,
‘Wild’ AS domain,
att.animal_type_description AS description
FROM tutorial.wild w
LEFT OUTER JOIN tutorial.animal_types att
ON w.animal_type = att.animal_type_id
LEFT OUTER JOIN tutorial.animal_descriptions d
ON w.animal_description = d.animal_description_id
WHERE w.animal_id IN (SELECT animal_id FROM wild WHERE animal_id <= 3)
|
Говоря в основном, дайте мне объединение двух запросов, но дайте мне только результаты с дикой стороны, у которых идентификатор животного меньше или равен 3. Полученный набор данных такой же, как и ожидалось:
Союзы очень просты в использовании, но пара замечаний. Первая сторона UNION будет определять название столбца. Кроме того, обе стороны UNION должны иметь одинаковые номера столбцов. Вы не можете иметь один запрос с 4 столбцами данных, а другой запрос с 3.
Имитация полного внешнего соединения : практическое использование
Мы можем связать все, что мы узнали, имитируя FULL OUTER JOIN, который, как я говорил выше, MySQL не поддерживает, используя простой оператор UNION. Давайте использовать исходный запрос с начала урока:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
SELECT f.animal_id AS farm_animal_id,
f.animal_type AS farm_animal_type,
d.animal_description AS farm_animal_description,
f.animal_name AS farm_animal_name,
att.animal_type_description AS description
FROM tutorial.farm f
LEFT OUTER JOIN tutorial.animal_types att
ON f.animal_type = att.animal_type_id
LEFT OUTER JOIN tutorial.animal_descriptions d
on f.animal_description = d.animal_description_id
UNION
SELECT f.animal_id AS farm_animal_id,
f.animal_type AS farm_animal_type,
d.animal_description AS farm_animal_description,
f.animal_name AS farm_animal_name,
att.animal_type_description AS description
FROM tutorial.farm f
RIGHT OUTER JOIN tutorial.animal_types att
ON f.animal_type = att.animal_type_id
RIGHT OUTER JOIN tutorial.animal_descriptions d
on f.animal_description = d.animal_description_id
|
В этом случае я использую оператор UNION вместо оператора UNION ALL, поскольку мне просто нужны разные значения. Вот мой набор результатов:
Вывод
Даже несмотря на то, что мои пальцы набирали мозоли из-за количества набираемых текстов, чтобы объяснить сложные запросы, я действительно только поцарапал поверхность. Я приложил все усилия, чтобы объяснить объединения без одинаковых иллюстраций кругов, показывающих аффективные области. Эти иллюстрации работают, но я думаю, что если вы опираетесь на свой опыт начальной школы и думаете: «Какой из них не похож на другой», вы добавите немного больше контекста к идее объединения наборов данных и таблиц. Если вы визуализируете свои операторы SELECT, становится намного легче получить тот результат, который вы имеете в виду. Если я что-то пропустил, просто дайте мне знать в разделе комментариев.