Inner join on: SQL INNER JOIN Keyword

Содержание

INNER JOIN условие в WHERE пункте или ON пункте?



Сегодня я неправильно набрал запрос, но он все равно сработал и дал желаемый результат. Я хотел запустить этот запрос:

SELECT e.id FROM employees e JOIN users u ON u.email=e.email WHERE u.id='139840'

но я случайно запустил этот запрос

SELECT e.id FROM employees e JOIN users u ON u.email=e.email AND u.id='139840'

(обратите внимание на AND вместо WHERE в последнем предложении)

и оба вернули правильный идентификатор сотрудника из идентификатора пользователя.

В чем разница между этими 2 запросами? Присоединяется ли вторая форма только к членам 2 таблиц, удовлетворяющих критериям, в то время как первая присоединится ко всей таблице, а затем выполнит запрос? Является ли одно более или менее эффективным, чем другое? Может быть, я упускаю что-то еще?

Спасибо!

mysql sql performance inner-join
Поделиться Источник chiliNUT     17 января 2014 в 21:27

5 ответов


  • Подзапрос v / s inner join in sql server

    У меня есть следующие вопросы Первый из них использует внутреннее соединение SELECT item_ID,item_Code,item_Name FROM [Pharmacy].[tblitemHdr] I INNER JOIN EMR.tblFavourites F ON I.item_ID=F.itemID WHERE F.doctorID = @doctorId AND F.favType = 'I' второй с помощью подзапроса типа SELECT...

  • PostgreSQL внутренний JOIN против WHERE пункт join

    Я думал, что внутренний JOIN эквивалентен JOIN в предложении WHERE. Когда я делаю join, используя предложение WHERE, я получаю возвращенные строки. Но если я делаю INNER JOIN,то никакие строки не выводятся. Я получаю результат, что совпадений нет. Я ошибаюсь или делаю что-то не так? Эквивалентны...



6

Для внутренних соединений, подобных этому, они логически эквивалентны. Однако вы можете столкнуться с ситуациями, когда условие в предложении join означает нечто иное, чем условие в предложении where.

В качестве простой иллюстрации представьте, что вы делаете левое соединение вот так;

select x.id
from x
       left join y
         on x.id = y.id
;

Здесь мы берем все строки из x, независимо от того, есть ли соответствующий идентификатор в y. Теперь предположим, что наше условие соединения растет - мы ищем совпадения не только в y на основе идентификатора, но и на основе id_type.

select x.id
from x
       left join y
         on x.id = y.id
         and y.id_type = 'some type'
;

Опять же, это дает все строки в x независимо от того, есть ли совпадение (id, id_type) в y.

Однако это совсем другое дело:

select x.id
from x
       left join y
         on x.id = y.id
where y.id_type = 'some type'
;

В этой ситуации мы выбираем все строки из x и пытаемся сопоставить их со строками из y. Теперь для строк, для которых нет совпадения в y, y.id_type будет null. Из-за этого y.id_type = 'some type' не удовлетворяется, поэтому те строки, в которых нет совпадения, отбрасываются, что фактически превратило это в inner join.

Короче говоря, для внутренних соединений не имеет значения, куда идут условия, но для внешних соединений это может быть.

Поделиться

yieldsfalsehood     17 января 2014 в 21:36



4

Оптимизатор будет относиться к ним одинаково. Вы можете сделать EXPLAIN, чтобы доказать это себе.

Поэтому напишите тот, который понятнее.

SELECT e.id
FROM employees e JOIN users u ON u.email=e.email
WHERE u.id='139840'

Поделиться Andy Lester     17 января 2014 в 21:36



3

В случае INNER JOIN два запроса семантически одинаковы, что означает, что они гарантированно будут иметь одинаковые результаты. Если бы вы использовали соединение OUTER, значение двух запросов могло бы быть очень разным, с разными результатами.

С точки зрения производительности я ожидал бы, что эти два запроса приведут к одному и тому же плану выполнения. Однако механизм запросов может вас удивить. Единственный способ узнать это-просмотреть планы выполнения для двух запросов.

Поделиться Joel Coehoorn     17 января 2014 в 21:31



2

Если бы это было внешнее соединение, а не внутреннее, вы получили бы непреднамеренные результаты, но при использовании inner join не имеет значения, используете ли вы дополнительные критерии соединения вместо предложения WHERE .

С точки зрения производительности они, скорее всего, идентичны, но не могут быть уверены.

Поделиться Hart CO     17 января 2014 в 21:32



1

Я поднял этот вопрос со своими коллегами по нашей команде на работе. Этот ответ немного SQL Server центрирован, а не MySQL. Однако оптимизатор должен иметь сходство в работе между SQL и MySQL..

Некоторые мысли: По сути, если вам нужно добавить WHERE, есть дополнительные проверки таблиц, выполняемые для проверки равенства для каждого условия (это увеличивается на порядки с AND или набором данных, OR, решение принимается при первом истинном условии) – если у вас есть один указатель идентификатора в приведенном примере, это очень быстро, наоборот, если вам нужно найти все записи, принадлежащие компании или отделу, это становится более неясным, так как у вас может быть несколько записей. Если вы можете применить условие равенства, оно гораздо эффективнее при работе с таблицей AuditLog или EventLog, содержащей миллионы строк. На самом деле никто не увидит больших преимуществ этого на небольших таблицах (около 200 000 строк или около того).

От: Алесандро Алпи http://suxstellino.wordpress.com/2013/01/07/sql-server-logical-query-processing-summary/

От: Ицик Бен-Ган http://tsql.solidq.com/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

Поделиться Leptonator     19 января 2014 в 01:31


  • Что не так с этим кодом mysql inner join?

    Я создал этот код, но он, похоже, не работает. правильно ли а в пункте где paymentnotification.subpayment и collections.status $sql = SELECT...

  • В чем разница между join в FROM пункте и WHERE пункте?

    У нас есть Oracle 10g, и большинство наших приложений работают под управлением Oracle Forms 6i. Я обнаружил, что все запросы, написанные в views/packages/procedures/functions, являются таблицами JOINING на уровне предложения WHERE. Пример SELECT * FROM TABLE_A A, TABLE_B B, TABLE_C C, TABLE_D D...


Похожие вопросы:


Который выполняет первое предложение WHERE или JOIN

Какое предложение выполняется первым в операторе SELECT ? У меня есть сомнения в select запросе на этом основании. рассмотрим следующий пример SELECT * FROM #temp A INNER JOIN #temp B ON A.id = B.id...


Условия в пункте WHERE и в пункте ON

Предположим, у меня есть две таблицы, Student Test Id Name TestId Type StudentId -- ---- ------ ---- --------- 1 Mark 774 Hard 1 2 Sam 774 Hard 2 3 John 775 Easy 3 Теперь я должен найти тех...


Разница ч/б поставив условие: в пункте JOIN против WHERE п.

Предположим, у меня есть 3 таблицы. торговый представитель Rep Код имя фамилия Телефон Email команда продаж Заказы номер заказа Rep Код номер клиента дата заказа статус заказа Покупатель номер...


Подзапрос v / s inner join in sql server

У меня есть следующие вопросы Первый из них использует внутреннее соединение SELECT item_ID,item_Code,item_Name FROM [Pharmacy].[tblitemHdr] I INNER JOIN EMR.tblFavourites F ON I.item_ID=F.itemID...


PostgreSQL внутренний JOIN против WHERE пункт join

Я думал, что внутренний JOIN эквивалентен JOIN в предложении WHERE. Когда я делаю join, используя предложение WHERE, я получаю возвращенные строки. Но если я делаю INNER JOIN,то никакие строки не...


Что не так с этим кодом mysql inner join?

Я создал этот код, но он, похоже, не работает. правильно ли а в пункте где paymentnotification.subpayment и collections.status $sql = SELECT...


В чем разница между join в FROM пункте и WHERE пункте?

У нас есть Oracle 10g, и большинство наших приложений работают под управлением Oracle Forms 6i. Я обнаружил, что все запросы, написанные в views/packages/procedures/functions, являются таблицами...


join условие "ON" vs В "где"

SELECT * FROM Customers c INNER JOIN CustomerAccounts ca ON ca.CustomerID = c.CustomerID AND c.State = 'NY' INNER JOIN Accounts a ON ca.AccountID = a.AccountID AND a.Status = 1 Эквивалент: SELECT *...


Дело в пункте WHERE в Т-SQL

Попытка использовать CASE для условного параметра Я уже публиковал это раньше, но так как я не мог достичь правильного результата, то публикую его снова с другим подходом. У меня есть сложный код,...


INNER JOIN с условием-Mysql

Я пытаюсь сделать inner join, если условие истинно, но оно не работает, я попробовал эти 2 способа: IF chat.tipo = 'vitima' THEN INNER JOIN vitima ON vitima.id_vit = chat.id_tipo ELSE INNER JOIN...

Как использовать AS в SQL INNER JOIN?



Я новичок в Mysql. У меня есть несколько таблиц, и я хочу join все из них. Для этого я использую внутренний JOIN.

"SELECT *
FROM table
               INNER JOIN table2
               ON table.client_id = table2.id

           WHERE table2.id= 113
           ORDER BY table.id DESC
           LIMIT 1 ";

Здесь я сталкиваюсь с проблемой, что у меня есть название столбца в каждой таблице. Я хочу использовать AS в своей команде. Как и в таблице outlook, есть имя столбца TITLE, я хочу использовать его как outlook_title. Как это возможно?

Я хочу использовать echo для печати таких данных

echo '<h2>' .$row["client"]. '</h2>' ;
echo '<h2>' .$row["name"]. '</h2>' ;
echo '<h2>' .$row["name"]. '</h2>' ;
echo '<h2>' .$row["ation_title"]. '</h2>' ;
echo '<h2>' .$row["look_title"]. '</h2>' ;

Пожалуйста, помогите мне

php mysql join inner-join
Поделиться Источник sunny     22 июля 2015 в 06:56

2 ответа


  • Как удалить из нескольких таблиц с помощью INNER JOIN in SQL server

    В MySQL вы можете использовать синтаксис DELETE t1,t2 FROM table1 AS t1 INNER JOIN table2 t2 ... INNER JOIN table3 t3 ... Как мне сделать то же самое в SQL Server?

  • SQL INNER JOIN синтаксис

    два бита SQL ниже получают тот же результат SELECT c.name, o.product FROM customer c, order o WHERE c.id = o.cust_id AND o.value = 150 SELECT c.name, o.product FROM customer c INNER JOIN order o on c.id = o.cust_id WHERE o.value = 150 Я видел, как оба стиля используются в качестве стандарта в...



2

Вы сказали, каждый стол, что я знаю:>

Добавьте другие столбцы на досуге.

SELECT r.title as rtitle,c.title as ctitle,
t.title as ttitle,a.title as atitle,o.title as otitle
FROM og_ratings r 
INNER JOIN og_companies c
ON r.client_id = c.id
INNER JOIN og_rating_types t
ON r.rating_type_id = t.id
INNER JOIN og_actions a
ON r.pacra_action = a.id
INNER JOIN og_outlooks o
ON r.pacra_outlook = o.id
WHERE c.id= 113
ORDER BY r.id DESC
LIMIT 1

Поделиться Drew     22 июля 2015 в 07:06



1

вы можете присвоить псевдоним с помощью AS своим столбцам, а также результату subquery

SELECT s.title as stitle,p.title as ptitle
FROM og_ratings s 
INNER JOIN og_companies p
ON s.client_id = p.id
WHERE s.id= 115

Поделиться atifaltaf     08 марта 2016 в 08:37


Похожие вопросы:


Как использовать INNER JOIN?

Я новичок в PHP. У меня есть запрос sql. Я использую внутренний Join. Я сталкиваюсь с некоторыми проблемами в этом. Если есть какое-то одно пустое поле, то оно пусто и не отображает никаких данных...


SQL INNER JOIN реализовано как неявное JOIN

Недавно я наткнулся на запрос SQL, который выглядел следующим образом: SELECT * FROM A, B WHERE A.NUM = B.NUM Мне кажется, что это вернется точно так же, как INNER JOIN : SELECT * FROM A INNER JOIN...


SQL inner join проблема

Следующий запрос SQL не работает. Я думаю, что ошибка находится в первой строке. SELECT SUBSTRING(tbl_news.comment, 1, 250) as tbl_news.comment, tbl_news.id, tbl_news.date, tbl_news.subject,...


Как удалить из нескольких таблиц с помощью INNER JOIN in SQL server

В MySQL вы можете использовать синтаксис DELETE t1,t2 FROM table1 AS t1 INNER JOIN table2 t2 ... INNER JOIN table3 t3 ... Как мне сделать то же самое в SQL Server?


SQL INNER JOIN синтаксис

два бита SQL ниже получают тот же результат SELECT c.name, o.product FROM customer c, order o WHERE c.id = o.cust_id AND o.value = 150 SELECT c.name, o.product FROM customer c INNER JOIN order o on...


Как использовать Left join вместо inner join

Я написал следующий запрос, который соединяет таблицу с уже Соединенными таблицами. Я в основном использовал внутренний join, используя SQL Server. Мне интересно, как изменить приведенный ниже код,...


Когда я должен использовать INNER -LOOP- JOIN вместо INNER JOIN

Сегодня я узнал о том, что в SQL Server году называется INNER LOOP JOIN . Что это значит? (Google не помогает .. или я должен сказать ... сообщения в блоге об этом немного .. технические и взрывают...


Использование дополнительного WHERE в SQL INNER JOIN

$sql = SELECT events.*, leagues.lname as leagueName,leagues.aliasname as AliasName, t1.tname as team1_name, t2.tname as team2_name FROM events INNER JOIN leagues ON events.leagueid = leagues.id...


SQL: я должен использовать INNER JOIN условия как WHERE условия?

Я должен использовать INNER JOIN условия как WHERE условия? Рассмотрим эти два примера запросов, чтобы объяснить вопрос: SELECT t1.*, t2.* FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id =...


Синтаксис Postgres SQL inner join

Может ли кто-нибудь объяснить синтаксис inner join в SQL ниже: CREATE TABLE dataset AS SELECT property.id , amount.band , amount.value FROM property INNER JOIN (locality INNER JOIN amount ON...

CROSS JOIN против INNER JOIN в SQL

CROSS JOIN = (ВНУТРЕННИЙ) JOIN = запятая (",")

TL; DR Единственная разница между SQL CROSS JOIN, (INNER) JOIN и запятой (",") (кроме запятой, имеющей более низкий приоритет для порядка оценки) состоит в том, что (INNER) JOIN имеет ON, а CROSS JOIN и запятую - нет.


Повторные промежуточные продукты

Все три производят промежуточный концептуальный реляционный «декартовский» продукт в стиле SQL, то есть перекрестное соединение, всех возможных комбинаций строки из каждой таблицы. Это включено и / или ГДЕ, что уменьшает количество строк. SQL Fiddle

Стандарт SQL определяет <запятую> через продукт (7.5 1.b.ii), <перекрестное соединение> через <запятую> (7.7 1.a) и JOIN ON <условие поиска> через <запятую> плюс WHERE (7.7 1.b ).

Как говорит Википедия:

Перекрестное соединение

CROSS JOIN возвращает декартово произведение строк из таблиц в соединении. Другими словами, он будет производить строки, которые объединяют каждую строку из первой таблицы с каждой строкой из второй таблицы.

Внутреннее соединение

[...] Результат объединения может быть определен как результат первого взятия декартова произведения (или перекрестного объединения) всех записей в таблицах (объединение каждой записи в таблице A с каждой записью в таблице B) и затем возврата все записи, которые удовлетворяют предикату соединения.

В «неявной нотации объединения» просто перечисляются таблицы для объединения в предложении FROM оператора SELECT с использованием запятых для их разделения. Таким образом, он определяет перекрестное соединение

Re OUTER JOINs и использование ON против ГДЕ в них см. Условия в LEFT JOIN (ВНЕШНЕЕ СОЕДИНЕНИЕ) против INNER JOIN .

Зачем сравнивать столбцы между таблицами?

Когда нет повторяющихся строк:

Каждая таблица содержит строки, которые составляют истинный оператор из определенного шаблона оператора заполнения-заполненных-именованных-бланков. (Это делает истинное предложение из - удовлетворяет - определенный (характерный) предикат .)

  • Базовая таблица содержит строки, которые делают истинный оператор из некоторого заданного DBA шаблона оператора:

    /* rows where
    customer C.CustomerID has age C.Age and ...
    */
    FROM Customers C
  • Промежуточный продукт объединения содержит строки, которые делают истинное утверждение из AND шаблонов его операндов:

    /* 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 условия AND, чтобы дать дополнительный шаблон. Значение снова строки, которые удовлетворяют этому шаблону:

    /* rows where
        customer C.CustomerID has age C.Age and ...
    AND movie M.Movie is rented by customer M.CustomerID and ...
    AND C.CustomerID = M.CustomerID
    AND C.Age >= M.[Minimum Age]
    AND C.Age = 18
    */
    FROM Customers C INNER JOIN Movies M
    ON C.CustomerID = M.CustomerID
    AND C.Age >= M.[Minimum Age]
    WHERE C.Age = 18

В частности, сравнение столбцов для (SQL) равенства между таблицами означает, что строки, хранящиеся в продукте из частей шаблона объединенных таблиц, имеют одинаковое (не NULL) значение для этих столбцов. Это просто совпадение, что многие строки обычно удаляются путем сравнения на равенство между таблицами - что необходимо и достаточно для характеристики нужных вам строк.

Просто напишите SQL для шаблона для строк, которые вы хотите!

Значение запросов (и таблиц и условий) см. В разделе:
Как получить совпадающие данные из другой таблицы SQL для двух разных столбцов: Inner Join и / или Union?
Есть ли эмпирическое правило для построения SQL-запроса из понятного человеку описания?

Перегрузка "перекрестное соединение"

К сожалению, термин «перекрестное соединение» используется для:

  • Промежуточный продукт.
  • CROSS JOIN
  • (ВНУТРЕННИЙ) СОЕДИНИТЕ с ON или WHERE, который не сравнивает столбцы из одной таблицы с столбцами из другой. (Так как это имеет тенденцию возвращать так много промежуточных строк продукта.)

Эти различные значения смешиваются. (Например, как в других ответах и ​​комментариях здесь.)

Использование CROSS JOIN против (INNER) JOIN против запятой

Общее соглашение:

  • Используйте CROSS JOIN тогда и только тогда, когда вы не сравниваете столбцы между таблицами. Это означает, что отсутствие сравнений было преднамеренным.
  • Используйте (INNER) JOIN с ON тогда и только тогда, когда вы сравниваете столбцы между таблицами. (Плюс, возможно, другие условия.)
  • Не используйте запятую.

Как правило, условия не для пар таблиц сохраняются для WHERE. Но их, возможно, придется поместить в (n INNER) JOIN ON, чтобы получить соответствующие строки для аргумента RIGHT, LEFT или FULL (OUTER) JOIN.

Re "Не используйте запятую" Смешивание запятой с явным JOIN может ввести в заблуждение, потому что запятая имеет меньший приоритет. Но учитывая роль промежуточного продукта в значении CROSS JOIN, (INNER) JOIN и запятой, аргументы для соглашения выше о том, что он вообще не используется, шатки. Перекрестное соединение или запятая - это как (ВНУТРЕННЕЕ) соединение, находящееся в состоянии ИСТИНА. Промежуточный продукт, ON и WHERE, все вводят AND в соответствующем предикате. Тем не менее, можно думать о INNER JOIN ON - скажем, о создании выходной строки только при поиске пары входных строк, удовлетворяющих условию ON - тем не менее он возвращает строки перекрестного соединения, которые удовлетворяют условию. Единственная причина, по которой ON должен был дополнить запятую в SQL, это написать OUTERСоединения. Конечно, выражение должно прояснить его значение; но то, что ясно, зависит от того, что вещи означают.

Диаграммы Венна Диаграмма Венна с двумя пересекающимися кругами может иллюстрировать разницу между выходными строками для INNER, LEFT, RIGHT & FULL JOIN для одного и того же входа. И когда ВКЛЮЧЕНО, безусловно, ИСТИНА, результат ВНУТРЕННЕГО СОЕДИНЕНИЯ такой же, как ВРЕМЕННОЕ СОЕДИНЕНИЕ. Также он может иллюстрировать строки ввода и вывода для INTERSECT, UNION и EXCEPT. И когда оба входа имеют одинаковые столбцы, результат INTERSECT такой же, как для стандартного SQL NATURAL JOIN, а результат EXCEPT такой же, как для некоторых идиом, включающих LEFT & RIGHT JOIN. Но это не иллюстрирует, как (INNER) JOIN работает в целом. На первый взгляд это кажется правдоподобным . Он может определить детали ввода и / или вывода дляособые случаиON, PK (первичные ключи), FK (внешние ключи) и / или SELECT. Все, что вам нужно сделать, чтобы увидеть это, это определить, какие именно элементы наборов представлены кружками . (Какие запутанные представления никогда не проясняются.) (Помните, что обычно для выходных строк объединений заголовки отличаются от входных строк . А таблицы SQL - это пакеты, а не наборы строк с NULL .)

JOINS — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном материале вы узнаете, как использовать JOINS (INNER и OUTER) в Oracle с синтаксисом и примерами.

Описание

Oracle JOINS используются для извлечения данных из нескольких таблиц. JOIN выполняется всякий раз, когда две или более таблиц объединяются в SQL предложении.

Есть 4 различных типа присоединения Oracle:

Рассмотрим синтаксис Oracle JOIN, а также изучим примеры Oracle JOIN.

INNER JOIN (простое соединение)

Скорее всего, вы уже писали запросы в которых используются Oracle INNER JOIN. Это наиболее распространенный тип соединения. Oracle INNER JOINS возвращает все строки из нескольких таблиц, где выполняется условия соединения.

Синтаксис

Синтаксис INNER JOIN в Oracle/PLSQL:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

В этом рисунке, Oracle INNER JOIN возвращает затененную область:

Oracle INNER JOIN будет возвращать записи, где table1 и table2 будут пересекаться.

Пример

Ниже приведен пример Oracle INNER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

SELECT suppliers.supplier_id,

       suppliers.supplier_name,

       orders.order_date

  FROM suppliers

INNER JOIN orders

    ON suppliers.supplier_id = orders.supplier_id;

Этот пример Oracle INNER JOIN возвращает все строки из таблиц suppliers и orders, где имеются соответствующие значение поля supplier_id в обоих таблицах.

Рассмотрим некоторые данные, чтобы понять, как работает INNER JOIN:

У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA

У нас есть еще одна таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

order_id supplier_id order_date
500125 10000 05.05.2015
500126 10001 08.02.2016
500127 10004 06.01.2017

Если мы выполним Oracle оператор SELECT (который содержит INNER JOIN) ниже:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

SELECT suppliers.supplier_id,

       suppliers.supplier_name,

       orders.order_date

  FROM suppliers

INNER JOIN orders

    ON suppliers.supplier_id = orders.supplier_id;

Наш результирующий набор будет выглядеть следующим образом:

supplier_id name order_date
10000 IBM 05.05.2015
10001 Hewlett Packard 08.02.2016

Строки для Microsoft и NVIDIA из таблицы suppliers будут опущены, так как значения supplier_id 10002 и 10003 не существует в обеих таблицах. Строка order_id 500127 из таблицы orders будет опущена, так как supplier_id 10004 не существует в таблице suppliers.

Старый Синтаксис

В качестве последнего примечания, стоит отметить, что приведенный выше пример Oracle INNER JOIN можно переписать, используя старый неявный синтаксис следующим образом (но рекомендуется использовать синтаксис INNER JOIN):

SELECT suppliers.supplier_id,
suppliers.supplier_name,
orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

LEFT OUTER JOIN

Другой тип соединения называется Oracle LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.

Синтаксис

Синтаксис для Oracle LEFT OUTER JOIN:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

В некоторых базах данных LEFT OUTER JOIN заменяется на LEFT JOIN.

На этом рисунке, Oracle LEFT OUTER JOIN возвращает затененную область:

Oracle LEFT OUTER JOIN возвратит все записи из table1 и только те записи из table2, которые пересекаются с table1.

Пример

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

SELECT suppliers.supplier_id,

       suppliers.supplier_name,

       orders.order_date

  FROM suppliers

  LEFT OUTER JOIN orders

    ON suppliers.supplier_id = orders.supplier_id;

Этот пример LEFT OUTER JOIN возвратит все строки из таблицы suppliers, и только те строки из таблицы orders, где объединяемые поля равны.

Если значение supplier_id в таблице suppliers не существует в таблице orders, все поля таблицы orders будут отображаться в результирующем наборе как NULL.

Рассмотрим некоторые данные, чтобы понять, как работает LEFT OUTER JOIN:

У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA

У нас есть еще одна таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

order_id supplier_id order_date
500125 10000 05.05.2015
500126 10001 08.02.2016

Если мы выполним Oracle оператор SELECT (который содержит LEFT OUTER JOIN) ниже:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

SELECT suppliers.supplier_id,

       suppliers.supplier_name,

       orders.order_date

  FROM suppliers

  LEFT OUTER JOIN orders

    ON suppliers.supplier_id = orders.supplier_id;

Результирующий набор будет выглядеть следующим образом:

supplier_id name order_date
10000 IBM 05.05.2015
10001 Hewlett Packard 08.02.2016
10002 Microsoft null
10003 NVIDIA null

Строки для Microsoft и NVIDIA будут включены, так как был использован LEFT OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.

RIGHT OUTER JOIN

Другой тип соединения называется Oracle RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.

Синтаксис

Синтаксис Oracle RIGHT OUTER JOIN:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

В некоторых базах данных, RIGHT OUTER JOIN заменяется на RIGHT JOIN.

На этом рисунке, Oracle RIGHT OUTER JOIN возвращает затененную область:

Oracle RIGHT OUTER JOIN возвратит все записи из table2 и только те записи из table1, которые пересекаются с table2.

Пример

Ниже приведен пример Oracle RIGHT OUTER JOIN:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

SELECT orders.order_id,

       orders.order_date,

       suppliers.supplier_name

  FROM suppliers

RIGHT OUTER JOIN orders

    ON suppliers.supplier_id = orders.supplier_id;

Этот пример RIGHT OUTER JOIN возвращает все строки из таблицы orders и только те строки из таблицы suppliers, где объединяемые поля равны.

Если значение supplier_id в таблице orders не существует в таблице suppliers, все поля в таблице suppliers будут отображаться в результирующем наборе как NULL.

Рассмотрим некоторые данные, чтобы понять, как работает RIGHT OUTER JOIN:

У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name) которая содержит следующие данные:

supplier_id supplier_name
10000 Apple
10001 Google

У нас есть вторая таблица orders с тремя полями (order_id, supplier_id и order_date). Она содержит следующие данные:

order_id supplier_id order_date
500125 10000 12.05.2016
500126 10001 14.05.2016
500127 10002 18.05.2016

Если мы выполним Oracle оператор SELECT (который содержит RIGHT OUTER JOIN) ниже:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

SELECT orders.order_id,

       orders.order_date,

       suppliers.supplier_name

  FROM suppliers

RIGHT OUTER JOIN orders

    ON suppliers.supplier_id = orders.supplier_id;

Результирующий набор будет выглядеть следующим образом:

order_id order_date supplier_name
500125 12.05.2016 Apple
500126 14.05.2016 Google
500127 18.05.2016 null

Строка для order_id 500127 будет включена, так как был использован RIGHT OUTER JOINS. Тем не менее, вы заметите, что поле supplier_name для этой записи содержит значение NULL.

FULL OUTER JOIN

Другой тип соединения называется Oracle FULL OUTER JOIN. Этот тип соединения возвращает все строки из левой таблицы и правой таблицы с NULL - значениями в месте, где условие объединения не выполняется.

Синтаксис

Синтаксис для Oracle FULL OUTER JOIN:

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

В некоторых базах данных, FULL OUTER JOIN заменяются FULL JOIN.

На этом рисунке, FULL OUTER JOIN возвращает затененную область:

Oracle FULL OUTER JOIN будет возвращать все записи из обеих таблиц table1 и table2.

Пример

Ниже приведен пример Oracle FULL OUTER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers FULL OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

SELECT suppliers.supplier_id,

       suppliers.supplier_name,

       orders.order_date

  FROM suppliers

  FULL OUTER JOIN orders

    ON suppliers.supplier_id = orders.supplier_id;

Этот пример FULL OUTER JOIN возвратит все строки из таблицы suppliers и все строки из таблицы orders и всякий раз, когда условие соединения не выполняется, то поля в результирующем наборе будут принимать значения NULL.

Если значение поля supplier_id в таблице suppliers не существует в таблице orders, то все поля в таблице orders будут отображаться в результирующем наборе как NULL. Если значение supplier_id в таблице orders не существует в таблице suppliers, то все поля в таблице suppliers будут отображаться результирующем наборе как NULL .

Рассмотрим некоторые данные, чтобы понять, как работает FULL OUTER JOIN:

У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name). Она содержит следующие данные:

supplier_id supplier_name
10000 IBM
10001 Hewlett Packard
10002 Microsoft
10003 NVIDIA

У нас есть вторая таблица orders с тремя полями (order_id, supplier_id и order_date), которая содержит следующие данные:

order_id supplier_id order_date
500125 10000 12.05.2016
500126 10001 14.05.2016
500127 10004 18.05.2016

Если мы выполним Oracle оператор SELECT (который содержит FULL OUTER JOIN) ниже:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers FULL OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;

SELECT suppliers.supplier_id,

       suppliers.supplier_name,

       orders.order_date

  FROM suppliers

  FULL OUTER JOIN orders

    ON suppliers.supplier_id = orders.supplier_id;

Результирующий набор будет выглядеть следующим образом:

supplier_id supplier_name order_date
10000 IBM 12.05.2016
10001 Hewlett Packard 14.05.2016
10002 Microsoft null
10003 NVIDIA null
null null 18.05.2016

Строки для Microsoft и NVIDIA будут включены, так как используется FULL OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.

Строка для supplier_id 10004 также будет включена, так как используется FULL OUTER JOIN. Тем не менее, вы заметите, что supplier_id и поле supplier_name для этих записей содержат значение NULL.

INNER JOIN простыми словами | Techrocks

Перевод статьи «SQL INNER JOIN Explained in Simple Words».

Как известно, база данных хранит данные в таблицах. Скорее всего вы уже умеете писать запросы к одной таблице. Но что если требуется поработать с несколькими таблицами? Чтобы комбинировать данные из двух и более таблиц, нужно воспользоваться оператором JOIN. Сегодня на примерах разберемся, как работает INNER JOIN.

JOIN для двух таблиц

Наша тренировочная база данных будет состоять из двух таблиц: TRAVEL_PACK и CITIES:

Таблица TRAVEL_PACK

PACK_NAMEBEST_SEASONPRICEDURATION
United States: Big CitiesAll year$3500.0010 days
United States: West CoastMarch to May$3700.0012 days
South American TourAugust to November$1850.0018 days
Beaches of BrazilDecember to March$2200.007 days
TRAVEL_PACK

Таблица CITIES

NAMETYPE_OF_CITYPACK_NAMEDAYS_STAYING
San Francisco historicahistoricalUnited States: West Coas5
WashingtonhistoricalUnited States: Big Cities3
New YorkbusinessUnited States: Big Cities7
Rio de JaneirobeachBeaches of Brazil4
UshuaiamountainSouth American Tour3
Salvador de BahiabeachBeaches of Brazil3
Los AngelesbeachUnited States: West Coast7
CITIES

Оператор 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_NAMEBEST_SEASONPRICEDURATION
United States: Big CitiesAll year$3500.0010 days
United States: West CoastMarch to May$3700.0012 days
South American TourAugust to November$1850.0018 days
Beaches of BrazilDecember to March$2200.007 days
TRAVEL_PACK

Таблица STATES

NAME COUNTRYPOPULATIONLANGUAGE
New YorkUnited States17000000english
Tierra del FuegoArgentina190000spanish
CaliforniaUnited States13400000english
Rio de JaneiroBrasil15000000portuguese
BahiaBrasil8000000portuguese
STATES

Таблица CITIES

NAME TYPE_OF CITYPACK_NAMEDAYS_STAYSTATE
San FranciscohistoricalUnited States: West Coast5California
WashingtonhistoricalUnited States: Big Cities3Washington
New YorkbusinessUnited States: Big Cities7New York
Rio de JaneirobeachBeaches of Brazil4Rio de Janeiro
UshuaiamountainSouth American Tour3Tierra del Fuego
Salvador de BahiabeachBeaches of Brazil3Bahia
Los AngelesbeachUnited States: West Coast7California
CITIES

Запросим все туристические города из регионов, говорящих на испанском и португальском. Понятно, что нужно соединить таблицы 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')

Результат:

NAMENAMEPACK_NAMELANGUAGE
SalvadorBahiaBeaches of Brazilportuguese
Rio de JaneiroRio de JaneiroBeaches of Brazilportuguese
UshuaiaTierra del FuegoSouth American Tourspanish

Итоги
В этой статье мы рассмотрели объединение записей в SQL-таблицах. Оператор JOIN открывает перед вами множество новых возможностей в использовании SQL.


От редакции Techrocks: Если хотите изучить не только INNER JOIN, но и другие виды объединений, обратите внимание на статью “SQL JOIN: руководство по объединению таблиц”.

SQL - Оператор INNER JOINS

Наиболее важным и наиболее часто используемым из объединений является оператор INNER JOIN . Также называют эквисоединением.

INNER JOIN создает новую таблицу результатов из объединения значений столбцов из двух таблиц (table1 и table2) на основе Join-predicate. Запрос сравнивает каждую строку table1 с каждой строкой table2, чтобы найти все пары строк, удовлетворяющих Join-predicate. Когда join-predicate выполняются, значение столбцов для каждой согласованной пары рядов А и В, объединяется в строку результата.

Синтаксис

Основной синтаксис INNER JOIN следующим:

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Пример

Рассмотрим следующие две таблицы.

Таблица 1 – Таблица CUSTOMERS выглядит следующим образом:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Maxim    |  35 | Moscow    | 21000.00 |
|  2 | AndreyEx |  38 | Krasnodar | 55500.00 |
|  3 | Oleg     |  33 | Rostov    | 34000.00 |
|  4 | Masha    |  35 | Moscow    | 34000.00 |
|  5 | Ruslan   |  34 | Omsk      | 45000.00 |
|  6 | Dima     |  32 | SP        | 45000.00 |
|  7 | Roma     |  34 | SP        | 10000.00 |
+----+----------+-----+-----------+----------+

 

Таблица 2 – Таблица ORDERS выглядит следующим образом:

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2017-01-11 00:00:00 |           3 |  34000 |
| 100 | 2017-01-11 00:00:00 |           3 |  34000 |
| 101 | 2017-02-02 00:00:00 |           2 |  12500 |
| 103 | 2017-03-05 00:00:00 |           4 |  45000 |
+-----+---------------------+-------------+--------+

 

Теперь, давайте объединим эти две таблицы с помощью INNER JOIN следующим образом:

SQL> SELECT  ID, NAME, AMOUNT, DATE
   FROM CUSTOMERS
   INNER JOIN ORDERS
   ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

 

Это выведет следующий результат:

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                |
+----+----------+--------+---------------------+
|  3 | Oleg     |  34000 | 2017-01-11 00:00:00 |
|  3 | Oleg     |  34000 | 2017-01-11 00:00:00 |
|  2 | AndreyEx |  12500 | 2017-02-02 00:00:00 |
|  4 | Masha    |  45000 | 2017-03-05 00:00:00 |
+----+----------+--------+---------------------+

 

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

SQL присоединяется к


SQL ПРИСОЕДИНЯТЬСЯ

Предложение JOIN используется для объединения строк из двух или более таблиц на основе связанный столбец между ними.

Рассмотрим выборку из таблицы «Заказы»:

Код заказа Идентификатор клиента Дата заказа
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20

Затем посмотрите на выбор из таблицы «Клиенты»:

Идентификатор клиента Имя клиента ContactName Страна
1 Альфредс Футтеркисте Мария Андерс Германия
2 Ana Trujillo Emparedados y helados Ана Трухильо Мексика
3 Антонио Морено Такерия Антонио Морено Мексика

Обратите внимание, что столбец «CustomerID» в таблице «Заказы» относится к «CustomerID» в таблице «Клиенты».Связь между двумя таблицами выше столбец «CustomerID».

Затем мы можем создать следующий оператор SQL (который содержит ВНУТРЕННЕЕ СОЕДИНЕНИЕ ), который выбирает записи, которые имеют совпадающие значения в обеих таблицах:

Пример

ВЫБЕРИТЕ Orders.OrderID, Customers.CustomerName, Orders.OrderDate
ИЗ Orders
ВНУТРЕННЕЕ СОЕДИНЕНИЕ клиентов НА Orders.CustomerID = Customers.CustomerID;

Попробуй сам "

, и он выдаст что-то вроде этого:

Код заказа Имя клиента Дата заказа
10308 Ana Trujillo Emparedados y helados 18.09.1996
10365 Антонио Морено Такерия 27.11.1996
10383 Вокруг Рога 16.12.1996
10355 Вокруг Рога 15.11.1996
10278 Berglunds snabbköp 12.08.1996

Различные типы SQL JOIN

Вот различные типы JOIN в SQL:

  • (INNER) JOIN : возвращает записи, которые имеют совпадающие значения в обеих таблицах
  • LEFT (OUTER) JOIN : возвращает все записи из левой таблицы и соответствующие записи из правой таблицы
  • RIGHT (OUTER) JOIN : возвращает все записи из правой таблицы и сопоставленных записи из левой таблицы
  • ПОЛНОЕ (ВНЕШНЕЕ) СОЕДИНЕНИЕ : возвращает все записи, если есть совпадение в любом из левых или правый стол




SQL INNER JOIN: Полное руководство: Полное руководство

Оператор SQL INNER JOIN возвращает строки с точными значениями в двух столбцах двух таблиц.Вы можете присоединиться к таблице по одному или нескольким столбцам. Операторы JOIN соединяют две таблицы базы данных и сравнивают их содержимое.

Найдите свой учебный лагерь Match