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) Откройте базу данных “TutorialsSampleDB.db” с помощью следующей команды:

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

SQLite INNER JOIN

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

Пример

В следующем примере мы объединим две таблицы «Students» и «Departments» по идентификатору отдела (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». Несоответствующие строки будут проигнорированы и не включены в результат.

Поэтому из 10 студентов, обучающихся на факультетах информационных технологий, математики и физики, в результате запроса были получены только 8 человек. Студенты «Йена» и «Джордж» не были включены в выборку, поскольку у них отсутствует идентификатор факультета (devent), который не соответствует столбцу departmentId в таблице departments. Вот как это выглядит следующим образом:

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

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

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

Разница между INNER JOIN и JOIN... USING:

При использовании оператора “JOIN … USING” условие объединения не указывается, а просто указывается столбец объединения, общий для двух объединяемых таблиц. Вместо “INNER JOIN table2 ON table1.cola = table2.cola” мы пишем “table1 JOIN table2 USING(cola)”.

Пример

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

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

объяснение

• В отличие от предыдущего примера, мы не написали «ON Students.DepartmentId = Departments.DepartmentId». Мы просто написали «USING(DepartmentId)».
• SQLite автоматически выводит условие соединения и сравнивает DepartmentId из обеих таблиц – Students и Departments.
• Вы можете использовать этот синтаксис, когда два сравниваемых столбца имеют одно и то же имя.

Результат

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

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

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

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

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

Пример

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

объяснение

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

Результат

Функция NATURAL JOIN даст точно такой же результат, как и функции INNER JOIN и JOIN USING, поскольку в нашем примере все три запроса эквивалентны. Однако в некоторых случаях результат INNER JOIN будет отличаться от результата INNER JOIN. Например, если существует несколько таблиц с одинаковыми именами, то INNER JOIN будет сопоставлять все столбцы друг с другом. В то же время INNER JOIN будет сопоставлять только столбцы, указанные в условии объединения.

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

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

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

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

Таким образом, вы получите количество строк, эквивалентное количеству строк в левом соединении. Таким образом, вы получите совпадающие строки из обеих таблиц (например, результаты 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 студентов. Даже если у четвертого и последнего студента, Йены и Джорджа, идентификаторы отделов (departmentIds) отсутствуют в таблице «Отделы», они также включены.

В этих случаях значение departmentName для Йены и Джорджа будет равно «null», поскольку в таблице departments нет departmentName, соответствующего значению departmentId.

Давайте подробнее рассмотрим предыдущий запрос с использованием левого объединения (left join) с помощью диаграмм Венна:

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

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

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

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

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

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

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» из таблицы факультетов.
• Для перекрестного соединения мы не указывали никаких условий соединения, а просто объединили две таблицы, используя перекрестное соединение между ними.

Результат

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

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

Часто задаваемые вопросы (FAQ)