Добро пожаловать в модуль 5 курса SQL Snow Day: праздничное приключение по основам Postgres с bit.io! В этом модуле вы изучите основы объединения таблиц данных с помощью SQL. Соединения позволяют получить доступ к данным из нескольких таблиц с помощью одного запроса.
В этом модуле вы:
- Узнайте, как использовать различные типы операторов
JOIN - Узнайте больше об использовании псевдонимов в SQL.
- Сравните левые и внутренние соединения, когда в левой таблице есть записи, отсутствующие в правой таблице.
⏱30 минут
SQL-соединения
В этом разделе мы будем работать с новой таблицей. Санта получил обновленную таблицу пингвинов к празднику 2022 года, включая их виды и острова. Добавьте таблицу в базу данных следующим образом:
CREATE TABLE penguin_presents_2022 (name text, species text, island text)
INSERT INTO penguin_presents_2022 (name, species, island) VALUES
('Flipperdoodle', 'Chinstrap', 'Biscoe'),
('Toboggan the Tuxedoed', 'Gentoo', 'Biscoe'),
('Tux the Festive', 'Adelie', 'Dream'),
('Iceberg the Icy', 'Chinstrap', 'Dream'),
('Aqua the Aquatic', 'Gentoo', 'Biscoe'),
('Waddle Waffles', 'Adelie', 'Dream'),
('Glam Glacier', 'Chinstrap', 'Dream'),
('Penguinina', 'Gentoo', 'Dream'),
('Chilly', 'Adelie', 'Torgersen'),
('Fluffy Flippers', 'Chinstrap', 'Biscoe'),
('Brr-illiant', 'Gentoo', 'Torgersen'),
('Snowy Shortbill', 'Adelie', 'Biscoe'),
('Frosty Feathers', 'Chinstrap', 'Biscoe'),
('Icicle', 'Gentoo', 'Torgersen'),
('Slippery Flippers', 'Adelie', 'Biscoe'),
('Arctic Waddler', 'Chinstrap', 'Biscoe'),
('Polar Bird', 'Gentoo', 'Torgersen'),
('Antar c. Tica', 'Adelie', 'Torgersen'),
('Ice Cream', 'Chinstrap', 'Torgersen'),
('Cool Waddlings', 'Gentoo', 'Dream'),
('Igloo', 'Adelie', 'Biscoe'),
('Dancer Proud-Webbed-Foot', 'Chinstrap', 'Biscoe'),
('Snowy Feathers', 'Gentoo', 'Torgersen'),
('Fluffy Tail', 'Adelie', 'Dream'),
('Icy Eyes', 'Chinstrap', 'Torgersen'),
('Cool Operator', 'Gentoo', 'Torgersen'),
('Arctic Bird', 'Adelie', 'Torgersen'),
('Snowbird', 'Chinstrap', 'Torgersen')
Теперь Санта хочет индивидуальный список подарков. Мы можем назначать дары на основе сопоставления видов и островов с дарами, определенными в таблице gifts. Но мы не хотим вручную ссылаться на таблицу gifts для заполнения таблицы penguin_presents_2022. Это заняло бы слишком много времени, и SQL может сделать это за нас с помощью команды JOIN.
Мы можем использовать INNER JOIN, чтобы соединить таблицу penguin_presents_2022 и таблицу подарков в столбцах видов и островов. Это сопоставит каждую строку в таблице penguin_presents_2022 с соответствующей строкой в таблице gifts, что позволит нам использовать столбец gift из таблицы gifts для назначения подарка каждому пингвину в таблице penguin_presents_2022.
Давайте запустим запрос, а затем обсудим, как он работает.
SELECT penguin_presents_2022.island, penguin_presents_2022.species, penguin_presents_2022.name, gifts.gift FROM penguin_presents_2022 INNER JOIN gifts ON penguin_presents_2022.island=gifts.island AND penguin_presents_2022.species=gifts.species;
Что сделал этот JOIN? Вы можете думать об этом так:
1. Начните с таблицы penguin_presents_2022
2. Посмотрите на таблицу gifts. Для каждой строки в penguin_presents_2022 найдите соответствующую строку в gifts, которая имеет совпадающие значения для island и species.
3. Верните name, island и species из таблицы penguin_presents_2022, а также gift из таблицы подарков.
С помощью этого запроса мы можем легко сослаться на подарок, который должен получить каждый пингвин, в зависимости от его острова и местоположения:
Обратите внимание, что, поскольку мы работаем с отдельными таблицами, нам нужно указать, из какой таблицы взят каждый столбец — поэтому мы пишем, например, penguin_presents_2022.island вместо просто island.
Псевдонимы
Вы можете подумать, что приведенный выше запрос выглядит немного громоздким. Мы столько раз повторяем названия столов! Это еще одна возможность использовать псевдонимы, которые мы впервые представили для столбцов в модуле 3. Мы можем давать таблицам разные имена (псевдонимы) в предложении FROM, что в целом позволяет сделать гораздо более краткий запрос:
SELECT p.island, p.species, p.name, g.gift FROM penguin_presents_2022 p INNER JOIN gifts g ON p.island = g.island AND p.species = g.species;
Это дает точно такие же результаты, как и предыдущий запрос, но намного короче.
Различные типы соединений
В приведенном выше примере мы использовали INNER JOIN, который включает только те строки, в которых есть совпадения в обеих таблицах. INNER JOIN используется по умолчанию — вы получите те же результаты, если просто используете JOIN в запросе. Это не единственный способ соединить две таблицы. Например, LEFT JOIN сохранит все строки в левой таблице (та, которая указана в предложении FROM), даже если в правой таблице нет соответствующей строки. Вы можете найти визуальное объяснение различных типов соединений здесь.
Давайте рассмотрим это дальше. Предположим, что на четвертом острове живут пингвины — назовем его «Новая надежда». Мы можем добавить этих пингвинов в таблицу penguin_presents_2022 с помощью
INSERT INTO penguin_presents_2022 (name, species, island)
VALUES ('Chewbeaka', 'Chinstrap', 'New Hope'),
('Darth Flipper', 'Gentoo', 'New Hope'),
('Princess Leia Egg', 'Adelie', 'New Hope'),
('Luke Skywaddler', 'Chinstrap', 'New Hope'),
('Boba Feather', 'Gentoo', 'New Hope');
Упражнение. Повторите предыдущий запрос Inner Join. Видите ли вы какие-то новые результаты после добавления пингвинов из New Hope? Почему или почему нет? Прочтите документацию по типам JOIN, чтобы попытаться понять, что происходит.
Давайте попробуем левое соединение. Левое соединение — это когда результаты из левой таблицы включаются, даже если в правой таблице нет совпадений.
SELECT p.island, p.species, p.name, g.gift FROM penguin_presents_2022 p LEFT JOIN gifts g ON p.island=g.island AND p.species=g.species
Что возвращает:
Пингвины Новой Надежды появляются в таблице, несмотря на то, что им не назначены подарки!
Левое соединение также дает нам полезный способ определить, у каких пингвинов еще нет подарков: мы можем искать нулевые значения в столбце подарков.
SELECT p.island, p.species, p.name, g.gift FROM penguin_presents_2022 p LEFT JOIN gifts g ON p.island=g.island AND p.species=g.species WHERE g.species is NULL;
Что возвращает:
Упражнение. Используя приемы, изученные в модуле 4, раздайте подарки пингвинам Новой Надежды. Вы можете выбрать любые подарки, которые вам нравятся. Попробуйте левое соединение еще раз. Что ты видишь? Теперь повторите попытку внутреннего соединения.
Поздравляем! Вы прошли пятый модуль курса bit.io SQL Snow Day. Это последний основной модуль курса. Если вы хотите получить сертификат об окончании, поделитесь с нами своей базой данных (с заполненными подарками для пингвинов Новой Надежды!). Вы можете сделать это, перейдя на страницу Поделиться и предоставив доступ пользователю InnerJoin.
Теперь пришло время перейти к шестому и последнему модулю, который охватывает продвинутые темы и поможет вам максимально использовать полученные знания SQL. Вы узнаете:
