SQLite Соединение: естественное левое внешнее, внутреннее, перекрестное с примером таблиц

SQLite поддерживает различные типы SQL Соединения, такие как INNER JOIN, LEFT OUTER JOIN и CROSS JOIN. Каждый тип JOIN используется для разных ситуаций, как мы увидим в этом уроке.

Введение в SQLite Предложение ПРИСОЕДИНЯЙТЕСЬ

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

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

Любое предложение JOIN должно иметь следующий синтаксис:

Каждое предложение соединения содержит:

Таблица или подзапрос, который является левой таблицей; таблица или подзапрос перед предложением соединения (слева от него).
Оператор JOIN – укажите тип соединения (INNER JOIN, LEFT OUTER JOIN или CROSS JOIN).
Ограничение JOIN — после того, как вы указали таблицы или подзапросы для объединения, вам необходимо указать ограничение соединения, которое будет условием, при котором в зависимости от типа соединения будут выбраны совпадающие строки, соответствующие этому условию.

Обратите внимание, что для всех следующих SQLite Для примеров таблиц JOIN необходимо запустить sqlite3.exe и открыть соединение с образцом базы данных следующим образом:

Шаг 1) На этом этапе

Откройте «Мой компьютер» и перейдите в следующий каталог «C:\sqlite" а также
Затем откройте «sqlite3.exe«:

Шаг 2) Откройте базу данных»УчебникиSampleDB.db» следующей командой:

Теперь вы готовы выполнить любой тип запроса к базе данных.

SQLite INNER JOIN

INNER JOIN возвращает только те строки, которые соответствуют условию соединения, и исключает все остальные строки, которые не соответствуют условию соединения.

Пример

В следующем примере мы объединим две таблицы «Студенты(Основной ключ) и Департаменты» с DepartmentId, чтобы получить название факультета для каждого студента, следующим образом:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Объяснение кода

ВНУТРЕННЕЕ СОЕДИНЕНИЕ работает следующим образом:

В предложении Select вы можете выбрать любые столбцы из двух связанных таблиц.
Предложение INNER JOIN записывается после первой таблицы, на которую ссылается предложение From.
Затем условие соединения указывается с помощью ON.
Для ссылочных таблиц можно указать псевдонимы.
Слово INNER не является обязательным, вы можете просто написать JOIN.

Результат

INNER JOIN создает записи как из таблиц студентов, так и из таблиц кафедры, которые соответствуют условию: «Students.DepartmentId = Departments.DepartmentId «. Несовпадающие строки будут игнорироваться и не включаться в результат.
Вот почему из 8 студентов по этому запросу с факультетов ИТ, математики и физики были возвращены только 10 студентов. А студенты «Йена» и «Джордж» не были включены, поскольку у них есть нулевой идентификатор отдела, который не соответствует столбцу «Идентификатор отдела» в таблице отделов. Как следующее:

SQLite ПРИСОЕДИНЯЙТЕСЬ… ИСПОЛЬЗУЙТЕ

INNER JOIN можно записать с использованием предложения «USING», чтобы избежать избыточности, поэтому вместо записи «ON Student.DepartmentId = Departments.DepartmentId» вы можете просто написать «USING(DepartmentID)».

Вы можете использовать «JOIN .. USING», когда столбцы, которые вы будете сравнивать в условии соединения, имеют одно и то же имя. В таких случаях нет необходимости повторять их с использованием условия on, а просто указать имена столбцов и SQLite это обнаружит.

Разница между INNER JOIN и JOIN. ИСПОЛЬЗОВАНИЕ:

С помощью «JOIN… USING» вы не пишете условие соединения, вы просто пишете столбец соединения, который является общим для двух объединенных таблиц, вместо того, чтобы писать table1 «INNER JOIN table2 ON table1.cola = table2.cola», мы пишем это похоже на «table1 JOIN table2 USING (cola)».

Пример

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments USING(DepartmentId);

объяснение

В отличие от предыдущего примера, мы не писали «ON Студенты.DepartmentId = Departments.DepartmentId«. Мы только что написали: «ИСПОЛЬЗОВАНИЕ(идентификатор отдела)».
SQLite автоматически выводит условие соединения и сравнивает DepartmentId из обеих таблиц – Students и Departments.
Вы можете использовать этот синтаксис, когда два сравниваемых столбца имеют одно и то же имя.

Результат

Это даст вам тот же результат, что и в предыдущем примере:

SQLite ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ

ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ аналогично JOIN…USING, разница в том, что оно автоматически проверяет равенство значений каждого столбца, существующего в обеих таблицах.

Разница между INNER JOIN и ЕСТЕСТВЕННЫМ СОЕДИНЕНИЕМ:

В INNER JOIN вам необходимо указать условие соединения, которое внутреннее соединение использует для соединения двух таблиц. В то время как при естественном соединении вы не пишете условие соединения. Вы просто пишете имена двух таблиц без каких-либо условий. Затем естественное соединение автоматически проверит равенство значений для каждого столбца в обеих таблицах. Естественное соединение автоматически выводит условие соединения.
В ЕСТЕСТВЕННОМ СОЕДИНЕНИИ все столбцы из обеих таблиц с одинаковым именем будут сопоставлены друг с другом. Например, если у нас есть две таблицы с двумя общими именами столбцов (два столбца существуют с одинаковым именем в двух таблицах), то естественное соединение объединит две таблицы путем сравнения значений обоих столбцов, а не только одного. столбец.

Пример

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
Natural JOIN Departments;

объяснение

Нам не нужно писать условие соединения с именами столбцов (как мы это делали в INNER JOIN). Нам даже не пришлось один раз писать имя столбца (как это было в JOIN USING).
Естественное соединение сканирует оба столбца из двух таблиц. Он обнаружит, что условие должно состоять из сравнения DepartmentId из двух таблиц «Студенты» и «Отделы».

Результат

Natural JOIN даст вам тот же результат, что и результат, который мы получили в примерах INNER JOIN и JOIN USING. Потому что в нашем примере все три запроса эквивалентны. Но в некоторых случаях выходные данные будут отличаться от внутреннего соединения и от естественного соединения. Например, если существует больше таблиц с одинаковыми именами, то естественное соединение сопоставит все столбцы друг с другом. Однако внутреннее соединение будет соответствовать только столбцам в условии соединения (подробнее об этом в следующем разделе; разница между внутренним соединением и естественным соединением).

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

Стандарт SQL определяет три типа ВНЕШНИХ СОЕДИНЕНИЙ: LEFT, RIGHT и FULL, но SQLite поддерживает только естественное LEFT OUTER JOIN.

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

Таким образом, если в левой таблице есть n строк, результаты запроса будут содержать n строк. Однако для значений столбцов из правой таблицы, если какое-либо значение не соответствует условию соединения, оно будет содержать «нулевое» значение.

Таким образом, вы получите количество строк, эквивалентное количеству строк в левом соединении. Таким образом, вы получите совпадающие строки из обеих таблиц (например, результаты INNER JOIN), а также несовпадающие строки из левой таблицы.

Пример

В следующем примере мы попробуем использовать «LEFT JOIN» для объединения двух таблиц «Студенты» и «Отделы»:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students             -- this is the left table
LEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

объяснение

SQLite Синтаксис LEFT JOIN аналогичен INNER JOIN; вы пишете LEFT JOIN между двумя таблицами, а затем условие соединения идет после предложения ON.
Первая таблица после предложения from — это левая таблица. Тогда как вторая таблица, указанная после естественного LEFT JOIN, является правой таблицей.
Предложение OUTER не является обязательным; LEFT natural OUTER JOIN аналогичен LEFT JOIN.

Результат

Как вы можете видеть, включены все строки из таблицы студентов, всего 10 студентов. Даже если идентификаторы отделов четвертого и последнего студента, Джены и Джорджа, не существуют в таблице «Отделы», они также включаются.
И в этих случаях значение DepartmentName как для Джены, так и для Джорджа будет «нулевым», поскольку в таблице DepartmentName нет значения DepartmentName, соответствующего их значению DepartmentId.

Давайте дадим предыдущему запросу, используя левое соединение, более глубокое объяснение с использованием диаграмм Вана:

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

Обратите внимание, что любое имя студента, у которого нет соответствующего факультета, будет иметь «нулевое» значение для названия факультета, поскольку для него нет соответствующего значения, и эти значения являются значениями в несовпадающих строках.

SQLite КРЕСТНОЕ СОЕДИНЕНИЕ

ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ дает декартово произведение для выбранных столбцов двух объединенных таблиц путем сопоставления всех значений из первой таблицы со всеми значениями из второй таблицы.

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

В отличие от INNER JOIN и LEFT OUTER JOIN, при CROSS JOIN вам не нужно указывать условие соединения, поскольку SQLite он не нужен для CROSS JOIN.

Команда SQLite приведет к получению логических результатов путем объединения всех значений из первой таблицы со всеми значениями из второй таблицы.

Например, если вы выбрали столбец из первой таблицы (colA) и другой столбец из второй таблицы (colB). colA содержит два значения (1,2), а colB также содержит два значения (3,4).

Тогда результатом CROSS JOIN будут четыре строки:

Две строки путем объединения первого значения из столбца A, равного 1, с двумя значениями столбца B (3,4), которые будут (1,3), (1,4).
Аналогично, две строки путем объединения второго значения из столбца A, равного 2, с двумя значениями столбца B (3,4), то есть (2,3), (2,4).

Пример

В следующем запросе мы попробуем выполнить CROSS JOIN между таблицами Students и Departments:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
CROSS JOIN Departments;

объяснение

В SQLite выбрать из нескольких таблиц, мы только что выбрали два столбца «studentname» из таблицы студентов и «departmentName» из таблицы факультетов.
Для перекрестного соединения мы не указывали никаких условий соединения, а только две таблицы, объединенные с помощью CROSS JOIN в середине.

Результат

Как вы видите, результат — 40 строк; 10 значений из таблицы students сопоставлены с 4 кафедрами из таблицы departments. Как показано ниже:

Четыре значения для четырех факультетов из таблицы факультетов соответствуют первому студенту Мишелю.
Четыре значения для четырех факультетов из таблицы отделов соответствуют второму студенту Джону.
Четыре значения для четырех факультетов из таблицы факультетов соответствуют третьему студенту Джеку… и так далее.

Итого

. SQLite JOIN-запрос, вы можете связать одну или несколько таблиц или подзапросов вместе, чтобы выбрать столбцы из обеих таблиц или подзапросов.

Вы могли бы:

Введение в SQLite Предложение ПРИСОЕДИНЯЙТЕСЬ

SQLite INNER JOIN

Пример

Объяснение кода

Результат

СТАТЬИ ПО ТЕМЕ

SQLite ПРИСОЕДИНЯЙТЕСЬ… ИСПОЛЬЗУЙТЕ

Пример

объяснение

Результат

SQLite ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ

Пример

объяснение

Результат

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

Пример

объяснение

Результат

SQLite КРЕСТНОЕ СОЕДИНЕНИЕ

Пример

объяснение

Результат

Итого