Таблицы соединения SQLite: внутренние, естественные, лево-внешние, перекрестные (примеры)

Содержание:

Anonim

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

В этом руководстве вы узнаете:

  • Введение в предложение SQLite JOIN
  • ВНУТРЕННЕЕ СОЕДИНЕНИЕ
  • ПРИСОЕДИНЯЙТЕСЬ… ИСПОЛЬЗУЯ
  • ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ
  • ЛЕВОЕ НАРУЖНОЕ СОЕДИНЕНИЕ
  • КРЕСТНОЕ СОЕДИНЕНИЕ

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

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

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

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

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

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

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

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

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

Шаг 2) Откройте базу данных TutorialsSampleDB.db с помощью следующей команды:

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

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

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

Пример

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

ВЫБРАТЬСтуденты.StudentName,Departments.DepartmentNameОТ студентовВНУТРЕННЕЕ СОЕДИНЕНИЕ Отделов к студентам.DepartmentId = Departments.DepartmentId;

Расшифровка кода:

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

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

Выход:

  • ВНУТРЕННЕЕ СОЕДИНЕНИЕ создает записи как из таблиц студентов, так и из таблиц факультета, которые соответствуют условию « S tudents.DepartmentId = Departments.DepartmentId ». Несовпадающие строки будут проигнорированы и не включены в результат.
  • Вот почему только 8 студентов из 10 были возвращены из этого запроса с факультетов ИТ, математики и физики. В то время как студенты «Jena» и «George» не были включены, потому что у них есть нулевой идентификатор отдела, который не соответствует столбцу DepartmentId из таблицы факультетов. Как следующее:

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

ВНУТРЕННЕЕ СОЕДИНЕНИЕ может быть записано с использованием предложения «USING», чтобы избежать избыточности, поэтому вместо записи «ON Students.DepartmentId = Departments.DepartmentId» вы можете просто написать «USING (DepartmentID)».

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

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

С помощью "JOIN

… ИСПОЛЬЗОВАНИЕ "вы не пишете условие соединения, вы просто пишете столбец соединения, который является общим для двух соединенных таблиц, вместо того, чтобы писать table1" INNER JOIN table2 ON table1.cola = table2.cola "мы пишем это как" table1 ПРИСОЕДИНЯЙТЕСЬ к таблице2 ИСПОЛЬЗУЯ (кола) ".

Пример

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

ВЫБРАТЬСтуденты.StudentName,Departments.DepartmentNameОТ студентовВНУТРЕННЕЕ СОЕДИНЕНИЕ Департаменты ИСПОЛЬЗОВАНИЕ (DepartmentId)

Объяснение

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

Выход

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

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

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

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

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

Пример

ВЫБРАТЬСтуденты.StudentName,Departments.DepartmentNameОТ студентовЕстественные отделы JOIN;

Объяснение

  • Нам не нужно писать условие соединения с именами столбцов (как мы это делали во 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» соединить две таблицы «Студенты» и «Отделы»:

ВЫБРАТЬСтуденты.StudentName,Departments.DepartmentNameОТ Студентов - это левая таблицаLEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Объяснение

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

Выход

  • Как вы можете видеть, включены все строки из таблицы студентов, всего 10 студентов. Даже если четвертого и последнего студента, Jena и George DepartmentIds нет в таблице Departments, они тоже будут включены.
  • И в этих случаях значение DepartmentName и для Jena, и для George будет "null", потому что в таблице DepartmentName отсутствует значение DepartmentName, соответствующее их значению DepartmentId.

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

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

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

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

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

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

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

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

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

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

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

Пример

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

ВЫБРАТЬСтуденты.StudentName,Departments.DepartmentNameОТ студентовCROSS JOIN Департаменты;

Объяснение

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

Выход:

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

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

    … и так далее.

Резюме

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