SQLite JOIN

Прежде чем продолжить, предположим, что есть две таблицы: COMPANY и DEPARTMENT. Мы уже видели операторы INSERT, использованные для заполнения таблицы COMPANY. Теперь предположим, что список записей в таблице COMPANY выглядит следующим образом:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Другая таблица — DEPARTMENT, определённая следующим образом:

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Ниже приведены операторы INSERT для заполнения таблицы DEPARTMENT:

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

Наконец, у нас есть следующий список записей в таблице DEPARTMENT:

ID          DEPT        EMP_ID
----------  ----------  ----------
1           IT Billing  1
2           Engineerin  2
3           Finance     7

Перекрестное соединение - CROSS JOIN

CROSS JOIN сопоставляет каждую строку первой таблицы с каждой строкой второй таблицы. Если две входные таблицы содержат соответственно x и y строк, результирующая таблица содержит x*y строк. Поскольку CROSS JOIN может создавать очень большие таблицы, его следует использовать осторожно и только когда это уместно.

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

Ниже приведён синтаксис CROSS JOIN:

SELECT ... FROM table1 CROSS JOIN table2 ...

На основе приведённых выше таблиц мы можем записать CROSS JOIN следующим образом:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

Приведённый выше запрос даст следующий результат:

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Paul        Engineerin
7           Paul        Finance
1           Allen       IT Billing
2           Allen       Engineerin
7           Allen       Finance
1           Teddy       IT Billing
2           Teddy       Engineerin
7           Teddy       Finance
1           Mark        IT Billing
2           Mark        Engineerin
7           Mark        Finance
1           David       IT Billing
2           David       Engineerin
7           David       Finance
1           Kim         IT Billing
2           Kim         Engineerin
7           Kim         Finance
1           James       IT Billing
2           James       Engineerin
7           James       Finance

Внутреннее соединение - INNER JOIN

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

INNER JOIN — наиболее распространённый тип соединения и тип соединения по умолчанию. Ключевое слово INNER необязательно.

Ниже приведён синтаксис INNER JOIN:

SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...

Чтобы избежать избыточности и сократить формулировку, можно использоватьUSINGвыражение, чтобы объявить условие INNER JOIN. Это выражение задаёт список из одного или нескольких столбцов:

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...

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

SELECT ... FROM table1 NATURAL JOIN table2...

На основе приведенных выше таблиц мы можем записать INNER JOIN следующим образом:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

Приведённый выше запрос даст следующий результат:

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineerin
7           James       Finance

Внешнее соединение - OUTER JOIN

OUTER JOIN является расширением INNER JOIN. Хотя стандарт SQL определяет три типа внешних соединений: LEFT, RIGHT и FULL, SQLite поддерживает толькоLEFT OUTER JOIN。

Способ, которым OUTER JOIN объявляет условия, такой же, как в INNER JOIN, с использованием ключевых слов ON, USING или NATURAL. Начальная таблица результатов вычисляется таким же образом. Как только основной расчет соединения завершен, OUTER JOIN добавляет любые несоединенные строки из одной или обеих таблиц, используя значения NULL для столбцов внешнего соединения, и присоединяет их к таблице результатов.

Ниже приведен синтаксис LEFT OUTER JOIN:

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

Чтобы избежать избыточности и сделать формулировку короче, вы можете использоватьUSINGвыражение для объявления условия OUTER JOIN. Это выражение задает список из одного или нескольких столбцов:

SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...

На основе приведенных выше таблиц мы можем записать OUTER JOIN следующим образом:

sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

Приведённый выше запрос даст следующий результат:

EMP_ID      NAME        DEPT
----------  ----------  ----------
1           Paul        IT Billing
2           Allen       Engineerin
            Teddy
            Mark
            David
            Kim
7           James       Finance

Другие расширения