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

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

Введение в предложение SQLite JOIN

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

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

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

Синтаксис предложения SQLite JOIN
Синтаксис предложения SQLite JOIN

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

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

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

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

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

Предложение SQLite JOIN

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

Предложение SQLite JOIN

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

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

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

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

Пример

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

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

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

INNER JOIN работает следующим образом.wing:

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

Результат

Пример SQLite INNER JOIN

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

    Пример SQLite INNER JOIN

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)».

Пример

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

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

объяснение

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

Результат

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

Пример SQLite JOIN

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

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

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

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

Пример

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

объяснение

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

Результат

Пример SQLite ЕСТЕСТВЕННОГО СОЕДИНЕНИЯ

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

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

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

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

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

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

Пример

В следующемwing Например, мы попробуем «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.

Результат

Пример SQLite LEFT OUTER JOIN

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

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

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

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

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).
  • Подобноwise, две строки путем объединения второго значения из colA, равного 2, с двумя значениями colB (3,4), то есть (2,3), (2,4).

Пример

В следующемwing запрос, мы попробуем CROSS JOIN между таблицами Student и Departments:

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

объяснение

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

Результат

Пример SQLite CROSS JOIN

Как видите, результат — 40 строк; 10 значений из таблицы «Студенты» сопоставлены с 4-мя факультетами из таблицы «Отделы». Как следуетwing:

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

Выводы

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