Oracle Database
JOINS
Поиск…
ПЕРЕКРЕСТНЫЙ ПРИСОЕДИНЕНИЕ
CROSS JOIN выполняет соединение между двумя таблицами, которое не использует явное предложение о соединении и приводит к декартовому произведению двух таблиц. Декартово произведение означает, что каждая строка одной таблицы объединяется с каждой строкой второй таблицы в соединении. Например, если TABLEA имеет 20 строк и TABLEB имеет 20 строк, результатом будет 20*20 = 400 строк вывода.
Пример:
SELECT *
FROM TABLEA CROSS JOIN TABLEB;
Это также можно записать в виде:
SELECT *
FROM TABLEA, TABLEB;
Вот пример перекрестного соединения в SQL между двумя таблицами:
Пример таблицы: TABLEA
+-------+---------+
| VALUE | NAME |
+-------+---------+
| 1 | ONE |
| 2 | TWO |
+-------+---------+
Пример таблицы: TABLEB
+-------+--------+
| VALUE | NAME |
+-------+--------+
| 3 | THREE |
| 4 | FOUR |
+-------+--------+
Теперь, если вы выполните запрос:
SELECT *
FROM TABLEA CROSS JOIN TABLEB;
Выход:
+-------+--------+-------+--------+
| VALUE | NAME | VALUE | NAME |
+-------+--------+-------+--------+
| 1 | ONE | 3 | THREE |
| 1 | ONE | 4 | FOUR |
| 2 | TWO | 3 | THREE |
| 2 | TWO | 4 | FOUR |
+-------+--------+-------+--------+
Вот как происходит перекрестное соединение между двумя таблицами: 
Подробнее о Cross Join: документация Oracle
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
INNER JOIN - операция JOIN, которая позволяет вам указать явное предложение о соединении.
Синтаксис
TableExpression [INNER] JOIN TableExpression {ON booleanExpression | ИСПОЛЬЗОВАНИЕ}
Вы можете указать предложение соединения, указав ON с булевым выражением.
Объем выражений в предложении ON включает текущие таблицы и любые таблицы во внешних блоках запросов к текущему SELECT. В следующем примере предложение ON относится к текущим таблицам:
-- Join the EMP_ACT and EMPLOYEE tables
-- select all the columns from the EMP_ACT table and
-- add the employee's surname (LASTNAME) from the EMPLOYEE table
-- to each row of the result
SELECT SAMP.EMP_ACT.*, LASTNAME
FROM SAMP.EMP_ACT JOIN SAMP.EMPLOYEE
ON EMP_ACT.EMPNO = EMPLOYEE.EMPNO
-- Join the EMPLOYEE and DEPARTMENT tables,
-- select the employee number (EMPNO),
-- employee surname (LASTNAME),
-- department number (WORKDEPT in the EMPLOYEE table and DEPTNO in the
-- DEPARTMENT table)
-- and department name (DEPTNAME)
-- of all employees who were born (BIRTHDATE) earlier than 1930.
SELECT EMPNO, LASTNAME, WORKDEPT, DEPTNAME
FROM SAMP.EMPLOYEE JOIN SAMP.DEPARTMENT
ON WORKDEPT = DEPTNO
AND YEAR(BIRTHDATE) < 1930
-- Another example of "generating" new data values,
-- using a query which selects from a VALUES clause (which is an
-- alternate form of a fullselect).
-- This query shows how a table can be derived called "X"
-- having 2 columns "R1" and "R2" and 1 row of data
SELECT *
FROM (VALUES (3, 4), (1, 5), (2, 6))
AS VALUESTABLE1(C1, C2)
JOIN (VALUES (3, 2), (1, 2),
(0, 3)) AS VALUESTABLE2(c1, c2)
ON VALUESTABLE1.c1 = VALUESTABLE2.c1
-- This results in:
-- C1 |C2 |C1 |2
-- -----------------------------------------------
-- 3 |4 |3 |2
-- 1 |5 |1 |2
-- List every department with the employee number and
-- last name of the manager
SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME
FROM DEPARTMENT INNER JOIN EMPLOYEE
ON MGRNO = EMPNO
-- List every employee number and last name
-- with the employee number and last name of their manager
SELECT E.EMPNO, E.LASTNAME, M.EMPNO, M.LASTNAME
FROM EMPLOYEE E INNER JOIN
DEPARTMENT INNER JOIN EMPLOYEE M
ON MGRNO = M.EMPNO
ON E.WORKDEPT = DEPTNO
ЛЕВЫЙ ВНЕШНИЙ ВСТУПИТЕЛЬ
LEFT OUTER JOIN выполняет соединение между двумя таблицами, для которых требуется явное предложение соединения, но не исключает непревзойденные строки из первой таблицы.
Пример:
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP LEFT OUTER JOIN SCOTT.DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
Несмотря на то, что синтаксис ANSI является рекомендуемым , он может часто встречаться с устаревшим синтаксисом. Используя (+) внутри условия, определите, какую сторону уравнения рассматривать как внешнюю .
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP,
SCOTT.DEPT
WHERE
EMP.DEPTNO = DEPT.DEPTNO(+);
Ниже приведен пример левой внешней связи между двумя таблицами:
Пример таблицы: EMPLOYEE
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Пример таблицы: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Теперь, если вы выполните запрос:
SELECT
*
FROM
EMPLOYEE LEFT OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Выход:
+-----------+---------+---------+--------------+
| NAME | DEPTNO | DEPTNO | DEPTNAME |
+-----------+---------+---------+--------------+
| F | 1 | 1 | ACCOUNTING |
| E | 1 | 1 | ACCOUNTING |
| B | 1 | 1 | ACCOUNTING |
| D | 2 | 2 | FINANCE |
| A | 2 | 2 | FINANCE |
| C | 3 | | |
| H | 4 | | |
| G | 4 | | |
+-----------+---------+---------+--------------+
ПРАВО НА ВСТУПЛЕНИЕ
RIGHT OUTER JOIN выполняет соединение между двумя таблицами, для которых требуется явное предложение соединения, но не исключает непревзойденные строки из второй таблицы.
Пример:
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.EMP RIGHT OUTER JOIN SCOTT.DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;
Поскольку включены SCOTT.DEPT строки SCOTT.DEPT , но несогласованные строки SCOTT.EMP не являются, приведенное выше эквивалентно следующему выражению, использующему LEFT OUTER JOIN .
SELECT
ENAME,
DNAME,
EMP.DEPTNO,
DEPT.DEPTNO
FROM
SCOTT.DEPT RIGHT OUTER JOIN SCOTT.EMP
ON DEPT.DEPTNO = EMP.DEPTNO;
Ниже приведен пример правой внешней связи между двумя таблицами:
Пример таблицы: EMPLOYEE
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Пример таблицы: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Теперь, если вы выполните запрос:
SELECT
*
FROM
EMPLOYEE RIGHT OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Выход:
+-----------+---------+---------+--------------+
| NAME | DEPTNO | DEPTNO | DEPTNAME |
+-----------+---------+---------+--------------+
| A | 2 | 2 | FINANCE |
| B | 1 | 1 | ACCOUNTING |
| D | 2 | 2 | FINANCE |
| E | 1 | 1 | ACCOUNTING |
| F | 1 | 1 | ACCOUNTING |
| | | 5 | MARKETING |
| | | 6 | HR |
+-----------+---------+---------+--------------+
Эквивалент синтаксиса Oracle (+) для запроса:
SELECT *
FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.DEPTNO(+) = DEPT.DEPTNO;
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
FULL OUTER JOIN выполняет соединение между двумя таблицами, для которых требуется явное предложение соединения, но не исключает непревзойденные строки в любой таблице. Другими словами, он возвращает все строки в каждой таблице.
Пример:
SELECT
*
FROM
EMPLOYEE FULL OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Вот пример полного внешнего соединения между двумя таблицами:
Пример таблицы: EMPLOYEE
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Пример таблицы: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Теперь, если вы выполните запрос:
SELECT
*
FROM
EMPLOYEE FULL OUTER JOIN DEPT
ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;
Выход
+-----------+---------+---------+--------------+
| NAME | DEPTNO | DEPTNO | DEPTNAME |
+-----------+---------+---------+--------------+
| A | 2 | 2 | FINANCE |
| B | 1 | 1 | ACCOUNTING |
| C | 3 | | |
| D | 2 | 2 | FINANCE |
| E | 1 | 1 | ACCOUNTING |
| F | 1 | 1 | ACCOUNTING |
| G | 4 | | |
| H | 4 | | |
| | | 6 | HR |
| | | 5 | MARKETING |
+-----------+---------+---------+--------------+
Здесь столбцы, которые не совпадают, были сохранены NULL.
ANTIJOIN
Antijoin возвращает строки с левой стороны предиката, для которых нет соответствующих строк в правой части предиката. Он возвращает строки, которые не соответствуют (НЕ В) подзапросам с правой стороны.
SELECT * FROM employees
WHERE department_id NOT IN
(SELECT department_id FROM departments
WHERE location_id = 1700)
ORDER BY last_name;
Вот пример Anti Join между двумя таблицами:
Пример таблицы: EMPLOYEE
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| A | 2 |
| B | 1 |
| C | 3 |
| D | 2 |
| E | 1 |
| F | 1 |
| G | 4 |
| H | 4 |
+-----------+---------+
Пример таблицы: DEPT
+---------+--------------+
| DEPTNO | DEPTNAME |
+---------+--------------+
| 1 | ACCOUNTING |
| 2 | FINANCE |
| 5 | MARKETING |
| 6 | HR |
+---------+--------------+
Теперь, если вы выполните запрос:
SELECT
*
FROM
EMPLOYEE WHERE DEPTNO NOT IN
(SELECT DEPTNO FROM DEPT);
Выход:
+-----------+---------+
| NAME | DEPTNO |
+-----------+---------+
| C | 3 |
| H | 4 |
| G | 4 |
+-----------+---------+
Вывод показывает, что только строки таблицы EMPLOYEE, из которых DEPTNO не присутствуют в таблице DEPT.
SEMIJOIN
Например, запрос semijoin можно найти, чтобы найти все отделы с хотя бы одним сотрудником, чья зарплата превышает 2500.
SELECT * FROM departments
WHERE EXISTS
(SELECT 1 FROM employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500)
ORDER BY department_name;
Это более эффективно, чем варианты полного объединения, поскольку внутреннее присоединение к сотрудникам, а затем предоставление предложения о том, что зарплата должна быть больше 2500, может возвращать тот же отдел несколько раз. Скажем, если в отделе пожарной охраны есть n сотрудников с зарплатой 3000, select * from departments, employees с необходимым объединением в ids, а наше предложение where вернет пожарную службу n раз.
ПРИСОЕДИНИТЬСЯ
Операция JOIN выполняет соединение между двумя таблицами, исключая любые несогласованные строки из первой таблицы. Начиная с Oracle 9i forward, JOIN эквивалентен функции INNER JOIN . Эта операция требует явного условия соединения, в отличие от операторов CROSS JOIN и NATURAL JOIN .
Пример:
select t1.*,
t2.DeptId
from table_1 t1
join table_2 t2 on t2.DeptNo = t1.DeptNo
Документация Oracle:
ПРИРОДНОЕ СОЕДИНЕНИЕ
ПРИРОДНЫЙ ПРИСОЕДИНЕНИЕ не требует никакого объяснительного условия соединения; он строит один на основе всех полей с тем же именем в объединенных таблицах.
create table tab1(id number, descr varchar2(100));
create table tab2(id number, descr varchar2(100));
insert into tab1 values(1, 'one');
insert into tab1 values(2, 'two');
insert into tab1 values(3, 'three');
insert into tab2 values(1, 'ONE');
insert into tab2 values(3, 'three');
Соединение будет выполнено по идентификаторам полей и DESCR, общим для обеих таблиц:
SQL> select *
2 from tab1
3 natural join
4 tab2;
ID DESCR
---------- ----------
3 three
Столбцы с разными именами не будут использоваться в состоянии JOIN:
SQL> select *
2 from (select id as id, descr as descr1 from tab1)
3 natural join
4 (select id as id, descr as descr2 from tab2);
ID DESCR1 DESCR2
---------- ---------- ----------
1 one ONE
3 three three
Если объединенные таблицы не имеют общих столбцов, будет выполнен JOIN без каких-либо условий:
SQL> select *
2 from (select id as id1, descr as descr1 from tab1)
3 natural join
4 (select id as id2, descr as descr2 from tab2);
ID1 DESCR1 ID2 DESCR2
---------- ---------- ---------- ----------
1 one 1 ONE
2 two 1 ONE
3 three 1 ONE
1 one 3 three
2 two 3 three
3 three 3 three