SQL Joins

SQL Joins

Inner join
An inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column

values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate.

When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome

of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which

satisfy the join predicate. Actual SQL implementations normally use other approaches like a Hash join or a Sort-merge join where possible, since computing the Cartesian product

is very inefficient.

Syntax : Select * from [Table 1] Inner Join [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A Inner Join B On A.ID = B.ID


Equi-join
An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate. Using other

comparison operators (such as <) disqualifies a join as an equi-join. The query shown above has already provided an example of an equi-join:

Syntax : Select * from [Table 1] EQUI JOIN [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A EQUI JOIN B On A.ID = B.ID

Natural join
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the

joined tables. The resulting joined table contains only one column for each pair of equally-named columns.


Syntax : Select * from [Table 1] NATURAL JOIN [Table 2]
E.g. Select * from A NATURAL JOIN B


Cross join
A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records

from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement. In

other words, a cross join combines every row in B with every row in A. The number of rows in the result set will be the number of rows in A times the number of rows in B.
Thus, if A and B are two sets, then the cross join is written as A × B.
The SQL code for a cross join lists the tables for joining (FROM), but does not include any filtering join-predicate.


Syntax : SELECT * FROM [Table 1] CROSS JOIN [Table 2] Or
SELECT * FROM [Table 1], [Table 2]


Outer joins
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists.

Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

1. Left outer join
The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any

matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each

column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join

predicate). If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row

on the right table.

Syntax : Select * from [Table 1] LEFT OUTER JOIN [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A LEFT OUTER JOIN B On A.ID = B.ID


2. Right outer joins
A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the

joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.
A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).

Syntax : Select * from [Table 1] RIGHT OUTER JOIN [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A RIGHT OUTER JOIN B On A.ID = B.ID

3. Full outer join
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on

either side.
For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and

each department which doesn't have an employee.

Syntax : Select * from [Table 1] FULL OUTER JOIN [Table 2] On [Table 1].[Match Field] = [Table 2].[Match Field]
E.g. Select * from A FULL OUTER JOIN B On A.ID = B.ID