SQL INNER JOIN ключевых слов
w3big.com
Latest web development tutorials
Предыдущее: подключение к SQL (JOIN)
Далее: SQL LEFT JOIN Ключевое слово
SQL INNER JOIN ключевых слов
INNER JOIN ключевого слова возвращают строки, когда присутствие по меньшей мере одного матча в таблице.
SQL INNER JOIN синтаксис
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name ;
Или:
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name ;
Примечание: INNER JOIN и присоединиться к такой же.
Демонстрационная база данных
В этом уроке мы будем использовать w3big образец базы данных.
Ниже приводится выбранные «сайты» таблица данных:
+----+--------------+---------------------------+-------+---------+ | id | name | url | alexa | country | +----+--------------+---------------------------+-------+---------+ | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | | 3 | 本教程 | http://www.w3big.com/ | 4689 | CN | | 4 | 微博 | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | | 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND | +----+---------------+---------------------------+-------+---------+
google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 本教程 | http://www.w3big.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
Вот данные «access_log» веб-сайт записи доступа к таблице:
mysql> SELECT * FROM access_log; +-----+---------+-------+------------+ | aid | site_id | count | date | +-----+---------+-------+------------+ | 1 | 1 | 45 | 2016-05-10 | | 2 | 3 | 100 | 2016-05-13 | | 3 | 1 | 230 | 2016-05-14 | | 4 | 2 | 10 | 2016-05-14 | | 5 | 5 | 205 | 2016-05-14 | | 6 | 4 | 13 | 2016-05-15 | | 7 | 3 | 220 | 2016-05-15 | | 8 | 5 | 545 | 2016-05-16 | | 9 | 3 | 201 | 2016-05-17 | +-----+---------+-------+------------+ 9 rows in set (0.
00 sec)
Примеры SQL INNER JOIN
Следующий SQL-оператор будет возвращать все сайты записи доступа:
примеров
ВЫБОР Websites.name, access_log.count, access_log.date
с веб-сайтов
INNER JOIN access_log
ON Websites.id = access_log.site_id
ORDER BY access_log.count;
Выполнить выше SQL вывода результатов заключаются в следующем:
Примечание: ключевое слово INNER JOIN возвращает строки , когда присутствие по меньшей мере одного матча в таблице. Если «Веб-сайты» строка таблицы «access_log» не совпадают, эти строки не будут перечислены.
Предыдущее: подключение к SQL (JOIN)
Далее: SQL LEFT JOIN Ключевое слово
w3big.com | HTML курс | Web курс | Web Tutorial
Работа с внутренними соединениями в SQL
Самое чистое и простое из соединений SQL: скромное внутреннее соединение. Как следует из названия, внутреннее соединение между двумя объектами базы данных возвращает все строки с совпадающими ключами соединения; любые несоответствующие ключи исключаются из результата запроса.
Как и для всех объединений, вам нужны некоторые объекты базы данных (например, таблицы/представления), ключи для объединения и оператор select для выполнения внутреннего соединения:
выберите
изas t1
внутреннее соединениеas t2
on t1.id = t2.id
В приведенном выше примере для объединения двух таблиц используется только одно поле из каждой таблицы; если вы соединяетесь между двумя объектами базы данных, для которых требуется несколько полей, вы можете использовать операторы И/ИЛИ и, что более предпочтительно, суррогатные ключи. Вы можете дополнительно добавить WHERE, GROUP BY, ORDER BY, HAVING и другие предложения после соединений, чтобы создать фильтрацию, упорядочение и выполнение агрегирования.
Как и в случае любого запроса, в одном запросе можно выполнять столько соединений, сколько необходимо. Общий совет: старайтесь поддерживать модульность моделей данных, проводя регулярные аудиты DAG.
Если вы присоедините определенные таблицы выше по течению, нужны ли эти отдельные таблицы снова ниже по течению? Если ваш запрос включает несколько объединений и сложную логику и доступен конечным бизнес-пользователям, убедитесь, что вы используете табличные или добавочные материализации.
SQL inner join example
Table A car_type
| user_id | car_type |
|---|---|
| 1 | van |
| 2 | sedan |
| 3 | truck |
Таблица B CAR_COLOR
| USER_ID | CAR_COLOR | ||
|---|---|---|---|
| 1 | RED | ||
| 1 | RED | ||
| 1 | . 0025 | 3 | Зеленый |
| 4 | Желтый |
SELECT
CAR_TYPE.USER_ID AS aser_id,
CAR_TYPE.CAR_TYPE AS,
CAR_COLOR.C.COLOR.C.COLOR. }} as car_type
внутреннее соединение {{ ref('car_color') }} as car_color
on car_type.user_id = car_color.user_id
Таблица Б:
| user_id | type | color |
|---|---|---|
| 1 | van | red |
| 3 | truck | green |
Because there’s no user_id = 4 in Table A и нет user_id = 2 в таблице B, строки с идентификаторами 2 и 4 (из любой таблицы) исключаются из результатов запроса внутреннего соединения.
Вероятно, существует бесчисленное множество сценариев, в которых вы хотели бы внутренне объединить несколько таблиц вместе — возможно, у вас есть действительно хорошо структурированные таблицы с точно такими же первичными ключами, которые на самом деле должны быть просто одной большей, более широкой таблицей, или вы соединяете две таблицы. вместе не нужны нулевые или отсутствующие значения столбца, если вы использовали левое или правое соединение — все это в значительной степени зависит от ваших исходных данных и конечных вариантов использования. Где вы не увидите (и не должны) видеть внутренние соединения, так это в промежуточных моделях, которые используются для очистки и подготовки необработанных исходных данных для использования в аналитике. Любые объединения в ваших проектах dbt должны происходить дальше по течению в промежуточных и рыночных моделях, чтобы улучшить модульность и чистоту DAG.
Все, что нужно знать о ВНУТРЕННИХ СОЕДИНЕНИЯХ SQL
В продолжение моего введения в соединения SQL Server в этом руководстве по T-SQL я собираюсь более подробно рассмотреть наиболее часто используемые типы соединений в SQL Server.
– ВНУТРЕННЕЕ СОЕДИНЕНИЕ.
Что такое ВНУТРЕННЕЕ СОЕДИНЕНИЕ?
ВНУТРЕННЕЕ СОЕДИНЕНИЕ — это тип соединения, используемый для возврата строк из двух входных данных (таблиц или представлений), где обычно имеется точное совпадение в одном или нескольких столбцах между двумя входными данными. Этот тип наиболее распространен из-за того, как устроены реляционные базы данных. Процесс нормализации моделирует бизнес-сущности в группы связанных таблиц, поэтому возврат связанных данных между этими таблицами является чрезвычайно важным аспектом разработки приложений, и это делается с помощью INNER JOIN.
Как написать простое предложение INNER JOIN
Начнем с двух разных способов написания предложения INNER JOIN. Один из них представляет собой более старый синтаксис ANSI SQL-89, а другой — более новый синтаксис ANSI SQL-92. Я предпочитаю новый синтаксис, потому что он выглядит лучше и приводит к меньшему количеству ошибок при написании оператора соединения (подробнее об этом позже).
Для начала я создам две временные таблицы и буду хранить в них числовые значения. Таблица #Numbers1 будет содержать значения от 1 до 10, а #Numbers2 будет содержать значение 1, а затем все четные числа от 2 до 10.
Сначала давайте посмотрим на старый синтаксис ANSI SQL-89, и в этом синтаксисе есть две вещи, на которые следует обратить внимание. Во-первых, имена и псевдонимы таблиц перечисляются в предложении FROM через запятую. Если бы вы забыли добавить предложение WHERE, это все равно был бы вполне корректный синтаксис, но он вызвал бы CROSS JOIN, а не INNER JOIN, что привело бы к дорогостоящему декартовому продукту. Другими словами, отсутствие предложения WHERE приведет к тому, что каждая строка в таблице #Numbers1 будет умножена на каждую строку в таблице #Numbers2, что не входит в наши намерения. Этот синтаксис, несмотря на то, что он идеально подходит для ВНУТРЕННИХ СОЕДИНЕНИЙ (не разрешен для ВНЕШНИХ СОЕДИНЕНИЙ), может привести к случайным проблемам. Вы можете узнать больше о различиях между INNER JOIN и OUTER JOIN здесь.
Далее идет синтаксис ANSI SQL-92. В этом синтаксисе вы используете ключевое слово JOIN между таблицами, которые вы хотите соединить, а ключевое слово INNER не требуется в SQL Server. Предложение ON требуется с синтаксисом INNER JOIN, и именно здесь вы указываете столбцы для сравнения для объединения.
Здесь я хочу отметить одну вещь: как оцениваются сравнения для операторов JOIN. В стандартном синтаксисе SQL предложение FROM оценивается перед предложением WHERE. Это означает, что фильтр, установленный в предложении FROM, оценивается перед предложением WHERE — важное, но тонкое отличие, когда фильтры применяются для возврата данных в наших запросах.
4 Примеры распространенных проблем, которых следует избегать при использовании ВНУТРЕННЕГО СОЕДИНЕНИЯ
Одной из наиболее распространенных проблем, с которыми я сталкиваюсь, является то, как часто клиенты чрезмерно нормализуют структуру своей базы данных. Нормализация к третьей нормальной форме — отличная теория с точки зрения дизайна.
Тем не менее выигрыш от уменьшения избыточности данных часто сильно перевешивается стоимостью дополнительных необходимых объединений для возврата данных.
Оптимизатор SQL Server обычно надежен в принятии правильных решений с точки зрения порядка соединения, но иногда ошибается. Включение слишком большого количества таблиц в операторы SQL часто может привести к снижению производительности, особенно если оператору SQL не требуются все таблицы, перечисленные в предложении соединения (что встречается чаще, чем вы думаете!)
Ниже я расскажу о некоторых других проблемах с ВНУТРЕННИМ СОЕДИНЕНИЕМ и предоставлю рекомендации и советы, которые помогут вам оптимизировать производительность.
1. Соединения многих таблиц
Очень часто я вижу пользовательский код с операторами SQL, которые включают шесть или более внутренних соединений между таблицами. Это связано с тем, что дизайн базы данных настолько нормализован, что необходимо иметь такое количество объединений, ИЛИ клиент просто пытается сделать слишком много с помощью одного запроса.
В любом случае, чем больше таблиц вы ВНУТРЕННИМ СОЕДИНЕНИЕМ в одном операторе SQL, тем больше вероятность того, что оптимизатор запросов не выберет оптимальный путь соединения. Как правило, я склоняюсь к тому, чтобы клиенты начали задумываться о рефакторинге внутренних соединений, когда оператор select включает шесть или более внутренних соединений.
При столкновении с очень сложным оператором SQL, включающим несколько внутренних соединений между таблицами, у оптимизатора запросов есть хитрый способ справиться с такими ситуациями. Чтобы обеспечить своевременное выполнение запроса, оптимизатор устанавливает ограничение по времени на то, сколько оптимизации он будет выполнять. Как только он достигнет этого предела, он выполнит лучший план, который он нашел на тот момент времени. Рассмотрим следующий пример INNER JOIN, который включает 6 внутренних объединенных таблиц в образце базы данных AdventureWorks:
В плане выполнения я могу посмотреть свойство плана «Причина досрочного завершения оптимизации оператора», которое в данном случае имеет значение «Время ожидания»:
Это означает, что во время оптимизации запроса было так много вариантов плана, что лимит времени для оптимизации был достигнут, и в качестве плана выполнения был выбран «лучший» план из изученных альтернатив плана.
Это происходит довольно часто, и если у вас есть сложные операторы T-SQL и вы не получаете желаемого плана выполнения, возможно, пришло время реорганизовать эти операторы T-SQL, чтобы они включали меньше объединений, или разбить операторы на отдельные заявления.
2. Исключение таблиц INNER JOIN
В некоторых случаях оптимизатор запросов SQL Server может исключить таблицы, включенные в операторы INNER JOIN, если таблицы действительно не нужны. Рассмотрим этот оператор соединения между таблицами Sales.SalesOrderHeader, Sales.SalesOrderDetail и Production.Product:
Глядя на план выполнения этого оператора, мы видим, что есть ссылка на таблицу Sales.SalesOrderDetail и таблицу Production.Product, но нет ссылки в таблицу Sales.SalesOrderHeader:
Причина в том, что существует связь внешнего ключа между таблицами Sales.SalesOrderHeader и Sales.SalesOrderDetail. В предложении WHERE нет фильтра для таблицы Sales.SalesOrderHeader, и столбцы из этой таблицы не упоминаются в списке SELECT.
Доверенный внешний ключ сообщает SQL Server, что для каждой записи в таблице Sales.SalesOrderDetail должна быть связанная запись (с тем же самым SalesOrderID) в таблице Sales.SalesOrderHeader. Это отношение «один ко многим» между этими двумя таблицами. Это ограничение внешнего ключа в сочетании с отсутствием ссылок на таблицу Sales.SalesOrderHeader позволяет SQL Server решить, что нет причин включать таблицу Sales.SalesOrderHeader в запрос.
Отсутствует связь по внешнему ключу между таблицами Sales.SalesOrderDetail и таблицей Production.Product. Из-за этого требуется соединение этих двух таблиц, хотя в запросе не требуется никакой информации из таблицы Production.Product. Если я добавлю внешний ключ и повторно запущу запрос, мы увидим, что физическое соединение между двумя таблицами устранено:
Давайте удалим это ограничение для остальных демонстраций.
3. Соединения неравенства
Операторы неравенства (любое сравнение, кроме оператора сравнения равенства (=)) могут использоваться для сравнения значений столбцов для операторов INNER JOIN, но часто сравнение не имеет большого смысла, если условие соединения по неравенству является единственным сравнением.
Выполнено. Рассмотрим следующий пример INNER JOIN:
Результатом этого запроса является 29 строк. Как таблица с 10 строками, соединенная с таблицей с 6 строками, может вернуть набор результатов с 29 строками? Что происходит, так это то, что сначала вычисляется декартово произведение между двумя таблицами, а затем происходит сравнение. Итак, каждое значение из #Numbers1 сравнивается с каждым значением из #Numbers2, и когда значение из #Numbers1 больше, чем значение из #Numbers2, оно возвращается. Более разумный запрос будет включать сравнение на равенство и сравнение на неравенство. Например, следующий запрос на соединение возвращает те продажи из Sales.SalesOrderDetail, где цена для данного ProductID меньше, чем ListPrice для этого продукта из таблицы Production.Product:
4. Подсказки для присоединения
Одна из областей, где клиенты действительно делают неправильный выбор, — это использование подсказок запросов в своем коде. Это почти всегда делается из лучших побуждений — обычно потому, что оптимизатор запросов делает определенный выбор, который не работает для определенного момента времени.
Несколько причин для этого: устаревшие статистические данные, неправильная оценка количества элементов локальных переменных и чувствительность параметров. Не поймите меня неправильно — безусловно, бывают случаи, когда подсказка МОЖЕТ помочь в выполнении запроса. Тем не менее, я всегда призываю клиентов использовать их с осторожностью, поскольку они всегда пользуются уважением, и мы часто не замечаем побочных эффектов их использования.
Рассмотрим еще раз вариант запроса, который мы использовали в этой статье. Соединение между таблицей Sales.SalesOrderDetail и таблицей Production.Product создает физическое соединение Hash Match:
HASH JOIN обычно отлично подходит для работы с большими объемами данных. Сначала он строит в памяти структуру данных, хешируя ключи, участвующие в операции соединения. Это называется этапом «сборки» и является этапом блокировки (блокировка в смысле приостановки выполнения запроса, а не блокировки других операций с помощью блокировок), поскольку часто требуется запрашивать дополнительную память рабочей области.
В идеале эта «внешняя» таблица должна быть меньшей из двух таблиц. Затем строки для других входных данных сканируются, хэшируются и сравниваются со значениями на этапе построения. Это отличный выбор для соединения двух больших входов.
Может возникнуть соблазн использовать подсказку, чтобы всегда использовать HASH JOIN. Однако это может вызвать некоторые непредвиденные проблемы, если сделать это без тестирования. Причина этого в том, что когда в запросе используется подсказка физического соединения, применяется порядок написания таблиц в операторе. Простое добавление ключевого слова HASH в оператор INNER JOIN всегда будет вызывать HASH JOIN, как показано ниже: общее выполнение запроса будет намного медленнее, чем исходный запрос без подсказки.
Выполняя два запроса в одном пакете, мы видим, что запрос без подсказки в два раза быстрее (по затратам в плане запроса — и процессорному времени выполнения), чем запрос с подсказкой и принудительным порядком :
Важность знания того, когда использовать INNER JOIN в SQL Понимание того, как работает INNER JOIN, является важным навыком для любого разработчика баз данных.
Вы тратите слишком много времени на устранение неполадок с производительностью базы данных и исправляете одну вещь только для того, чтобы сломаться еще две? SolarWinds® SQL Sentry создан для того, чтобы дать вам всеобъемлющее представление о производительности SQL Server, ОС, уровня виртуализации и служб SQL Server Analysis Services (SSAS).