JOIN, UNION, INTERSECT and EXCEPT in SQL

Содержание

Natural join
Cartesian product (cross join)
Outer join
Theta join
Self join
Semi join
UNION operator

Joining tables in a SELECT query is done using the JOIN statement.

It is also possible to join without a JOIN statement using a WHERE clause using join columns, but this syntax is considered implicit and deprecated.

There are the following types of joins, each of which has its own form of the JOIN operator:

CROSS JOIN – cross or cartesian join
[INNER] JOIN – natural or inner join
LEFT [OUTER] JOIN – left outer join
RIGHT [OUTER] JOIN – right outer join
FULL [OUTER] JOIN – full outer join

There is also a theta join, a self join, and a semi join.

Natural join

A natural join is an inner join or an equijoin.

SELECT employee.*, department.*
FROM employee INNER JOIN department
ON employee.dept_no = department.dept_no;

SELECT employee.*, department.*

FROM employee INNER JOIN department

ON employee.dept_no = department.dept_no;

Here the FROM clause defines the tables to be joined and explicitly specifies the join type – INNER JOIN. The ON clause is part of the FROM clause and specifies the columns to join. The expression employee.dept_no = department.dept_no defines a join condition.

Equivalent query using implicit syntax:

SELECT employee.*, department.*
FROM employee, department
WHERE employee.dept_no = department.dept_no;

SELECT employee.*, department.*

FROM employee, department

WHERE employee.dept_no = department.dept_no;

Joined columns must have identical semantics, i.e. both columns must have the same boolean value. Joined columns do not have to have the same name (or even the same data type), although they often do.

Only rows that have the same value in the joined columns are joined. Rows that do not have such identical values will not be included in the result set at all.

You can join up to 64 tables in a SELECT statement (MS SQL limitation), while one JOIN statement joins only two tables:

SELECT emp_fname, emp_lname
FROM works_on 
JOIN employee ON works_on.emp_no=employee.emp_no
JOIN department ON employee.dept_no=department.dept_no

SELECT emp_fname, emp_lname

FROM works_on

JOIN employee ON works_on.emp_no=employee.emp_no

JOIN department ON employee.dept_no=department.dept_no

Cartesian product (cross join)

The Cartesian product (cross join) connects each row of the first table with each row of the second. The Cartesian product of the first table with n rows and the second table with m rows will result in a table with n × m rows.

SELECT employee.*, department.*
FROM employee CROSS JOIN department;

1 2	SELECT employee., department. FROM employee CROSS JOIN department;

Outer join

An outer join, unlike an inner join, allows you to extract not only rows with the same values of the joined columns, but also rows without matches from one or both tables.

There are three types of outer joins:

left outer join – all rows from the table on the left side of the comparison operator (regardless of whether there are matching rows on the right side) get into the result set, and only rows with matching column values from the table on the right side. In this case, if for a row from the left table there are no matches in the right table, the values of the row in the right table will be assigned NULL

Transact-SQL
SELECT employee_enh.*, department.location FROM employee_enh LEFT OUTER JOIN department ON domicile = location;
1
2
3
SELECT employee_enh.*, department.location
FROM employee_enh LEFT OUTER JOIN department
ON domicile = location;
right outer join – similar to left outer join, but the tables are swapped
Transact-SQL
SELECT employee_enh.domicile, department.* FROM employee_enh RIGHT OUTER JOIN department ON domicile =location;
1
2
3
SELECT employee_enh.domicile, department.*
FROM employee_enh RIGHT OUTER JOIN department
ON domicile =location;
full outer join – a composition of a left and a right outer join: the result set consists of all rows from both tables. If a row in one of the tables does not have a corresponding row in another table, all cells in the row in the second table are set to NULL.

Theta join

The join column comparison condition does not have to be an equality, but can be any other comparison. A join that uses a common join column comparison condition is called a theta join:

SELECT emp_fname, emp_lname, domicile, location
FROM employee_enh JOIN department
ON domicile < location;

SELECT emp_fname, emp_lname, domicile, location

FROM employee_enh JOIN department

ON domicile < location;

Self join

A self-join is a natural join of a table to itself. In this case, one column of the table is compared with itself. Comparing a column to itself means that the table name appears twice in the FROM clause of the SELECT statement. Therefore, it is necessary to be able to refer to the same table name twice. This can be done using at least one alias. The same applies to column names in a join condition in a SELECT statement. Qualified names must be used to distinguish between columns with the same name.

Semi join

A semi join is similar to a natural join, but returns only the set of all rows from one table for which there is one or more matches in another table.

UNION operator

The UNION operator combines the results of two or more queries into a single result set that includes all rows belonging to all queries in the union:

select_1 UNION [ALL] select_2 {[UNION [ALL] select_3]}...

1	select_1 UNION [ALL] select_2 {[UNION [ALL] select_3]}...

The select_1, select_2, … options are SELECT statements that create a union. If the ALL option is used, all rows are displayed, including duplicates. By default, duplicates are removed.

Only compatible tables can be joined using the UNION statement. By compatible tables, we mean that both lists of columns in the selection must contain the same number of columns, and that the corresponding columns must have compatible data types. The result of a join can only be ordered by using the ORDER BY clause in the last SELECT statement. GROUP BY and HAVING clauses can be used with individual SELECT statements, but not within the join itself.

INTERSECT and EXCEPT operators

Two other set operators:

INTERSECT – intersection – a set of rows that belong to both tables
EXCEPT – difference between two tables – all values that belong to the first table and are not present in the second