JOIN in SQL
JOIN is an SQL operator that implements the join operation of relational algebra. It is designed to provide data selection from two tables and include this data in one result set.
The features of the join operation are as follows:
- the schema of the result table includes the columns of both source tables (operand tables), that is, the result schema is a "concatenation" of the operand schemas;
- each row of the result table is a "concatenation" of a row from one operand table with a row of the second operand table;
- if it is necessary to join not two, but several tables, the join operation is applied several times (sequentially).
SELECT
field_name [,... n]
FROM
Table1
{INNER | {LEFT | RIGHT | FULL} OUTER | CROSS } JOIN
Table2
{ON <condition> | USING (field_name [,... n])}
Types of JOINs
(INNER) JOIN The result of joining tables are records that are common to the left and right tables. The order of the tables is not important for the operator because the operator is symmetric.
LEFT (OUTER) JOIN Selects all records in the first table and their corresponding records in the second table. If no records are found in the second table, then an empty result (NULL) is substituted for them. The order of the tables for the operator is important because the operator is not symmetric.
RIGHT (OUTER) JOIN LEFT JOIN with operands in reverse order. The order of the tables for the operator is important because the operator is not symmetric.
FULL (OUTER) JOIN The result of joining tables is all the records that are present in the tables. The order of the tables is not important for the operator because the operator is symmetric.
CROSS JOIN (Cartesian product) When selected, each row of one table is joined with each row of the second table, thus giving all possible combinations of rows of the two tables. The order of the tables is not important for the operator because the operator is symmetric.
Which is better to use JOIN or subqueries?
It is usually better to use a JOIN, since in most cases it is clearer and better optimized by the DBMS (but this cannot be 100% guaranteed). JOIN also has a noticeable advantage over subqueries when the SELECT list contains columns from more than one table.
Subqueries are best used when you need to compute aggregates and use them for comparisons in external queries.
Read also:
Comments
Post a Comment