Home / Questions / 36
Accepted
jachguate
Asked: 2020-11-03 07:31:51 +0800 CST 2020-11-03 07:31:51 +0800 CST

В чем разница между внутренним и внешним соединением?

  • 772

В чем разница между aи innera outer join?

И какова функция модификаторов?

  • left
  • right
  • full
mysql

3 Answers

  1. Best Answer

    Предполагая, что вы выполняете joinпроверку столбца без дубликатов, что является распространенным случаем:

    • Внутреннее соединение A и B вернет результат пересечения множеств A и B. Другими словами, внутреннюю часть — пересечение — в диаграмме Венна.

    • Полное внешнее соединение между A и B вернет результат объединения A и B. Другими словами, внешняя часть — соединение — в диаграмме Венна.

    Примеры:

    Предположим, у нас есть две таблицы с одним столбцом в каждой и следующими данными:

    A    B
-    -
1    3
2    4
3    5
4    6

    Обратите внимание, что (1,2) встречаются только в A, (3,4) распространены, а (5,6) встречаются только в B.

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

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

    select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a, b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

    Левое внешнее соединение

    A outer joinслева даст вам все строки A, включая общие строки между A и B.

    select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

    Правое внешнее соединение

    A outer joinсправа даст вам все строки B, включая общие строки с A.

    select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

    Полное внешнее соединение

    Полный outer join(полный) даст вам союз А и Б; то есть все строки в A и все строки в B. Если строка в A не имеет соответствующей строки в B, часть B равна нулю, и наоборот.

    select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    5
null |    6

    Диаграммы Венна

    То же самое мы можем видеть и с диаграммами Венна:

    Визуальное объяснение соединений Изображение SQL Joins.svg от Arbeck , опубликовано под лицензией CC BY 3.0 .

    • 196

  2. Я собираюсь использовать тот же пример jachguate, который очень нагляден, добавив несколько мелких деталей.

    Глоссарий

    • внутреннее соединение (вольный перевод: внутреннее соединение)
    • внешнее соединение (вольный перевод: внешнее соединение)

    введите описание изображения здесь

    -- Crear tabla A (tabla Izquierda)
CREATE TABLE A
(
a INT
);

-- Crear tabla B (tabla derecha)
CREATE TABLE B
(
b INT
);

-- Insertar datos
Insert into A (a) Values (1);
Insert into A (a) Values (2);
Insert into A (a) Values (3);
Insert into A (a) Values (4);
Insert into B (b) Values (3);
Insert into B (b) Values (4);
Insert into B (b) Values (5);
Insert into B (b) Values (6);
COMMIT;

-- Tabla A
SELECT * FROM A;

-- Tabla B
SELECT * FROM B;

/* Inner Join. */
-- Unión interna, filas que ambas tablas tienen en común.
select * from A INNER JOIN B on A.a = B.b;
select A.*, B.*  from A, B where A.a = B.b;

/* Left outer join */
-- Unión externa por la izquierda, todas las filas de A (tabla izquierda) relacionadas con B, así estas tengan o no coincidencias.
select * from A LEFT OUTER JOIN B on A.a = B.b;
select A.*,B.*  from A,B where A.a = B.b(+);

/* Right outer join */
-- Unión externa por la derecha, todas las filas de B (tabla derecha), así estas tengan o no coincidencias con A.
select * from A RIGHT OUTER JOIN B on A.a = B.b;
select A.*,B.*  from A,B where A.a(+) = B.b;

/* Full outer join */
-- Unión externa completa, unión externa por la izquierda unida a unión externa por la derecha. 

-- En oracle:
select * from A FULL OUTER JOIN B on A.a = B.b;

-- En MySql no está implementado FULL OUTER JOIN, para obtener este mismo resultado:

select * from A LEFT OUTER JOIN B on A.a = B.b
UNION 
select * from A RIGHT OUTER JOIN B on A.a = B.b;

    введите описание изображения здесь

    Смотреть:

    • 44

  3. Мои данные для объяснения:

    
CREATE TABLE Departamentos (
    Id int,
    Nombre varchar(20)
);

CREATE TABLE Empleados (
    Nombre varchar(20),
    DepartamentoId int
);

INSERT INTO Departamentos VALUES(31, 'Sales'),
                                (33, 'Engineering'),
                                (34, 'Clerical'),
                                (35, 'Marketing');

INSERT INTO Empleados VALUES('Rafferty', 31),
                            ('Jones', 33),
                            ('Heisenberg', 33),
                            ('Robinson', 34),
                            ('Smith', 34),
                            ('Williams', NULL);

    Ejecutar

    ВНУТРЕННЕЕ СОЕДИНЕНИЕ

    Это предложение ищет соответствие между двумя таблицами, оно похоже на объединение таблиц с первичным ключом, которое использовалось в старые времена, напримерSELECT * FROM Departamentos, Empleados WHERE Departamentos.Id=Empleados.DepartamentosId;

    введите описание изображения здесь

    Например: Если я хочу перечислить сотрудников и указать название отдела, к которому они принадлежат, мы можем сделать следующее:

    SELECT * 
FROM Empleados E
JOIN Departamentos D
ON E.DepartamentoId = D.Id

    Ejecutar

    Результат будет:

    +----------+------------------+---------+----------------+
|Nombre    |   DepartmentoId  |   Id    |   Nombre       |
+----------+------------------+---------+----------------+
|Rafferty  |        31        |    31   |    Sales       |
|Jones     |        33        |    33   |    Engineering |
|Heisenberg|        33        |    33   |    Engineering |
|Robinson  |        34        |    34   |    Clerical    |
|Smith     |        34        |    34   |    Clerical    |
+----------+------------------+---------+----------------+

    И отсюда мы понимаем следующее:

    • Сотрудник «Уильямс» не отображается в результатах, так как он не принадлежит ни к одному из существующих в настоящее время отделов.

    • Отдел "Маркетинг" также не появляется, так как ни один сотрудник не принадлежит этому отделу.

    Почему это происходит? Потому что он показывает в результате пересечение обеих таблиц.

    Имейте в виду, что в результатах мы видим 4 столбца. Первые 2 соответствуют таблице «Сотрудники», а последние — отделам.

    Это происходит потому, что мы выбираем все столбцы с помощью звездочки (*).

    Если мы хотим, мы можем быть конкретными и выбрать только 2 столбца:

    SELECT E.Nombre as 'Empleado',
D.Nombre as 'Departamento'
FROM Empleados E
JOIN Departamentos D
ON E.DepartamentoId = D.Id

    И получить :

    +-----------+-----------------+
| Empleado  |   Departamento  |
+-----------+-----------------+
| Rafferty  |    Sales        |
| Jones     |    Engineering  |
| Heisenberg|    Engineering  |
| Robinson  |    Clerical     |
| Smith     |    Clerical     |
+-----------+-----------------+

    Ejecutar

    ЛЕВОЕ СОЕДИНЕНИЕ

    В отличие от INNER JOIN с LEFT JOIN мы отдаем приоритет левой таблице и ищем правую таблицу.

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

    введите описание изображения здесь

    Пример:

    SELECT E.Nombre as 'Empleado', D.Nombre as 'Departamento'
FROM Empleados E
LEFT JOIN Departamentos D
ON E.DepartamentoId = D.Id

    • Таблица «Сотрудники» — это первая таблица, которая появляется в запросе (в FROM), поэтому это ЛЕВАЯ таблица, и все ее строки будут отображаться в результатах.

    • Таблица «Отделы» — это таблица справа (она появляется после LEFT JOIN). Поэтому, если совпадения будут найдены, будут отображены соответствующие значения, иначе в результатах будет отображаться NULL.

    +------------+---------------+
| Empleado   |  Departamento |
+------------+---------------+
| Rafferty   |  Sales        |
| Jones      |  Engineering  |
| Heisenberg |  Engineering  |
| Robinson   |  Clerical     |
| Smith      |  Clerical     |
| Williams   |  NULL         |
+------------+---------------+

    Ejecutar

    ПРАВИЛЬНОЕ СОЕДИНЕНИЕ

    В случае RIGHT JOIN все очень похоже, но здесь приоритет отдается таблице справа.

    введите описание изображения здесь

    Итак, если я использую следующий запрос, мы отображаем все строки из таблицы справа:

    SELECT E.Nombre as 'Empleado',D.Nombre as 'Departamento'
FROM Empleados E
RIGHT JOIN Departamentos D
ON E.DepartamentoId = D.Id

    Ejecutar

    La tabla de la izquierda es Empleados, mientras que Departamentos es la tabla de la derecha.

    La tabla asociada al FROM será siempre la tabla LEFT, y la tabla que viene después del JOIN será la tabla RIGHT.

    Entonces el resultado mostrará todos los departamentos al menos 1 vez.

    Si no hay ningún empleado trabajando en un departamento, se mostrará NULL. Pero el departamento aparecerá de todo modos.

    +------------+-----------------+
| Empleado   |  Departamento   |
+------------+-----------------+
| Rafferty   |   Sales         |
| Jones      |   Engineering   |
| Heisenberg |   Engineering   |
| Robinson   |   Clerical      |
| Smith      |   Clerical      |
| NULL       |   Marketing     |
+------------+-----------------+

    Cláusula FULL JOIN

    FULL JOIN es una mezcla entre LEFT JOIN y RIGHT JOIN ya que se muestra ambas tablas.

    введите описание изображения здесь

    En el ejemplo, ocurre lo siguiente:

    SELECT E.Nombre as 'Empleado',D.Nombre as 'Departamento'
FROM Empleados E
FULL JOIN Departamentos D
ON E.DepartamentoId = D.Id

    Pero todavía no esta implementado desgraciadamente en MySQL, mientras tanto podemos unir LEFT JOIN y RIGHT JOIN para obtener FULL JOIN. Por lo que una manera similar de realizarlo seria de la siguiente forma :

    SELECT E.Nombre as 'Empleado',
  D.Nombre as 'Departamento'
  FROM Empleados E
  LEFT JOIN Departamentos D
  ON E.DepartamentoId = D.Id
UNION
SELECT E.Nombre as 'Empleado',
  D.Nombre as 'Departamento'
  FROM Empleados E
  RIGHT JOIN Departamentos D
  ON E.DepartamentoId = D.Id;

    Se muestra el empleado "Williams" a pesar que no está asignado a ningún departamento, y se muestra el departamento de "Marketing" a pesar que aún nadie está trabajando allí :

    +------------+----------------+
| Empleado   |  Departamento  |
+------------+----------------+
| Rafferty   |  Sales         |
| Jones      |  Engineering   |
| Heisenberg |  Engineering   |
| Robinson   |  Clerical      |
| Smith      |  Clerical      |
| Williams   |  NULL          |
| NULL       |  Marketing     |
+------------+----------------+

    Ejecutar

    Más variantes

    Si prestamos atención a los diagramas de Venn, vamos a notar que es posible formar incluso más combinaciones, al momento de seleccionar datos.

    Por ejemplo, tenemos el siguiente caso, conocido como Left Excluding JOIN:

    введите описание изображения здесь

    Y de igual manera Right Excluding JOIN:

    введите описание изображения здесь

    Estas combinaciones son posibles de lograr si añadimos algunas condiciones a nuestras consultas, haciendo uso de la cláusula WHERE.

    Por ejemplo, siguiendo el ejemplo que estamos viendo (donde Empleados es la tabla izquierda y Departamentos la tabla derecha):

    • Left Excluding JOIN nos permitirá obtener la lista de empleados que aún no han sido asignados a ningún departamento de trabajo.

    • В то время как Right Excluding JOIN покажет нам список отделов, у которых нет связанных работников.

    Информация взята с: https://programacionymas.com/blog/como-carga-inner-left-right-full-join

    • 2