SQL-запросы. В чем разница между INNER JOIN, RIGHT JOIN, LEFT JOIN? Inner join sql запросы
sql - Использование GROUP BY и ORDER BY в запросе INNER JOIN SQL
Я использую следующий запрос, чтобы группировать время работы и затраты для клиентов из трех таблиц: один для клиентов, один для рабочего времени и один для расходов:
SELECT a.*, COALESCE(b.totalCount, 0) AS CountWork, COALESCE(b.totalAmount, 0) AS WorkTotal, COALESCE(c.totalCount, 0) AS CountExpense, COALESCE(c.totalAmount, 0) AS ExpenseTotal FROM clients A LEFT JOIN ( SELECT Client, COUNT(*) totalCount, SUM(Amount) totalAmount FROM work_times WHERE DATE BETWEEN '2013-01-01' AND '2013-02-01' GROUP BY Client ) b ON a.Client = b.Client LEFT JOIN ( SELECT Client, COUNT(*) totalCount, SUM(Amount) totalAmount FROM expenses WHERE DATE BETWEEN '2013-01-01' AND '2013-02-01' GROUP BY Client ) c ON a.Client = c.Client WHERE b.Client IS NOT NULL OR c.Client IS NOT NULLВы можете увидеть запрос, работающий в скрипте здесь.
Я пытаюсь изменить этот запрос так, чтобы для каждого клиента была строка для каждого месяца, отсортированного по месяцам, а затем клиенту. Я пытаюсь сделать это со следующим измененным запросом:
Вы можете увидеть измененный запрос в действии здесь.
Тем не менее, он не работает. Для клиента B возвращается только одна строка, хотя в январе 2013 года есть рабочее время и расходы в феврале 2013 года (поэтому должно быть 2 строки), и кажется, что строки заказываются клиентом, а не месяцем. Может кто-нибудь предложить, как изменить запрос, чтобы получить желаемый результат, который для примера на втором скрипке будет: ╔════════╦═══════════╦═══════════╦══════════════╦══════════════╗ ║ CLIENT ║ COUNTWORK ║ WORKTOTAL ║ COUNTEXPENSE ║ EXPENSETOTAL ║ ╠════════╬═══════════╬═══════════╬══════════════╬══════════════╣ ║ A ║ 1 ║ 10 ║ 1 ║ 10 ║ ║ B ║ 1 ║ 20 ║ 0 ║ 0 ║ ║ A ║ 1 ║ 15 ║ 0 ║ 0 ║ ║ B ║ 0 ║ 0 ║ 1 ║ 10 ║ ║ C ║ 1 ║ 10 ║ 0 ║ 0 ║ ╚════════╩═══════════╩═══════════╩══════════════╩══════════════╝SQL-запросы. В чем разница между INNER JOIN, RIGHT JOIN, LEFT JOIN?
– Автор: Игорь (Администратор)В рамках сегодняшней статьи я расскажу вам в чем разница между INNER JOIN, RIGHT JOIN и LEFT JOIN, приведу подробные примеры, а так же предоставлю некоторые пояснения.
Без баз данных сегодня не обходится практически ни одно приложение. И в этом нет ничего странного, так как наличие пользовательской информации требует специальных хранилищ. Кто-то пытается реализовывать их собственными силами, используя, например, файлы. А кто-то использует уже готовые решения, по типу MySQL. Если в первом случае все задачи ложатся на плечи их создателей, то во втором авторам программных продуктов предоставляется универсальный язык для построения запросов под названием SQL
Однако, специфика использования SQL запросов может оказаться простой не для всех. Поэтому попробуем поэтапно посмотреть их на практике.
Допустим, что у вас существует две таблицы - авторы и программы. Сразу оговоримся, что не всегда известно какие программы кем написаны (обычно мелкие утилиты и инструменты можно встретить без указания авторства, к примеру, небольшие скрипты), а так же не все авторы еще что-либо написали (т.е. допустим, автор оставил заявку, но еще не приложил свое творение). Для простоты, таблицы назовем author и util.
Таблица авторов (author) будет выглядеть следующим образом:
| 1 | Вася |
| 2 | Коля |
| 3 | Петя |
Где id - это идентификатор автора, а name - это, собственно, имя автора
Таблица программ (util) будет выглядеть следующим образом:
| 1 | 1 | Вася Мега продукт |
| 2 | 0 |
ida-freewares.ru
join - Разделите очень большой запрос INNER JOIN SQL
SQL-запрос является довольно стандартным внутренним типом объединения. Например, сравнение n таблиц, чтобы увидеть, какой clientId существует во всех n таблицах, будет основным запросом WHERE... AND.
Проблема состоит в том, что размер таблиц составляет> 10 миллионов записей. База данных денормализуется. Нормализация - это не вариант. Запрос либо длится до завершения, либо не завершается.
Я не уверен, что это актуально, но мы используем модули задания Spring xd для других типов запросов.
Я не уверен, как разделить эту работу таким образом, чтобы ее можно запускать параллельно, чтобы она занимала меньше времени, поэтому, если шаг/подразделение не удается, он может продолжить работу с того места, где он остановился.
Другие сообщения с аналогичной проблемой предполагают использование альтернативных методов, помимо механизма базы данных, например, внедрение LOOP JOIN в коде или использование MapReduce или Hadoop, никогда не использовавшее ни то, что я не уверен, стоит ли рассматривать этот вариант использования.
Каков стандартный подход к такого рода операциям, я ожидаю, что он будет довольно распространенным явлением. Я мог бы использовать неправильные условия поиска для исследовательских подходов, потому что я не сталкивался ни с какими-либо стандартными стандартными решениями или четкими указаниями.
Исключительно загадочным исходным требованием было:
Сравните столбец party_id в трех очень больших таблицах, чтобы идентифицировать клиента, доступного в трех таблицах, т.е. Если это операция И между тремя. SAMPLE1.PARTY_ID И SAMPLE2.PARTY_ID И SAMPLE3.PARTY_ID
Если операция OR, выберите всех клиентов, доступных в трех таблицах. SAMPLE1.PARTY_ID ИЛИ SAMPLE2.PARTY_ID ИЛИ SAMPLE3.PARTY_ID
И/ИЛИ используются между таблицами, тогда выполняется сравнение по мере необходимости. SAMPLE1.PARTY_ID И SAMPLE2.PARTY_ID ИЛИ SAMPLE3.PARTY_ID
Я установил около 4 тестовых таблиц каждый с этим определением
CREATE TABLE 'TABLE1' ( 'CREATED' datetime DEFAULT NULL, 'PARTY_ID' varchar(45) NOT NULL, 'GROUP_ID' varchar(45) NOT NULL, 'SEQUENCE_ID' int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY ('SEQUENCE_ID') ) ENGINE=InnoDB AUTO_INCREMENT=978536 DEFAULT CHARSET=latin1;Я использовал следующий тестовый запрос
SELECT 'TABLE1'.'PARTY_ID' AS 'pi1', 'TABLE2'.'PARTY_ID' AS 'pi2', 'TABLE3'.'PARTY_ID' AS 'pi3', 'TABLE4'.'PARTY_ID' AS 'pi4' FROM 'devt1'.'TABLE2' AS 'TABLE2', 'devt1'.'TABLE1' AS 'TABLE1', 'devt1'.'TABLE3' AS 'TABLE3', 'devt1'.'TABLE4' AS 'TABLE4' WHERE 'TABLE2'.'PARTY_ID' = 'TABLE1'.'PARTY_ID' AND 'TABLE3'.'PARTY_ID' = 'TABLE2'.'PARTY_ID' AND 'TABLE4'.'PARTY_ID' = 'TABLE3'.'PARTY_ID'Предполагалось, что оно закончится менее чем за 10 минут, а размеры стола в 10 раз больше. Мой тестовый запрос еще не завершен, и он работает в течение 15 минут
qaru.site
sql - CROSS JOIN vs INNER JOIN в SQL Server 2008
CROSS JOIN = (INNER) JOIN = запятая (",")
TL; DR Единственное различие между SQL CROSS JOIN, INNER JOIN и запятой (",") (кроме запятой, имеющей более низкий приоритет для порядка оценки) состоит в том, что (INNER) JOIN имеет ON, а CROSS JOIN и запятая нет.
Re промежуточные продукты
Все три производят промежуточный концептуальный реляционный "декартово" "перекрестный" продукт из всех возможных комбинаций строки из каждой таблицы. Он ВКЛ и/или ГДЕ уменьшает количество строк. SQL Fiddle
Стандарт SQL определяет <comma> через продукт (7.5 1.b.ii), <cross join> через <comma> (7.7 1.a) и JOIN ON <условие поиска> через <comma> плюс WHERE (7.7 1.b).
Как пишет Википедия:
Крест
CROSS JOIN возвращает декартово произведение строк из таблиц в объединении. Другими словами, он будет создавать строки, которые объединяют каждую строку из первой таблицы с каждой строкой из второй таблицы.
Внутреннее соединение
[...] Результат объединения можно определить как результат первого принятия декартова произведения (или перекрестного соединения) всех записей в таблицах (объединение каждой записи в таблице A с каждой записью в таблице B), а затем возврат все записи, которые удовлетворяют предикату соединения.
"Неявная запись присоединения" просто перечисляет таблицы для присоединения в предложении FROM инструкции SELECT с использованием запятых для их разделения. Таким образом, он указывает крест-соединение
Re OUTER JOINs & ON vs WHERE в них см. Условия в LEFT JOIN (OUTER JOIN) против INNER JOIN.
Зачем сравнивать столбцы между таблицами?
Каждая таблица содержит строки, которые делают истинное утверждение из определенного шаблона утверждения заливки в [named-]. (Это делает истинное предложение из - удовлетворяет - определенному (характерному) предикату.)
-
Базовая таблица содержит строки, которые делают истинную инструкцию из некоторого шаблона оператора DBA:
/* rows where customer C.CustomerID has age C.Age and ... */ FROM Customers C -
Промежуточный продукт соединения содержит строки, которые делают истинное утверждение из шаблонов ИО его операндов:
/* rows where customer C.CustomerID has age C.Age and ... AND movie M.Movie is rented by customer M.CustomerID and ... */ FROM Customers C CROSS JOIN Movies M -
ON & WHERE условия ANDed, чтобы дать дополнительный шаблон. Значением снова являются строки, удовлетворяющие этому шаблону:
В частности, сравнение столбцов для равенства между таблицами означает, что строки, хранящиеся из продукта из частей соединенных таблиц, имеют одинаковое значение для этих столбцов. Просто совпадение в том, что многие строки обычно удаляются путем сопоставления сравнений между таблицами - что необходимо и достаточно, чтобы охарактеризовать нужные строки.
Просто напишите SQL для шаблона для строк, которые вы хотите!
Повторите значение запросов (и таблиц и условий): Как получить соответствующие данные из другой таблицы SQL для двух разных столбцов: Inner Join и/или Union? Есть ли какое-либо эмпирическое правило для создания SQL-запроса из удобочитаемого описания?
Перегрузка "кросс-соединения"
К сожалению, термин "кросс-соединение" используется для:
- Промежуточный продукт.
- ПЕРЕКРЕСТНЫЙ ПРИСОЕДИНЕНИЕ.
- (INNER) JOIN с ON или WHERE, который не сравнивает столбцы из одной таблицы с столбцами другого. (Так как это имеет тенденцию возвращать так много промежуточных строк продукта).
Использование CROSS JOIN vs (INNER) JOIN против запятой
Общим соглашением является:
- Используйте CROSS JOIN когда и только тогда, когда вы не сравниваете столбцы между таблицами. Это означает, что отсутствие сопоставлений было преднамеренным.
- Используйте (INNER) JOIN с ON когда и только когда вы сравниваете столбцы между таблицами (плюс, возможно, другие условия).
- Не используйте запятую.
Как правило, условия, не связанные с парами таблиц, хранятся для WHERE. Но они могут быть помещены в (n INNER) JOIN ON, чтобы получить соответствующие строки для аргумента для ВРАЩЕНИЯ, ВЛЕВО или ПОЛНОГО (ВНЕШНЕГО) ПРИСОЕДИНЕНИЯ.
Эти различные значения путаются. (Например, как и в других ответах и комментариях здесь.)
Re "Не использовать запятую" Смешивание запятой с эксплойтом JOIN может ввести в заблуждение, потому что запятая имеет более низкий приоритет. Но, учитывая роль промежуточного продукта в значении CROSS JOIN, (INNER) JOIN и запятой, аргументы вышеприведенного соглашения о том, чтобы не использовать его вообще, являются шаткими. CROSS JOIN или запятая точно так же, как (INNER) JOIN, что в состоянии TRUE. Промежуточный продукт, ON и WHERE, вводят AND в соответствующий предикат. Как бы то ни было, INNER JOIN ON - например, генерируя выходную строку только при поиске пары входных строк, которая удовлетворяет условию ON, тем не менее возвращает строки перекрестного соединения, которые удовлетворяют условию. Единственной причиной, по которой ON пришлось дополнить запятую в SQL, было написать OUTER JOINs. Конечно, выражение должно сделать его смысл понятным; но то, что ясно, зависит от того, что принято понимать.
Диаграммы Венна Диаграмма Венна с двумя пересекающимися кругами может иллюстрировать разницу между выходными строками для INNER, LEFT, RIGHT & FULL JOINs для одного и того же ввода. И когда значение ON равнозначно TRUE, результат INNER JOIN совпадает с CROSS JOIN. Также он может иллюстрировать строки ввода и вывода для INTERSECT, UNION & EXCEPT. И если оба входа имеют одинаковые столбцы, результат INTERSECT будет таким же, как стандартный SQL NATURAL JOIN. Но это не иллюстрирует, как (INNER) JOIN работает вообще. На первый взгляд это кажется правдоподобным. Он может идентифицировать некоторые части ввода и/или вывода для особых случаев ON, PK (первичные ключи), FK (внешние ключи) и/или SELECT. Все, что вам нужно сделать, чтобы увидеть это, - это определить, какие именно ограничения и каковы именно элементы наборов, представленных кругами. (Что в этих особых случаях повторные части никогда не уточняются.) (Помните, что в целом выходные строки имеют разные заголовки из входных строк.)
qaru.site
sql - ВНУТРЕННЕЕ ПРИСОЕДИНЕНИЕ к предложению WHERE
Применение условных операторов в ON/WHERE
Здесь я объяснил о шагах обработки логических запросов.
Справка: внутри SQL-запросов SQL Server ™ 2005 T-SQL
Издатель: Microsoft Press Паб Дата: 07 марта 2006 г. Печать ISBN-10: 0-7356-2313-9 Печать ISBN-13: 978-0-7356-2313-2 Страницы: 640
Внутри запросов Microsoft SQL Server ™ 2005 T-SQL
(8) SELECT (9) DISTINCT (11) TOP <top_specification> <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (6) WITH {CUBE | ROLLUP} (7) HAVING <having_condition> (10) ORDER BY <order_by_list>Первым заметным аспектом SQL, который отличается от других языков программирования, является порядок обработки кода. В большинстве языков программирования код обрабатывается в том порядке, в котором он написан. В SQL первое обрабатываемое предложение является предложением FROM, в то время как первое предложение SELECT, которое появляется первым, обрабатывается почти последним.
Каждый шаг генерирует виртуальную таблицу, которая используется в качестве входа на следующий шаг. Эти виртуальные таблицы недоступны для вызывающего (клиентского приложения или внешнего запроса). Конечной стороне возвращается только таблица, сгенерированная на последнем шаге. Если определенное условие не указано в запросе, соответствующий шаг просто пропущен.
Не беспокойтесь слишком много, если описание шагов, похоже, пока не имеет большого смысла. Они приведены в качестве ссылки. Разделы, которые появляются после примера сценария, будут более подробно описаны в шагах.
-
FROM: Декартово произведение (кросс-соединение) выполняется между двумя первыми двумя таблицами в предложении FROM, и в результате создается виртуальная таблица VT1.
-
ВКЛ: фильтр ВКЛ применяется к VT1. Только строки, для которых значение <join_condition> TRUE, вставляются в VT2.
-
OUTER (join): Если указан OUTER JOIN (в отличие от CROSS JOIN или INNER JOIN), строки из сохраненной таблицы или таблиц, для которых совпадение не было найдено, добавляются к строкам из VT2 как внешние строки, генерирующие VT3. Если в предложении FROM появляется более двух таблиц, шаги 1 - 3 применяются повторно между результатом последнего соединения и следующей таблицей в предложении FROM до тех пор, пока не будут обработаны все таблицы.
-
ГДЕ: Фильтр WHERE применяется к VT3. Только строки, для которых <where_condition> TRUE, вставляются в VT4.
-
GROUP BY: Строки из VT4 расположены в группах на основе списка столбцов, указанного в предложении GROUP BY. VT5 генерируется.
-
CUBE | ROLLUP: Супергруппы (группы групп) добавляются к строкам из VT5, генерируя VT6.
-
HAVING: Фильтр HAVING применяется к VT6. В VT7 вставляются только те группы, для которых значение <having_condition> TRUE.
-
SELECT: обрабатывается список SELECT, генерирующий VT8.
-
DISTINCT: Дублирующие строки удаляются из VT8. VT9 генерируется.
-
ORDER BY: строки из VT9 сортируются в соответствии с списком столбцов, указанным в предложении ORDER BY. Создается курсор (VC10).
-
TOP: указанное число или процент строк выбирается с начала VC10. Таблица VT11 генерируется и возвращается вызывающему абоненту.
(Применение условных операторов в ON/WHERE не будет иметь большого значения в нескольких случаях. Это зависит от того, сколько таблиц вы присоединились и количество строк доступно в каждой таблице соединений)
qaru.site
inner-join - SQL-запросы/поиск записей из внутренних столбцов объединения
Запрос:
SELECT (STUFF((SELECT ',' + CONVERT(VARCHAR(50),mode.model_name) FROM InventoryMake SUB INNER JOIN Model mode ON SUB.model_ID = mode.model_ID WHERE SUB.inv_ID = CAT.inv_ID FOR XML PATH('')), 1, 1, '' )) [Models],CAT.inv_ID FROM Inventory CATРезультаты запроса следующие:
Models inv_ID Pulsar,Hunk 14 Splender,Hunk 15 Chaly (CF50),Hunk,CBZ,Splender 16 Pulsar,Hunk 17 Pulsar,Hunk,CBZ 18Как видно из запроса выше, таблица InventoryMake имеет внешние ключи из таблиц Inventory and Model. Ниже показаны иллюстрации этих таблиц.
Таблица инвентаря
inv_ID inv_name 14 abc 15 bcx 16 glx 17 lco 18 btxТаблица инвентаризации
inm_id inv_ID model_ID 1 2 15 7 3 15 8 5 16 9 3 16 10 4 16 11 2 16 12 1 14 13 3 14 14 1 17 15 3 17Модельный стол
model_ID model_name 1 Pulsar 2 Splender 3 Hunk 4 CBZ 5 Chaly (CF50)Мне нужно найти записи для ввода пользователей, которые соответствуют либо inv_ID из таблицы Inventory, либо имя model_name из таблицы Model. Для этого я отредактировал запрос, как показано ниже.
SELECT (STUFF((SELECT ',' + CONVERT(VARCHAR(50),mode.model_name) FROM InventoryMake SUB INNER JOIN Model mode ON SUB.model_ID = mode.model_ID WHERE SUB.inv_ID = CAT.inv_ID FOR XML PATH('')), 1, 1, '' )) [Models],CAT.inv_ID FROM Inventory CAT WHERE CAT.inv_ID LIKE '%@term%'Из вышеприведенного запроса можно найти записи, соответствующие термину в inv_ID. Но мне нужно найти записи, которые соответствуют либо Inventory.inv_ID, либо Model.model_name. Как вы предлагаете мне это сделать? Заранее спасибо. PS: Я использую MSSQL
qaru.site
join - Запрос SQL Server INNER JOIN - пропущенные записи
FYI - этот запрос работает от excel. У меня есть поля подсказки для установки диапазона дат.
вот оригинальный рабочий запрос, который я получил от кого-то:
SELECT SalesInvoiceItems.FreeTextItem, SalesInvoiceItems.Product, SalesInvoiceItems.ItemDescription, SalesInvoiceItems.Quantity, SalesInvoiceItems.ItemValue, Customers.CustomerId, Customers.CustomerName, SalesInvoices.SalesInvoiceId, SalesInvoices.EffectiveDate, Countries.CountryId, SalesInvoiceItems.ItemType FROM Winman.dbo.Countries Countries, Winman.dbo.Customers Customers, Winman.dbo.Products Products, Winman.dbo.SalesInvoiceItems SalesInvoiceItems, Winman.dbo.SalesInvoices SalesInvoices WHERE Customers.Customer = SalesInvoices.Customer AND SalesInvoiceItems.SalesInvoice = SalesInvoices.SalesInvoice AND Customers.Country = Countries.Country AND ((SalesInvoices.SystemType='F') AND (SalesInvoiceItems.ItemType<>'T') AND (SalesInvoices.EffectiveDate>=? And SalesInvoices.EffectiveDate<=?) AND (SalesInvoiceItems.ItemValue<>$0)) ORDER BY SalesInvoiceItems.Quantity DESCВажным битом здесь является ItemType (это может быть только T, который исключается, P - для продуктов и N - для бесплатных текстовых элементов)
Мне нужно было добавить Table Products, чтобы получить ProductID. Очевидно, что добавление ниже кода в WHERE:
AND Products.Product = SalesInvoiceItems.Productне сможет открыть какой-либо свободный текстовый элемент, поскольку они не являются продуктами.
Поэтому я переписал запрос с JOINs, надеясь, что это решит мою проблему (воссоздание записей типа P и N):
SELECT Products.ProductId, SalesInvoiceItems.FreeTextItem, SalesInvoiceItems.Product, SalesInvoiceItems.ItemDescription, SalesInvoiceItems.Quantity, SalesInvoiceItems.ItemValue, Customers.CustomerId, Customers.CustomerName, SalesInvoices.SalesInvoiceId, SalesInvoices.EffectiveDate, Countries.CountryId, SalesInvoiceItems.ItemType FROM Winman.dbo.SalesInvoiceItems AS SalesInvoiceItems INNER JOIN Winman.dbo.Products AS Products ON Products.Product = SalesInvoiceItems.Product INNER JOIN Winman.dbo.SalesInvoices AS SalesInvoices ON SalesInvoices.SalesInvoice= SalesInvoiceItems.SalesInvoice INNER JOIN Winman.dbo.Customers AS Customers ON Customers.Customer = SalesInvoices.Customer INNER JOIN Winman.dbo.Countries AS Countries ON Countries.Country = Customers.Country WHERE ((SalesInvoices.SystemType='F') AND (SalesInvoiceItems.ItemType<>'T') AND (SalesInvoices.EffectiveDate >= ? And SalesInvoices.EffectiveDate <= ?) AND (SalesInvoiceItems.ItemValue <> $0) ) ORDER BY SalesInvoiceItems.Quantity DESCно это все еще действует как AND - который игнорирует бесплатные текстовые элементы! Я, очевидно, что-то пропустил. Как я могу принести оба продукта и бесплатные текстовые элементы, несмотря на то, что у бесплатных текстовых элементов нет ProductID?
qaru.site
