Какого типа соединений нет в sql
JOIN is a method of combining (joining) information from two tables. The result is a stitched set of columns from both tables, defined by the join type (INNER/OUTER/CROSS and LEFT/RIGHT/FULL, explained below) and join criteria (how rows from both tables relate).
A table may be joined to itself or to any other table. If information from more than two tables needs to be accessed, multiple joins can be specified in a FROM clause.
# Self Join
A table may be joined to itself, with different rows matching each other by some condition. In this use case, aliases must be used in order to distinguish the two occurrences of the table.
In the below example, for each Employee in the example database Employees table
(opens new window) , a record is returned containing the employee’s first name together with the corresponding first name of the employee’s manager. Since managers are also employees, the table is joined with itself:
This query will return the following data:
| Employee | Manager |
|---|---|
| John | James |
| Michael | James |
| Johnathon | John |
# So how does this work?
The original table contains these records:
| Id | FName | LName | PhoneNumber | ManagerId | DepartmentId | Salary | HireDate |
|---|---|---|---|---|---|---|---|
| 1 | James | Smith | 1234567890 | NULL | 1 | 1000 | 01-01-2002 |
| 2 | John | Johnson | 2468101214 | 1 | 1 | 400 | 23-03-2005 |
| 3 | Michael | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 |
| 4 | Johnathon | Smith | 1212121212 | 2 | 1 | 500 | 24-07-2016 |
The first action is to create a Cartesian product of all records in the tables used in the FROM clause. In this case it’s the Employees table twice, so the intermediate table will look like this (I’ve removed any fields not used in this example):
| e.Id | e.FName | e.ManagerId | m.Id | m.FName | m.ManagerId |
|---|---|---|---|---|---|
| 1 | James | NULL | 1 | James | NULL |
| 1 | James | NULL | 2 | John | 1 |
| 1 | James | NULL | 3 | Michael | 1 |
| 1 | James | NULL | 4 | Johnathon | 2 |
| 2 | John | 1 | 1 | James | NULL |
| 2 | John | 1 | 2 | John | 1 |
| 2 | John | 1 | 3 | Michael | 1 |
| 2 | John | 1 | 4 | Johnathon | 2 |
| 3 | Michael | 1 | 1 | James | NULL |
| 3 | Michael | 1 | 2 | John | 1 |
| 3 | Michael | 1 | 3 | Michael | 1 |
| 3 | Michael | 1 | 4 | Johnathon | 2 |
| 4 | Johnathon | 2 | 1 | James | NULL |
| 4 | Johnathon | 2 | 2 | John | 1 |
| 4 | Johnathon | 2 | 3 | Michael | 1 |
| 4 | Johnathon | 2 | 4 | Johnathon | 2 |
The next action is to only keep the records that meet the JOIN criteria, so any records where the aliased e table ManagerId equals the aliased m table Id :
| e.Id | e.FName | e.ManagerId | m.Id | m.FName | m.ManagerId |
|---|---|---|---|---|---|
| 2 | John | 1 | 1 | James | NULL |
| 3 | Michael | 1 | 1 | James | NULL |
| 4 | Johnathon | 2 | 2 | John | 1 |
Then, each expression used within the SELECT clause is evaluated to return this table:
| e.FName | m.FName |
|---|---|
| John | James |
| Michael | James |
| Johnathon | John |
Finally, column names e.FName and m.FName are replaced by their alias column names, assigned with the AS
| Employee | Manager |
|---|---|
| John | James |
| Michael | James |
| Johnathon | John |
# Differences between inner/outer joins
SQL has various join types to specify whether (non-)matching rows are included in the result: INNER JOIN , LEFT OUTER JOIN , RIGHT OUTER JOIN , and FULL OUTER JOIN (the INNER and OUTER keywords are optional). The figure below underlines the differences between these types of joins: the blue area represents the results returned by the join, and the white area represents the results that the join will not return.
Cross Join SQL Pictorial Presentation (reference
Below are examples from this
For instance there are two tables as below :
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
# Inner Join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common:
# Left outer join
A left outer join will give all rows in A, plus any common rows in B:
# Right outer join
Similarly, a right outer join will give all rows in B, plus any common rows in A:
# Full outer join
A full outer join will give you the union of A and B, i.e., all the rows in A and all the rows in B. If something in A doesn’t have a corresponding datum in B, then the B portion is null, and vice versa.
# JOIN Terminology: Inner, Outer, Semi, Anti.
Let’s say we have two tables (A and B) and some of their rows match (relative to the given JOIN condition, whatever it may be in the particular case):
We can use various join types to include or exclude matching or non-matching rows from either side, and correctly name the join by picking the corresponding terms from the diagram above.
The examples below use the following test data:
# Inner Join
Combines left and right rows that match.
# Left Outer Join
Sometimes abbreviated to "left join". Combines left and right rows that match, and includes non-matching left rows.
# Right Outer Join
Sometimes abbreviated to "right join". Combines left and right rows that match, and includes non-matching right rows.
# Full Outer Join
Sometimes abbreviated to "full join". Union of left and right outer join.
# Left Semi Join
Includes left rows that match right rows.
# Right Semi Join
Includes right rows that match left rows.
As you can see, there is no dedicated IN syntax for left vs. right semi join — we achieve the effect simply by switching the table positions within SQL text.
# Left Anti Semi Join
Includes left rows that do not match right rows.
WARNING: Be careful if you happen to be using NOT IN on a NULL-able column! More details here
# Right Anti Semi Join
Includes right rows that do not match left rows.
As you can see, there is no dedicated NOT IN syntax for left vs. right anti semi join — we achieve the effect simply by switching the table positions within SQL text.
# Cross Join
A Cartesian product of all left with all right rows.
Cross join is equivalent to an inner join with join condition which always matches, so the following query would have returned the same result:
# Self-Join
This simply denotes a table joining with itself. A self-join can be any of the join types discussed above. For example, this is a an inner self-join:
# Left Outer Join
A Left Outer Join (also known as a Left Join or Outer Join) is a Join that ensures all rows from the left table are represented; if no matching row from the right table exists, its corresponding fields are NULL .
The following example will select all departments and the first name of employees that work in that department. Departments with no employees are still returned in the results, but will have NULL for the employee name:
This would return the following from the example database
| Departments.Name | Employees.FName |
|---|---|
| HR | James |
| HR | John |
| HR | Johnathon |
| Sales | Michael |
| Tech | NULL |
# So how does this work?
There are two tables in the FROM clause:
| Id | FName | LName | PhoneNumber | ManagerId | DepartmentId | Salary | HireDate |
|---|---|---|---|---|---|---|---|
| 1 | James | Smith | 1234567890 | NULL | 1 | 1000 | 01-01-2002 |
| 2 | John | Johnson | 2468101214 | 1 | 1 | 400 | 23-03-2005 |
| 3 | Michael | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 |
| 4 | Johnathon | Smith | 1212121212 | 2 | 1 | 500 | 24-07-2016 |
| Id | Name |
|---|---|
| 1 | HR |
| 2 | Sales |
| 3 | Tech |
First a Cartesian product is created from the two tables giving an intermediate table.
The records that meet the join criteria (Departments.Id = Employees.DepartmentId) are highlighted in bold; these are passed to the next stage of the query.
As this is a LEFT OUTER JOIN all records are returned from the LEFT side of the join (Departments), while any records on the RIGHT side are given a NULL marker if they do not match the join criteria. In the table below this will return Tech with NULL
| Id | Name | Id | FName | LName | PhoneNumber | ManagerId | DepartmentId | Salary | HireDate |
|---|---|---|---|---|---|---|---|---|---|
| 1 | HR | 1 | James | Smith | 1234567890 | NULL | 1 | 1000 | 01-01-2002 |
| 1 | HR | 2 | John | Johnson | 2468101214 | 1 | 1 | 400 | 23-03-2005 |
| 1 | HR | 3 | Michael | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 |
| 1 | HR | 4 | Johnathon | Smith | 1212121212 | 2 | 1 | 500 | 24-07-2016 |
| 2 | Sales | 1 | James | Smith | 1234567890 | NULL | 1 | 1000 | 01-01-2002 |
| 2 | Sales | 2 | John | Johnson | 2468101214 | 1 | 1 | 400 | 23-03-2005 |
| 2 | Sales | 3 | Michael | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 |
| 2 | Sales | 4 | Johnathon | Smith | 1212121212 | 2 | 1 | 500 | 24-07-2016 |
| 3 | Tech | 1 | James | Smith | 1234567890 | NULL | 1 | 1000 | 01-01-2002 |
| 3 | Tech | 2 | John | Johnson | 2468101214 | 1 | 1 | 400 | 23-03-2005 |
| 3 | Tech | 3 | Michael | Williams | 1357911131 | 1 | 2 | 600 | 12-05-2009 |
| 3 | Tech | 4 | Johnathon | Smith | 1212121212 | 2 | 1 | 500 | 24-07-2016 |
Finally each expression used within the SELECT clause is evaluated to return our final table:
| Departments.Name | Employees.FName |
|---|---|
| HR | James |
| HR | John |
| Sales | Richard |
| Tech | NULL |
# Implicit Join
Joins can also be performed by having several tables in the from clause, separated with commas , and defining the relationship between them in the where clause. This technique is called an Implicit Join (since it doesn’t actually contain a join clause).
All RDBMSs support it, but the syntax is usually advised against. The reasons why it is a bad idea to use this syntax are:
- It is possible to get accidental cross joins which then return incorrect results, especially if you have a lot of joins in the query.
- If you intended a cross join, then it is not clear from the syntax (write out CROSS JOIN instead), and someone is likely to change it during maintenance.
The following example will select employee’s first names and the name of the departments they work for:
This would return the following from the example database
| e.FName | d.Name |
|---|---|
| James | HR |
| John | HR |
| Richard | Sales |
# Basic explicit inner join
A basic join (also called "inner join") queries data from two tables, with their relationship defined in a join clause.
The following example will select employees’ first names (FName) from the Employees table and the name of the department they work for (Name) from the Departments table:
This would return the following from the example database
| Employees.FName | Departments.Name |
|---|---|
| James | HR |
| John | HR |
| Richard | Sales |
# CROSS JOIN
Cross join does a Cartesian product of the two members, A Cartesian product means each row of one table is combined with each row of the second table in the join. For example, if TABLEA has 20 rows and TABLEB has 20 rows, the result would be 20*20 = 400 output rows.
| d.Name | e.FName |
|---|---|
| HR | James |
| HR | John |
| HR | Michael |
| HR | Johnathon |
| Sales | James |
| Sales | John |
| Sales | Michael |
| Sales | Johnathon |
| Tech | James |
| Tech | John |
| Tech | Michael |
| Tech | Johnathon |
It is recommended to write an explicit CROSS JOIN if you want to do a cartesian join, to highlight that this is what you want.
# Joining on a Subquery
Joining a subquery is often used when you want to get aggregate data from a child/details table and display that along with records from the parent/header table. For example, you might want to get a count of child records, an average of some numeric column in child records, or the top or bottom row based on a date or numeric field. This example uses aliases, which arguable makes queries easier to read when you have multiple tables involved. Here’s what a fairly typical subquery join looks like. In this case we are retrieving all rows from the parent table Purchase Orders and retrieving only the first row for each parent record of the child table PurchaseOrderLineItems.
# CROSS APPLY & LATERAL JOIN
A very interesting type of JOIN is the LATERAL JOIN (new in PostgreSQL 9.3+),
which is also known as CROSS APPLY/OUTER APPLY in SQL-Server & Oracle.
The basic idea is that a table-valued function (or inline subquery) gets applied for every row you join.
This makes it possible to, for example, only join the first matching entry in another table.
The difference between a normal and a lateral join lies in the fact that you can use a column that you previously joined in the subquery that you "CROSS APPLY".
left | right | inner JOIN LATERAL
CROSS | OUTER APPLY
INNER JOIN LATERAL is the same as CROSS APPLY
and LEFT JOIN LATERAL is the same as OUTER APPLY
Example usage (PostgreSQL 9.3+):
And for SQL-Server
# FULL JOIN
One type of JOIN that is less known, is the FULL JOIN.
(Note: FULL JOIN is not supported by MySQL as per 2016)
A FULL OUTER JOIN returns all rows from the left table, and all rows from the right table.
If there are rows in the left table that do not have matches in the right table, or if there are rows in right table that do not have matches in the left table, then those rows will be listed, too.
Note that if you’re using soft-deletes, you’ll have to check the soft-delete status again in the WHERE-clause (because FULL JOIN behaves kind-of like a UNION);
It’s easy to overlook this little fact, since you put AP_SoftDeleteStatus = 1 in the join clause.
Also, if you are doing a FULL JOIN, you’ll usually have to allow NULL in the WHERE-clause; forgetting to allow NULL on a value will have the same effects as an INNER join, which is something you don’t want if you’re doing a FULL JOIN.
# Recursive JOINs
Recursive joins are often used to obtain parent-child data. In SQL, they are implemented with recursive common table expressions
# Syntax
- [ < INNER | < < LEFT | RIGHT | FULL >[ OUTER ] > > ] JOIN
# Remarks
Joins, as their name suggests, are a way of querying data from several tables in a joint fashion, with the rows displaying columns taken from more than one table.
Основы SQL: работа с таблицами и их соединением
Базы данных – то, что помогает бизнесу, а также государственным органам и предприятиям. Это – хранилища информации. Работать с ними должен уметь каждый, особенно когда речь заходит о программировании.
В основном БД представлены таблицами. С ними совершают разнообразные действия:
- объединяют тем или иным способом;
- удаляют;
- корректируют;
- сохраняют;
- выводят на экран.
Делается это или посредством пользовательского интерфейса и специализированных утилит, или через специальные языки «программирования». С ними должен быть знаком каждый программер.
Определение SQL
Чтобы задействовать таблицы в приложениях, играх и прочем контенте, можно использовать SQL. Это – самый распространенный вариант развития событий.
Так называют язык структурированных запросов. Он дает возможность сохранять, управлять и извлекать информацию из реляционных баз данных.
Особенности – что умеет язык
При помощи SQL пользователь/разработчик сможет:
- заполучать доступ к информации в системах управления БД;
- производить описание данных, их структур;
- определять электронные материалы в «табличном хранилище», управляя оными;
- проводить взаимодействие с иными языками при помощи модулей, библиотек и компиляторов SQL;
- создавать новые таблички, удалять старые;
- заниматься созданием представлений, хранимых процедур и функций.
Также при работе с таблицами БД за счет SQL можно настраивать доступ к представлениям, таблицам и процедурам. Главное знать, каким именно образом действовать.
В SQL существуют всевозможные команды, использованием которых удается производить те или иные манипуляции. Далее будет рассказано всего об одном достаточно важном моменте. А именно – как использовать оператор Join. Он пригодится и новичкам, и тем, кто долгое время работает с таблицами и БД.
Что представляет собой JOIN
JOIN – команда/оператор, который используется, когда нужно произвести объединение нескольких таблиц в базах данных. Вследствие нее происходит преобразование двух строк в одну. И не обязательно оные окажутся в разных табличках. JOIN может работать в пределах одного «хранилища» информации.
Команда выполняется при перечислении двух и более таблиц в операторе SQL. Определение JOIN – соединение. Синтаксис здесь довольно простой. Но стоит обратить внимание на то, что вариантов объединения несколько. У каждого – своя запись.
Возможные слияния зависят от того, что именно хочет получить пользователь в конечном итоге. Существуют следующие типы соединений таблиц и иных материалов в рассматриваемом языке запросов:
- простое;
- left outer join;
- right outer join;
- full outer join.
Каждый join запрос в SQL имеет собственные нюансы. О них будет рассказано далее. Разобраться, какой вариант подойдет в том или ином случае поможет простая математика. Там тоже есть объединение. Если разобрать соответствующий вопрос там, в программировании добиться успеха удастся в считанные минуты.
Важно: есть еще один вариант – cross join. Встречается на практике не слишком часто, но помнить о подобном раскладе тоже нужно.
Проще простого – Inner
Первый вариант – это использование простого объединения. Встречается на практике чаще всего. Возвращает пересечение нескольких (обычно двух) множеств. В табличной терминологии — происходит возврат записи, которая имеется в обоих таблицах. Оная должна соответствовать выставленному заранее критерию.
Для реализации поставленной задачи применяется INNER JOIN. Чтобы лучше понимать данный процесс, стоит составить диаграмму Венна:
- нарисовать круг – пусть это будет «таблица 1»;
- рядом, задевая область первой фигуры, изобразить второй круг – это «таблица 2»;
- закрасить область пересечения – это и есть результат операции inner join.
Рисунок выше – наглядный пример диаграммы Венна. С его помощью удастся разобраться в принципах «простого» объединения нескольких табличек.
Запись
Для того, чтобы выполнять те или иные операции в запросовом языке, стоит уточнить синтаксис желаемой команды. В случае с обычным (простым) объединением необходимо использовать следующий шаблон:
Здесь никаких проблем возникнуть не должно. Код легко читается:
- выбрать колонки;
- from table1 – из первой таблицы;
- объединить с таблицей 2.
Для наглядного примера стоит рассмотреть несколько таблиц с информацией, а также принцип joins типа inner.
Наглядный пример
Пусть будет дана таблица под названием customer. В ней такая информация:
Далее – создается таблица orders:
Теперь нужно joining поля в этих хранилищах информации простым объединением. Для реализации поставленной задачи составляется команда типа:
| SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id; |
В конечном итоге при обработке запроса на экран будет выведен следующий результат:
Здесь выбираются поля в таблице, которые имеют одинаковые значения customer_id в обоих хранилищах. Поэтому другие поля будут пропущены. А строчка, где order_id = 5 в orders опускается, так как customer_id идут со значениями null. То есть, их нет в customers.
Left Join
В отличие от предыдущего объединения, left join – это возврат всех строк из левой таблицы по установленным принципам. Это – левостороннее соединение, осуществляемое через условие ON. Вследствие обработки операции:
- проводится проверка на соответствие условия соединения;
- если оно выполняется – строчка из второй прибавляется к первой таблице.
Именно такое описание можно дать команде left join. Представив его в виде диаграмм, необходимо запомнить следующее представление:
Вся закрашенная область – это результат обработки команды left join в языке SQL.
Запись и пример
Указанным ранее вариантом соединения пользуются чаще всего. Но иногда, особенно при работе с большим количеством информации, может потребоваться левостороннее «слияние». Оно обладает такой формой записи:
Ключевое слово OUTER может быть пропущено. Это нормальное явление, допускаемое некоторыми языками запросов. Помогает значительно сократить исходный код при его написании.
Для примера необходимо взять таблицу с информацией:
Вторая база данных:
Названия тут будут такими же, как и в прошлом случае. Теперь составляется запрос выполнения левостороннего слияния:
После обработки оного на экране появятся всего 6 записей:
Так произошло, потому что left join произвел внутреннее объединение только строки customers и те строчки из orders, где объединенные поля обладают равными значениями. Также стоит запомнить следующие важные сведения:
- когда значение в customer_id из customers отсутствует в orders, поля «ордерс» отображаются в виде null;
- если выставленный параметр слияния не выполняется, поля/строчки «отбрасываются».
Ничего трудного. Такой тип объединения табличек в программировании и базах данных тоже встречается не слишком редко.
Right Join
Описание right join предельно простое – правостороннее соединение. Результатом будут служить строчки из второй таблицы, соответствующие выставленному условию слияния. Наглядно это выглядит так:
Результат запроса исключает поля левой таблицы, не соответствующие выставленным при составлении команды критериям.
О синтаксической записи и примерах
Синтаксис в команды будет иметь вид:
Чтобы понять, как работает right join в языке SQL, рекомендуется обратить внимание на наглядный пример. Он опять осуществляется с табличками customer и orders. Пример будет прописан в операторе SELECT.
Даны две таблицы с информацией:
Далее, чтобы joined две таблички по правостороннему принципу, требуется отправить соответствующий запрос. Он обладает такой формой записи:
Как только операция пройдет обработку, на экране устройства появится результат. Он будет состоять из пяти элементов:
- возвращаются строки из orders – все;
- на экран дополнительно выводятся строчки из customers, которые имеют с «ордерс» одинаковые значения;
- если customers_id в orders отсутствует в «кастомерс», соответствующие поля имеют значение null.
Но и это еще не все. Для полного осознания запросов слияния электронных материалов, требуется в первую очередь изучить все доступные расклады. Их осталось еще 2. Встречаются на практике не слишком часто, из-за чего доставляют немалые хлопоты. Особенно тем, кто занимается запросовым языком относительно недавно.
По полной программе
Предпоследний вариант, который предусматривает join в языке SQL – это full. Можно назвать данный процесс созданием единой таблицы. Информациях из обеих БД будет выведена на экран. Здесь не важно, где именно осуществляется непосредственное пересечение полей.
При full join происходит:
- проверка на невыполнение условия;
- вывод на экран всех строчек из левой и правой таблиц со значениями null, когда условие не выполнено.
Выглядит это так:
Если говорить математическим языком, вследствие обработки запроса произойдет слияние двух множеств. На диаграмме виден результат – это закрашенная область.
Как записывать и применять
Форма записи окажется такого типа:
Full – это внутреннее соединение всех полей в табличках предоставленных баз данных. Для того, чтобы намного лучше разобраться в этом вопросе, составляются таблички:
Они называются так же, как и предлагал последний наглядный пример. Запрос обладает следующим видом:
Перекрестным типом
Еще один довольно интересный подход, который относится к основным – это cross. Называется перекрестным. Ин6огда – декартово. При его использовании происходит генерация того же результата, что и при вызове двух таблиц без рассматриваемой команды. Это значит, что:
- на экране появится итог слияния первой таблицы и второй;
- каждая запись одной таблицы будет дублироваться для каждого поля из другой.
Так, если в таблице1 a записей, а в таблице2 b, результирующей окажется таблица a x b полей. В виде диаграммы представить подобный процесс проблематично. Она только запутает разработчиков.
Обладает запрос следующим синтаксисом:
Внимание: cross join – это декартово произведение. Так будет проще понять, о чем идет речь.
Комбинации
Для того, чтобы объединять пары табличек, можно воспользоваться self join. Чтобы разобраться в принципах работы запроса, необходимо рассмотреть пример. Сам запрос выглядит так:
Представить такое произведение множеств наглядно тоже проблематично. Зато на примере – нет. Тут показано, что у Гарри нет начальника.
Исключение
После рассмотрения внешнего соединения и другого объединения в языке SQL, важно не забывать об исключении. Результатом будут данные из первой таблицы, но исключая поля, совпадающие со второй табличкой. Наглядно это выглядит так:
Чтобы воспользоваться подобным приемом, не нужно знать ни декартово произведение, ни какие-то другие сложные понятия. В запрос добавляется оператор Where.
А вот пример записи команды:
Планы исполнения
Для того, чтобы грамотно использовать join в SQL, нужно учитывать планы исполнения запросов. То, как именно (в какой последовательности) будет происходить обработка операторов и необходимые вычисления.
- from;
- join;
- where.
Данный принцип актуален для всех СУБД. Если не принимать его во внимание, можно в конечном итоге получить таблички с неверной информацией.
Важно: для того, чтобы ускорить обработку команд, важно использовать кластерные индексы. Они применяются Server Query Optimizer для обеих таблиц. Автоматическое создание кластерных индексов производится для первичных ключей. С остальными придется производить соответствующую настройку.
Описание join в языке SQL не должно вызывать вопросов. А если хочется лучше понять, что это такое, а также разобраться в принципах работы queries, стоит посетить дистанционные специализированные курсы. По окончанию обучения выдается сертификат, подтверждающий знания и навыки в выбранном направлении.
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
What’s the difference between INNER JOIN , LEFT JOIN , RIGHT JOIN and FULL JOIN in MySQL?
2 Answers 2
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
There are different types of joins available in SQL:
INNER JOIN: returns rows when there is a match in both tables.
LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN: combines the results of both left and right outer joins.
The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.
SELF JOIN: joins a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.
We can take each first four joins in Details :
We have two tables with the following values.
TableA
TableB
INNER JOIN
Note : gives the intersection of the two tables, i.e. rows TableA and TableB have in common.
Apply it in our sample table :
LEFT JOIN
Note : gives all selected rows in TableA, plus any common selected rows in TableB.
Apply it in our sample table :
RIGHT JOIN
Note : gives all selected rows in TableB, plus any common selected rows in TableA.
Apply it in our sample table :
FULL JOIN
Note : returns all selected values from both tables.
Apply it in our sample table :
Interesting Fact
- For INNER joins the order doesn’t matter.
- For (LEFT, RIGHT or FULL) OUTER joins, the order matters.
Better to go check this Link it will give you interesting details about join order.
SQL оператор JOINS
В этом учебном материале вы узнаете, как использовать SQL JOINS с синтаксисом и примерами.
Описание
SQL JOINS используются для извлечения данных из нескольких таблиц. SQL JOIN выполняется всякий раз, когда две или более таблицы перечислены в операторе SQL.
Существует 4 различных типа соединений SQL:
Итак, давайте обсудим синтаксис SQL JOIN, рассмотрим наглядные иллюстрации SQL JOINS и рассмотрим несколько примеров.
SQL INNER JOIN (простое соединение)
Скорее всего, вы уже писали SQL запрос, который использует SQL INNER JOIN. Это наиболее распространенный тип соединения SQL. INNER JOIN возвращает все строки из нескольких таблиц, где выполняется условие соединения.
Синтаксис
Синтаксис INNER JOIN в SQL:
Рисунок.
На этом рисунке SQL INNER JOIN возвращает затененную область:
SQL INNER JOIN будет возвращать записи, где пересекаются table1 и table2 .
Пример
Давайте рассмотрим пример использования INNER JOIN в запросе.
В этом примере у нас есть таблица customer и следующими данными:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
И таблица orders со следующими данными:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2019/06/18 |
| 2 | 5000 | 2019/06/18 |
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
Выполним следующий SQL оператор:
Будет выбрано 4 записи. Вот результаты, которые вы должны получить:
| customer_id | order_id | order_date |
|---|---|---|
| 4000 | 4 | 2019/06/20 |
| 5000 | 2 | 2019/06/18 |
| 7000 | 1 | 2019/06/18 |
| 8000 | 3 | 2019/06/19 |
В этом примере будут возвращены все строки из таблиц customers и orders , в которых совпадают значения поля customer_id в обоих таблицах.
Строки, где значение customer_id равен 6000 и 9000 в таблице customers , будут опущены, поскольку они не существуют в обеих таблицах. Строка, в которой значение order_id равно 5 из таблицы orders , будет опущена, поскольку customer_id со значением NULL не существует в таблице customers .
SQL LEFT OUTER JOIN
Другой тип соединения называется LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие соединения).
Синтаксис
Синтаксис для LEFT OUTER JOIN в SQL:
В некоторых базах данных ключевое слово OUTER опущено и записывается просто как LEFT JOIN.
Рисунок
На этом рисунке SQL LEFT OUTER JOIN возвращает затененную область:
SQL LEFT OUTER JOIN возвращает все записи из table1 и только те записи из table2 , которые пересекаются с table1 .
Пример
Теперь давайте рассмотрим пример, который показывает, как использовать LEFT OUTER JOIN в операторе SELECT.
Используя ту же таблицу customers , что и в предыдущем примере:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
И таблицу orders со следующими данными:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2019/06/18 |
| 2 | 5000 | 2019/06/18 |
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
Введите следующий SQL оператор:
Будет выбрано 6 записей. Вот результаты, которые вы получите:
| customer_id | order_id | order_date |
|---|---|---|
| 4000 | 4 | 2019/06/20 |
| 5000 | 2 | 2019/06/18 |
| 6000 | NULL | NULL |
| 7000 | 1 | 2019/06/18 |
| 8000 | 3 | 2019/06/19 |
| 9000 | NULL | NULL |
Этот пример LEFT OUTER JOIN вернул бы все строки из таблицы customers и только те строки из таблицы orders , в которых объединенные поля равны.
Если значение customer_id в таблице customers не существует в таблице orders , все поля таблицы orders будут отображаться как NULL в наборе результатов. Как вы можете видеть, строки, где customer_id равен 6000 и 9000, будут включены в LEFT OUTER JOIN, но поля order_id и order_date отображают NULL.
SQL RIGHT OUTER JOIN JOIN
Другой тип соединения называется SQL RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие соединения).
Синтаксис
Синтаксис для RIGHT OUTER JOIN в SQL:
В некоторых базах данных ключевое слово OUTER опущено и записывается просто как RIGHT JOIN.
Рисунок
На этом рисунке SQL RIGHT OUTER JOIN возвращает затененную область:
SQL RIGHT OUTER JOIN возвращает все записи из table2 и только те записи из table1 , которые пересекаются с table2 .
Пример
Теперь давайте рассмотрим пример, который показывает, как использовать RIGHT OUTER JOIN в операторе SELECT.
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
И таблицу orders со следующими данными:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2019/06/18 |
| 2 | 5000 | 2019/06/18 |
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
Введите следующий SQL оператор:
Будет выбрано 5 записей. Вот результаты, которые вы должны получить:
| customer_id | order_id | order_date |
|---|---|---|
| NULL | 5 | 2019/07/01 |
| 4000 | 4 | 2019/06/20 |
| 5000 | 2 | 2019/06/18 |
| 7000 | 1 | 2019/06/18 |
| 8000 | 3 | 2019/06/19 |
Этот пример RIGHT OUTER JOIN вернул бы все строки из таблицы orders и только те строки из таблицы customers , где объединенные поля равны.
Если значение customer_id в таблице orders не существует в таблице customers , то все поля в таблице customers будут отображаться как NULL в наборе результатов. Как видите, строка, где order_id равен 5, будет включена в RIGHT OUTER JOIN, но в поле customer_id отображается NULL.
SQL FULL OUTER JOIN
Другой тип объединения называется SQL FULL OUTER JOIN. Этот тип объединения возвращает все строки из LEFT таблицы и RIGHT таблицы со значениями NULL в месте, где условие соединения не выполняется.
Синтаксис
Синтаксис для SQL FULL OUTER JOIN:
В некоторых базах данных ключевое слово OUTER опускается и записывается просто как FULL JOIN.
Рисунок
На этом рисунке SQL FULL OUTER JOIN возвращает затененную область:
SQL FULL OUTER JOIN возвращает все записи из таблиц table1 и table2 .
Пример
Давайте рассмотрим пример, который показывает, как использовать FULL OUTER JOIN в операторе SELECT.
Используя ту же таблицу customers , что и в предыдущем примере:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
И таблицу orders со следующими данными:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2019/06/18 |
| 2 | 5000 | 2019/06/18 |
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
Введите следующий SQL оператор:
SELECT customers.customer_id,
orders.order_id,
orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;
Будет выбрано 7 записей. Вот результаты, которые вы получите:
| customer_id | order_id | order_date |
|---|---|---|
| NULL | 5 | 2019/07/01 |
| 4000 | 4 | 2019/06/20 |
| 5000 | 2 | 2019/06/18 |
| 6000 | NULL | NULL |
| 7000 | 1 | 2019/06/18 |
| 8000 | 3 | 2019/06/19 |
| 9000 | NULL | NULL |
Это пример FULL OUTER JOIN будет возвращать все строки из таблицы orders и все строки из таблицы customers . Всякий раз, когда условие соединения не выполняется, значение NULL будет распространяться на эти поля в наборе результатов. Это означает, что если значение customer_id в таблице customers не существует в таблице orders , то все поля в таблице orders будут отображаться в наборе результатов как NULL Кроме того, если значение customer_id в таблице orders не существует в таблице customers , то все поля в таблице customers будут отображаться в наборе результатов как NULL.
Как видите, строки, где customer_id равен 6000 и 9000, будут включены, но поля order_id и order_date для этих записей содержат значение NULL. Строка, где order_id равен 5, также будет включена, но поле customer_id для этой записи имеет значение NULL.