Как объединить несколько таблиц в одну 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: операторы JOIN,UNION, INTERSECT и EXCEPT
Соединение таблиц в запросе SELECT выполняется с помощью оператора JOIN.
Возможно также выполнить соединение и без оператора JOIN с помощью инструкции WHERE используя столбцы соединения, но этот синтаксис считается неявным и устаревшим.
Выделяют следующие виды соединения, каждому из которых соответствует своя форма оператора JOIN:
- CROSS JOIN — перекрестное или декартово соединение
- [INNER] JOIN — естественное или внутреннее соединение
- LEFT [OUTER] JOIN — левое внешнее соединение
- RIGHT [OUTER] JOIN — правое внешнее соединение
- FULL [OUTER] JOIN — полное внешнее соединение
Существует также тета-соединение, самосоединение и полусоединение.
Естественное соединение
Естественное соединение — внутреннее соединение или соединение по эквивалентности.
Здесь предложение FROM определяет соединяемые таблицы и в нем явно указывается тип соединения — INNER JOIN. Предложение ON является частью предложения FROM и указывает соединяемые столбцы. Выражение employee.dept_no = department.dept_no определяет условие соединения.
Эквивалентный запрос с применением неявного синтаксиса:
Соединяемые столбцы должны иметь идентичную семантику, т.е. оба столбца должны иметь одинаковое логическое значение. Соединяемые столбцы не обязательно должны иметь одинаковое имя (или даже одинаковый тип данных), хотя часто так и бывает.
Соединяются только строки имеющие одинаковое значение в соединяемых столбцах. Строки, не имеющие таких одинаковых значений в результирующий набор вообще не попадут.
В инструкции SELECT объединить можно до 64 таблиц (ограничение MS SQL), при этом один оператор JOIN соединяет только две таблицы:
Декартово произведение (перекрестное соединение)
Декартово произведение (перекрестное соединение) соединяет каждую строку первой таблицы с каждой строкой второй. Результатом декартово произведения первой таблицы с n строками и второй таблицы с m строками будет таблица с n × m строками.
Внешнее соединение
Внешнее соединение позволяет в отличие от внутреннего извлечь не только строки с одинаковыми значениями соединяемых столбцов, но и строки без совпадений из одной или обеих таблиц.
Выделяют три вида внешних соединений:
-
левое внешнее соединение — в результирующий набор попадают все строки из таблицы с левой стороны оператора сравнения (независимо от того имеются ли совпадающие строки с правой стороны), а из таблицы с правой стороны — только строки с совпадающими значениями столбцов. При этом если для строки из левой таблицы нет соответствий в правой таблице, значениям строки в правой таблице будут присвоены NULL
Тета-соединение
Условие сравнения столбцов соединения не обязательно должно быть равенством, но может быть любым другим сравнением. Соединение, в котором используется общее условие сравнения столбцов соединения, называется тета-соединением:
Самосоединение
Самосоединение — это естественное соединение таблицы с самой собой. При этом один столбец таблицы сравнивается сам с собой. Сравнивание столбца с самим собой означает, что в предложении FROM инструкции SELECT имя таблицы употребляется дважды. Поэтому необходимо иметь возможность ссылаться на имя одной и той же таблицы дважды. Это можно осуществить, используя, по крайней мере, один псевдоним. То же самое относится и к именам столбцов в условии соединения в инструкции SELECT. Для того чтобы различить столбцы с одинаковыми именами, необходимо использовать уточненные имена.
Полусоединение
Полусоединение похоже на естественное соединение, но возвращает только набор всех строк из одной таблицы, для которой в другой таблице есть одно или несколько совпадений.
Оператор UNION
Оператор UNION объединяет результаты двух или более запросов в один результирующий набор, в который входят все строки, принадлежащие всем запросам в объединении:
Объединение таблиц SQL
Зачастую при работе с базой данных нам требуется сделать выборку данных сразу из нескольких таблиц. Данная статья раскрывает как осуществить объединение таблиц SQL.
Пример запроса сразу к двум таблицам:
SELECT name, title FROM users, products;
Пример запроса с одноимёнными полями:
На первом месте имя таблицы или псевдоним, а на втором название поля. Если имя поля уникальное для выбранных таблиц, то указывать таблицу или псевдоним не обязательно.
SELECT users.id, name, products.id, title FROM users, products; — или SELECT us.id, name, prod.id, title FROM users AS us, products AS prod;
Объединение двух таблиц в одном запросе:
SELECT u.name, o.price FROM users u, orders o WHERE o.user_id = u.id;
Создание виртуального дубликата одной и той же таблицы:
SELECT u.name, o.surname FROM users u, users o;
Вложенные запросы
Используются для получения данных из разных таблиц.
— получение всех имён пользователей, которые делали заказы. — Выбор данных идёт из разных таблиц — Вложенный запрос должен возвращать только одно значение SELECT name FROM users WHERE id_user FROM orders WHERE user = users.name); — Если подзапрос возвращает множество: SELECT name FROM users WHERE id IN (SELECT id_user FROM orders WHERE user = users.name);
Оператор EXISTS
Определяет подзапрос как true или false
— получение всех имён пользователей, которые делали заказ и сумма заказа > 1000 — внешний запрос выполняется если подзапрос возвращает истину SELECT name FROM users WHERE EXISTS ( SELECT id_user FROM orders WHERE user = users.name AND order_price > 1000 );
SELECT * FROM users WHERE name = "Ivan" AND EXISTS ( SELECT id_user FROM orders WHERE order_price > 1000 );
Оператор UNION
Объединение полей из двух и более таблиц.
Результаты из таблиц будут выведены один под другим. То есть если в одной таблице 4 строки и в другой 4 строки, то в результирующей выборке будет 8 строк. Количество полей в обоих таблицах должно быть одинаковым!
SELECT name FROM users UNION SELECT name FROM orders;
Оператор JOIN
Бывает двух видов: внутреннее и внешнее объединение таблиц.
Оператор JOIN упрощает запрос и является альтернативой стандартного объединения данных из разных таблиц. INNER JOIN и JOIN это одно и тоже, оператор INNER может опускаться.
Внутреннее объединение INNER JOIN
Получение связанных данных из нескольких таблиц по определённому столбцу (связи полей). Не связанные данные пропускаются!
Оператор INNER JOIN
— внутреннее объединение с таблицей orders — где после ON мы указываем связи между таблицами SELECT u.name, o.order FROM users u INNER JOIN orders o ON orders.user_id = users.id WHERE u.name = ‘Ivan’;
Внешнее объединение LEFT и RIGHT OUTER JOIN
Не связанные данные так же выведутся в результате запроса. Не связанные данные получат значение NULL.
Объединение таблиц в SQL
Все запросы, которые вы видели до сих пор, были связаны с одной таблицей. В реальной жизни часто нужно запросить две или более таблиц одновременно и получить объединенный набор результатов. Технически это называется объединением (join), поскольку оно подразумевает объединение различных таблиц на основе общего поля между ними (внешнего ключа) для создания новых представлений данных.
Давайте рассмотрим таблицы сотрудников employees и отделов departments . Столбец dept_id таблицы employees является внешним ключом для таблицы departments . Поэтому эти две таблицы можно объединить, чтобы получить «комбинированные» данные.
| Таблица employees | Таблица departments |
Примечание. Чтобы объединить таблицы, должны совпадать данные столбцов, которые используются для объединения. Имена столбцов не обязаны совпадать.
Виды объединений таблиц
При объединении таблиц в запросе указывается тип объединения, который влияет на строки, которые появятся в результатов. В SQL доступны следующие виды объединений:
Inner Join
Объединение, которое возвращает только те строки, которые имеют совпадения в обеих объединенных таблицах.
Например, вы можете объединить таблицы сотрудников employees и отделов departments , чтобы создать набор результатов, показывающий название отдела для каждого сотрудника. При inner join сотрудники, для которых нет информации об отделе, не включаются в набор результатов, как и отделы, в которых нет сотрудников.
Подробнее об inner join мы поговорим в следующей статье.
Outer Join
Outer join — это расширение inner join . Outer join возвращает строки, даже если они не имеют связанных строк в объединенной таблице. Существует три типа outer join : left outer join , right outer join и full outer join .
Подробнее об этих разновидностях outer join мы поговорим в следующих статьях.
Cross Join
Cross join — это соединения без условия соединения. Каждая строка одной таблицы объединяется с каждой строкой другой таблицы. Такой тип набора результатов называется декартовым или перекрестным произведением.
Например, cross join между таблицами сотрудников employees и отделов departments вернет набор результатов с одной строкой для каждой возможной комбинации «сотрудники/отделы».