Вопросы Теги

Три соединения таблицы с соединениями кроме ВНУТРЕННЕГО ОБЪЕДИНЕНИЯ

Python:

  • скорость
  • статический анализ (отсутствие)
  • анонимные функции, ограниченные одним выражением
5
задан MedicineMan 11 August 2009 в 20:52
поделиться

8 ответов

Да, я использую все три из этих JOIN, хотя я предпочитаю использовать только LEFT (OUTER) JOIN вместо смешивания LEFT и RIGHT JOIN. . Я также использую FULL OUTER JOIN s и CROSS JOIN s.

Таким образом, INNER JOIN ограничивает набор результатов только теми записями, которым удовлетворяет JOIN состояние. Рассмотрим следующие таблицы

РЕДАКТИРОВАТЬ: Я переименовал имена таблиц и снабдил их префиксом @ , чтобы переменные таблицы можно было использовать для всех, кто читает этот ответ и хочет поэкспериментировать.

Если вы также хотите поэкспериментировать с этим в браузере, Я настроил все это и на SQL Fiddle ; 

@Table1

id | name
---------
1  | One
2  | Two
3  | Three
4  | Four

@Table2

id | name
---------
1  | Partridge
2  | Turtle Doves
3  | French Hens
5  | Gold Rings

Код SQL 

DECLARE @Table1 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25))

INSERT INTO @Table1 VALUES(1, 'One');
INSERT INTO @Table1 VALUES(2, 'Two');
INSERT INTO @Table1 VALUES(3, 'Three');
INSERT INTO @Table1 VALUES(4, 'Four');

DECLARE @Table2 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25))

INSERT INTO @Table2 VALUES(1, 'Partridge');
INSERT INTO @Table2 VALUES(2, 'Turtle Doves');
INSERT INTO @Table2 VALUES(3, 'French Hens');
INSERT INTO @Table2 VALUES(5, 'Gold Rings');

INNER JOIN SQL Утверждение, объединено в поле id 

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
INNER JOIN
    @Table2 t2
    ON 
        t1.id = t2.id

Результаты в 

id | name | name
----------------
1  | One  | Partridge
2  | Two  | Turtle Doves
3  | Three| French Hens

A LEFT JOIN вернет набор результатов со всеми записями из таблицы в левой части соединения (если вы чтобы записать оператор как однострочник, таблица, которая появляется первой) и поля из таблицы с правой стороны соединения, которые соответствуют выражению соединения и включены в предложение SELECT . Отсутствующие данные будут заполнены NULL 

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
LEFT JOIN
    @Table2 t2
    ON 
        t1.id = t2.id

Результаты в 

id | name | name
----------------
1  | One  | Partridge
2  | Two  | Turtle Doves
3  | Three| French Hens
4  | Four | NULL

A RIGHT JOIN - та же логика, что и LEFT JOIN , но вернет все записи из правая часть соединения и поля с левой стороны, которые соответствуют выражению соединения и включены в предложение SELECT . 

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
RIGHT JOIN
    @Table2 t2
    ON 
        t1.id = t2.id

Результаты в 

id | name | name
----------------
1  | One  | Partridge
2  | Two  | Turtle Doves
3  | Three| French Hens
NULL| NULL| Gold Rings

Конечно, есть также ] ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ , который включает записи из обеих объединенных таблиц и заполняет любые отсутствующие детали с помощью NULL. 

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
FULL OUTER JOIN
    @Table2 t2
    ON 
        t1.id = t2.id

Результат в 

id | name | name
----------------
1  | One  | Partridge
2  | Two  | Turtle Doves
3  | Three| French Hens
4  | Four | NULL
NULL| NULL| Gold Rings

И CROSS JOIN (также известный как КАРТСИЙСКИЙ ПРОДУКТ ), который является просто произведением перекрестного применения полей в операторе SELECT из одной таблицы с полями в операторе SELECT из другой таблицы. Обратите внимание, что в CROSS JOIN 

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
CROSS JOIN
    @Table2 t2

нет выражения соединения. Результаты в 

id | name  | name
------------------
1  | One   | Partridge
2  | Two   | Partridge
3  | Three | Partridge
4  | Four  | Partridge
1  | One   | Turtle Doves
2  | Two   | Turtle Doves
3  | Three | Turtle Doves
4  | Four  | Turtle Doves
1  | One   | French Hens
2  | Two   | French Hens
3  | Three | French Hens
4  | Four  | French Hens
1  | One   | Gold Rings
2  | Two   | Gold Rings
3  | Three | Gold Rings
4  | Four  | Gold Rings

РЕДАКТИРОВАТЬ:

Представьте, что теперь есть таблица3 

@Table3

id | name
---------
2  | Prime 1
3  | Prime 2
5  | Prime 3

Код SQL 

DECLARE @Table3 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25))

INSERT INTO @Table3 VALUES(2, 'Prime 1');
INSERT INTO @Table3 VALUES(3, 'Prime 2');
INSERT INTO @Table3 VALUES(5, 'Prime 3');

Теперь все три таблицы объединены с помощью INNER JOINS 

SELECT 
    t1.id,
    t1.name,
    t2.name,
    t3.name
FROM
    @Table1 t1
INNER JOIN
    @Table2 t2
    ON 
        t1.id = t2.id
INNER JOIN
    @Table3 t3
    ON 
        t1.id = t3.id

Результаты в 

id | name | name         | name
-------------------------------
2  | Two  | Turtle Doves | Prime 1
3  | Three| French Hens  | Prime 2

Это может помочь понять этот результат, если подумать, что записи с идентификаторами 2 и 3 являются единственными общими для всех трех таблиц и также являются полем, к которому мы присоединяемся каждая таблица включена.

Теперь все три с LEFT JOINS 

SELECT 
    t1.id,
    t1.name,
    t2.name,
    t3.name
FROM
    @Table1 t1
LEFT JOIN
    @Table2 t2
    ON 
        t1.id = t2.id
LEFT JOIN
    @Table3 t3
    ON 
        t1.id = t3.id

Результаты в 

id | name | name         | name
-------------------------------
1  | One  | Partridge    | NULL
2  | Two  | Turtle Doves | Prime 1
3  | Three| French Hens  | Prime 2
4  | Four | NULL         | NULL

Ответ Джоэла является хорошим объяснением этого набора результатов (Таблица1 - это таблица базы / происхождения).

Теперь с INNER JOIN и LEFT JOIN 

SELECT 
    t1.id,
    t1.name,
    t2.name,
    t3.name
FROM
    @Table1 t1
INNER JOIN
    @Table2 t2
    ON 
        t1.id = t2.id
LEFT JOIN
    @Table3 t3
    ON 
        t1.id = t3.id

Результаты в 

id | name | name         | name
-------------------------------
1  | One  | Partridge    | NULL
2  | Two  | Turtle Doves | Prime 1
3  | Three| French Hens  | Prime 2

Хотя мы не знаем порядок, в котором оптимизатор запросов будет выполнять операции, мы рассмотрим этот запрос сверху вниз. понять набор результатов. INNER JOIN на идентификаторах между Table1 и Table2 ограничит набор результатов только теми записями, которые удовлетворяются условием соединения, то есть тремя строками, которые мы видели в самом первом примере. Этот временный набор результатов будет затем LEFT JOIN привязан к Table3 по идентификаторам между Table1 и Tables; В Table3 есть записи с идентификаторами 2 и 3, но не с идентификатором 1, поэтому t3.

22
ответ дан 18 December 2019 в 05:44
поделиться

Выбор из трех таблиц ничем не отличается от выбора из двух (или целых сотен, хотя это было бы довольно уродливым запросом для чтения).

Для КАЖДОГО соединения вы пишете , наличие INNER указывает на то, что вам нужны только строки, которые успешно соединяют эти две таблицы вместе. Если другие таблицы были объединены ранее в запросе, эти результаты теперь совершенно неактуальны, за исключением тех случаев, когда их вызывают ваши собственные условия соединения.

Например: 

SELECT person.*
FROM person
LEFT JOIN vehicle ON (person.person_id = vehicle.owner_id)
LEFT JOIN house ON (person.person_id = house.owner_id)

Здесь мне нужен список всех людей и (если доступны) все автомобили и дома, которыми они владеют.

В качестве альтернативы: 

SELECT person.*
FROM person
INNER JOIN vehicle ON (person.person_id = vehicle.owner_id)
LEFT JOIN house ON (person.person_id = house.owner_id)

Здесь я хочу, чтобы все люди, владеющие транспортными средствами (они должны владеть транспортными средствами, чтобы получить результаты по моему запросу), и (если доступно ) все дома, которыми они владеют).

Здесь каждое соединение полностью отделено.

Конечно,

2
ответ дан 18 December 2019 в 05:44
поделиться

Объединения - это просто способы объединения таблиц. Объединение трех таблиц ничем не отличается от объединения 2 ... или 200. Вы можете смешивать и сопоставлять INNER, [LEFT / RIGHT / FULL] OUTER и даже CROSS в любом количестве. Единственная разница заключается в том, какие результаты сохраняются: INNER соединения сохраняют только строки, обе стороны которых соответствуют выражению. Соединения OUTER выбирают "исходную" таблицу в зависимости от спецификации LEFT / RIGHT / FULL, всегда сохраняют все строки из исходной таблицы и предоставляют значения NULL для строк с другой стороны, которые не соответствуют выражению. Соединения CROSS возвращают все возможные комбинации обеих сторон.

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

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

Итак, с тремя таблицами вы начинаете со своей базовой таблицы, затем присоединяете значения, которые вам нужны, из следующей таблицы, следующей и так далее, точно так же, как добавление строк кода функции для получения требуемого вывода.

Что касается использования различных типов соединения, я использовал все перечисленные здесь типы: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER и CROSS. Но большинство из них нужно использовать только изредка. INNER JOIN и LEFT JOIN охватят, вероятно, 95% или больше того, что вы хотите сделать.

Теперь поговорим о производительности. Часто порядок, в котором вы перечисляете таблицы, диктуется вам: вы начинаете с TableA , и вам нужно сначала указать TableB , чтобы иметь доступ к столбцам, необходимым для присоединения к TableC . Но иногда и TableB , и TableC зависят только от TableA , и вы можете перечислить их в любом порядке. Когда это происходит, оптимизатор запросов обычно выбирает для вас лучший порядок, но иногда не знает, как это сделать. Даже если бы это было так, полезно иметь хорошую систему для перечисления таблиц, чтобы вы всегда могли посмотреть на запрос и знать, что он «правильный».

Имея это в виду, вы должны думать о запросе в терминах рабочего набора , который в настоящее время находится в памяти в процессе построения запроса. Когда вы начинаете с TableA , база данных просматривает все столбцы из TableA в списке выбора или где-либо еще (например, предложения WHERE или ORDER BY или потенциальные индексы) в запросе, факторы в соответствующих условиях из предложения WHERE и загружают в память самую маленькую часть этой таблицы, которая может сойти с рук. Он делает это по очереди для каждой таблицы, всегда загружая как можно меньше. И это ключ: вы хотите, чтобы этот рабочий набор был как можно меньшим как можно дольше.

Итак, возвращаясь к нашему объединению трех таблиц, мы хотим перечислить таблицы в том порядке, в котором будет сохраняться рабочий установите меньше на более длительный срок. Это означает перечисление меньшей таблицы над большей. Еще одно хорошее практическое правило: соединения INNER имеют тенденцию сжимать наборы результатов, в то время как соединения OUTER имеют тенденцию к увеличению наборов результатов, поэтому вы хотите сначала перечислить свои INNER соединения. Однако это не требование для работы запроса и не всегда верно; иногда может произойти и обратное.

Наконец, я хочу еще раз отметить, что на самом деле это не так. Оптимизатор запросов и планы выполнения - это очень сложная тема, и существует множество уловок, которые база данных может использовать, время от времени нарушая эту модель. Это всего лишь одна модель, которую вы, как разработчик, можете использовать, чтобы понять, что делает сервер, и помочь вам лучше писать запросы.

Я хочу еще раз отметить, что на самом деле это не так. Оптимизатор запросов и планы выполнения - это очень сложная тема, и существует множество уловок, которые база данных может использовать, время от времени нарушая эту модель. Это всего лишь одна модель, которую вы, как разработчик, можете использовать, чтобы понять, что делает сервер, и помочь вам лучше писать запросы.

Я хочу еще раз отметить, что на самом деле это не так. Оптимизатор запросов и планы выполнения - это очень сложная тема, и существует множество уловок, которые база данных может использовать, время от времени нарушая эту модель. Это всего лишь одна модель, которую вы, как разработчик, можете использовать, чтобы понять, что делает сервер, и помочь вам лучше писать запросы.

6
ответ дан 18 December 2019 в 05:44
поделиться

Это действительно зависит от того, что вы делаете. Я написал много запросов к таблицам 3+, которые будут иметь внешнее соединение. Это просто зависит от данных, которые вы запрашиваете, и от того, что вы пытаетесь выполнить.

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

0
ответ дан 18 December 2019 в 05:44
поделиться

Допустим, у нас есть таблица «сотрудники» с полями ID, NAME и MANAGER_ID.

Вот простой запрос : 

SELECT E.ID, E.NAME, M.NAME AS MANAGER
FROM EMPLOYEES E
JOIN EMPLOYEE M ON E.MANAGER_ID = M.ID

Это вернет всех сотрудников с именами их руководителей. Но что происходит с боссом? у кого нет менеджера? Нулевое значение базы данных фактически предотвратит возврат этой строки, поскольку она не может найти соответствующую запись для присоединения. Таким образом, вы должны использовать ВНЕШНЕЕ соединение (левое или правое, в зависимости от того, как вы пишете запрос).

Та же самая логика применима для написания запроса с 2 + n соединениями. Если вы, возможно, собираетесь иметь строки, которым нет совпадений в вашем предложении соединения, и хотите, чтобы эти строки возвращались (хотя и с нулями), тогда вы золотой.

0
ответ дан 18 December 2019 в 05:44
поделиться

О некоторых sql engine возникает проблема, когда вы присоединяетесь к левому соединению. Если вы присоединяетесь к A-> B-> C, а строка в B не существует, тогда столбец соединения из B равен NULL. Некоторые из тех, что я использовал, требуют, чтобы соединение от B-> C было левым, если соединение от A-> B является левым.

Это нормально 

select a.*, b.*, c.*
 from a
 left join b on b.id = a.id
 left join c on c.id = b.id

это не 

 select a.*, b.*, c.*
 from a
 left join b on b.id = a.id
 inner join c on c.id = b.id
0
ответ дан 18 December 2019 в 05:44
поделиться

Для ради полноты и стандартной проповеди, я воспользуюсь синтаксисом вложенного соединения ansi-92: 

select t1.*
    ,t2.*
    ,t3.*
from table1 t1
    left outer join (
        table2 t2 left outer join table3 t3 on (t2.b = t3.b)
    ) on (t1.a = t2.a)

Выбранный вами механизм SQL может оптимизировать для них.

0
ответ дан 18 December 2019 в 05:44
поделиться
Другие вопросы по тегам:

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