Перевод статьи «SQL INNER JOIN Explained in Simple Words».
Как известно, база данных хранит данные в таблицах. Скорее всего вы уже умеете писать запросы к одной таблице. Но что если требуется поработать с несколькими таблицами? Чтобы комбинировать данные из двух и более таблиц, нужно воспользоваться оператором JOIN. Сегодня на примерах разберемся, как работает INNER JOIN.
JOIN для двух таблиц
Наша тренировочная база данных будет состоять из двух таблиц: TRAVEL_PACK и CITIES:
Таблица TRAVEL_PACK
| PACK_NAME | BEST_SEASON | PRICE | DURATION |
|---|---|---|---|
| United States: Big Cities | All year | $3500.00 | 10 days |
| United States: West Coast | March to May | $3700.00 | 12 days |
| South American Tour | August to November | $1850.00 | 18 days |
| Beaches of Brazil | December to March | $2200.00 | 7 days |
Таблица CITIES
| NAME | TYPE_OF_CITY | PACK_NAME | DAYS_STAYING |
|---|---|---|---|
| San Francisco historica | historical | United States: West Coas | 5 |
| Washington | historical | United States: Big Cities | 3 |
| New York | business | United States: Big Cities | 7 |
| Rio de Janeiro | beach | Beaches of Brazil | 4 |
| Ushuaia | mountain | South American Tour | 3 |
| Salvador de Bahia | beach | Beaches of Brazil | 3 |
| Los Angeles | beach | United States: West Coast | 7 |
Оператор JOIN объединяет записи таблиц по общему полю или колонке (т.е. такая колонка должна быть в каждой из таблиц). В нашем случае у нас есть колонка PACK_NAME в таблице TRAVEL_PACK и точно такая же — в таблице CITIES.
Легко заметить, что в обеих таблицах эта колонка называется одинаково. Но это не обязательно, и в других базах данных может быть иначе. Другими словами, при использовании оператора INNER JOIN названия общей колонки в таблицах могут совпадать, но могут и отличаться.
Первый JOIN — найдем путевки по приморским городам
Залог хорошего SQL-запроса — четкое понимание того, где хранятся нужные данные. В нашем случае очевидно, что для поиска названий путёвок понадобится TRAVEL_PACK, а проверить, находится ли город у моря, можно в таблице CITIES.
Комбинировать данные из двух таблиц можно с помощью конструкции JOIN. Она создает связь между таблицами и помогает отобразить данные из них единовременно. Условно говоря, из двух строк отдельных таблиц JOIN создает одну целую. Такой тип соединения называется INNER JOIN (впрочем, он является синонимом для JOIN). Наряду с этим видом часто используют LEFT, RIGHT, OUTER и много других типов JOIN.
Вот как JOIN создаёт соединённые строки с данными:
Синтаксис запроса с JOIN:
FROM table1 INNER JOIN table2 ON common column in table1 = common column in table2
Подставим в эту схему названия таблиц и колонок из тренировочной базы данных:
FROM CITIES INNER JOIN TRAVEL_PACK ON CITIES.PACK_NAME = TRAVEL_PACK.PACK_NAME FROM CITIES
После секции FROM с INNER JOIN можно переходить к написанию SELECT с WHERE:
SELECT TRAVEL_PACK.PACK_NAME
Обратите внимание, что обращение к колонке PACK_NAME сделано с добавлением названия таблицы (TRAVEL_PACK). Это хорошая практика для случаев, когда в запросе задействовано больше одной таблицы.
При помощи оператора WHERE отфильтруем результаты, чтобы остались только приморские города:
WHERE CITIES.TYPE_OF_CITY = 'beach'
В результате должен получится вот такой запрос:
SELECT TRAVEL_PACK.PACK_NAME FROM CITIES JOIN TRAVEL_PACK ON CITIES.PACK_NAME = TRAVEL_PACK.PACK_NAME WHERE CITIES.TYPE_OF_CITY = 'beach'
А результатом его выполнения будет такой датасет:
| NAME |
|---|
| Los Angeles |
| Salvador de Bahia |
| Rio de Janeiro |
Следующий шаг — найти города с путевками дешевле $2500.
По образу и подобию предыдущего запроса можно обратиться к двум известным таблицам CITIES и TRAVEL_PACK и соединить их с помощью INNER JOIN (далее JOIN и INNER JOIN будут использоваться взаимозаменяемо).
По условиям задачи запрос из предыдущего раздела можно оставить почти неизменным — достаточно сделать легкие изменения в SELECT и WHERE.
Начнем с SELECT, в нем обратимся к наименованию города:
SELECT CITY.NAME
В фильтрации WHERE ограничим набор данных по стоимости:
WHERE TRAVEL_PACK.PRICE <= 2500
Полностью запрос будет выглядеть так:
SELECT CITY.NAME FROM CITIES JOIN TRAVEL_PACK ON CITIES.PACK_NAME = TRAVEL_PACK.PACK_NAME WHERE TRAVEL_PACK.PRICE <= 2500
А вернёт он такие данные:
| NAME |
|---|
| Rio de Janeiro |
| Ushuaia |
| Salvador de Bahia |
Глубокое погружение: INNER JOIN для трёх таблиц
JOIN можно использовать не только с двумя, но и с большим числом таблиц. Для соединения каждой пары таблиц необходим один JOIN. Схематический пример:
FROM T1 JOIN T2 ON ......
JOIN T3 ON .......
Обратимся к практическому примеру. Добавим в базу данных новую таблицу STATES, в которой будем хранить данные о государствах, регионах и провинциях разных стран.
Таблица TRAVEL_PACK
| PACK_NAME | BEST_SEASON | PRICE | DURATION |
|---|---|---|---|
| United States: Big Cities | All year | $3500.00 | 10 days |
| United States: West Coast | March to May | $3700.00 | 12 days |
| South American Tour | August to November | $1850.00 | 18 days |
| Beaches of Brazil | December to March | $2200.00 | 7 days |
Таблица STATES
| NAME | COUNTRY | POPULATION | LANGUAGE |
|---|---|---|---|
| New York | United States | 17000000 | english |
| Tierra del Fuego | Argentina | 190000 | spanish |
| California | United States | 13400000 | english |
| Rio de Janeiro | Brasil | 15000000 | portuguese |
| Bahia | Brasil | 8000000 | portuguese |
Таблица CITIES
| NAME | TYPE_OF CITY | PACK_NAME | DAYS_STAY | STATE |
|---|---|---|---|---|
| San Francisco | historical | United States: West Coast | 5 | California |
| Washington | historical | United States: Big Cities | 3 | Washington |
| New York | business | United States: Big Cities | 7 | New York |
| Rio de Janeiro | beach | Beaches of Brazil | 4 | Rio de Janeiro |
| Ushuaia | mountain | South American Tour | 3 | Tierra del Fuego |
| Salvador de Bahia | beach | Beaches of Brazil | 3 | Bahia |
| Los Angeles | beach | United States: West Coast | 7 | California |
Запросим все туристические города из регионов, говорящих на испанском и португальском. Понятно, что нужно соединить таблицы CITIES и STATES, а затем приджоинить к ним TRAVEL_PACK. Приступим к решению задачи и используем знания из предыдущих частей этой статьи.
Во-первых, сделаем JOIN для таблиц CITIES и STATES по колонкам CITIES.STATE и STATE.NAME:
FROM CITIES JOIN STATES ON CITIES.STATE = STATES.NAME
Сделаем JOIN с третьей таблицей TRAVEL_PACK:
FROM CITIES JOIN STATES ON CITIES.STATE = STATES.NAME JOIN TRAVEL_PACK ON CITIES.PACK_NAME = TRAVEL_PACK.PACK_NAME
Отполируем запрос с помощью SELECT и WHERE:
SELECT CITIES.NAME, STATES.NAME, TRAVEL_PACK.PACK_NAME, STATES.LANGUAGE
FROM CITIES JOIN STATES ON CITIES.STATE = STATES.NAME
JOIN TRAVEL_PACK ON CITIES.PACK_NAME = TRAVEL_PACK.PACK_NAME
WHERE STATES.LANGUAGE IN ('spanish','portuguese')
Результат:
| NAME | NAME | PACK_NAME | LANGUAGE |
|---|---|---|---|
| Salvador | Bahia | Beaches of Brazil | portuguese |
| Rio de Janeiro | Rio de Janeiro | Beaches of Brazil | portuguese |
| Ushuaia | Tierra del Fuego | South American Tour | spanish |
Итоги
В этой статье мы рассмотрели объединение записей в SQL-таблицах. Оператор JOIN открывает перед вами множество новых возможностей в использовании SQL.
От редакции Techrocks: Если хотите изучить не только INNER JOIN, но и другие виды объединений, обратите внимание на статью “SQL JOIN: руководство по объединению таблиц”.
[customscript]techrocks_custom_after_post_html[/customscript]
[customscript]techrocks_custom_script[/customscript]
