Example for All Types of SQL JOIN (Inner Join, Cross Join, Outer Join, Self Join)
Definition and very simple examples for each JOINS in SQL
Here we are demonstrating with examples all types of JOINS in SQL Server. First of all we created sample tables and as per the table data we are explaining each SQL JOINS with most understandable examples.
Sample tables
In the following tables, Department.DepartmentID is the primary key, while Employee.DepartmentID is a foreign key.
|
| ||||||||||||||||||||||||||||
Inner join
An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. 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.
SQL specifies two different syntactical ways to express joins. The first, called “explicit join notation”, uses the keyword JOIN, whereas the second uses the “implicit join notation”. The implicit join notation lists the tables for joining in the FROM clause of a SELECT statement, using commas to separate them. Thus, it specifies a cross-join, and the WHERE clause may apply additional filter-predicates. Those filter-predicates function comparably to join-predicates in the explicit notation.
One can further classify inner joins as equi-joins, as natural joins, or as cross-joins (see below).
Example of an explicit inner join:
SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID Is equivalent to: SELECT * FROM employee, department WHERE employee.DepartmentID = department.DepartmentID
Explicit Inner join result:
Employee.
LastName
|
Employee.
DepartmentID
|
Department.
DepartmentName
|
Department.
DepartmentID
|
Smith
|
34
|
Clerical
|
34
|
Jones
|
33
|
Engineering
|
33
|
Robinson
|
34
|
Clerical
|
34
|
Steinberg
|
33
|
Engineering
|
33
|
Rafferty
|
31
|
Sales
|
31
|
Types of inner joins
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.
SELECT Employee.lastName, Employee.DepartmentID, Department.DepartmentName FROM Employee INNER JOIN Department ON Employee.DepartmentID = Department.DepartmentID; SQL provides optional syntactic sugar for expressing equi-joins, by way of the USING construct (Feature ID F402): SELECT Employee.lastName, DepartmentID, Department.DepartmentName FROM Employee INNER JOIN Department USING(DepartmentID);
The USING clause is supported by MySQL, Oracle and PostgreSQL.
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.
The above sample query for inner joins can be expressed as a natural join in the following way:
SELECT * FROM employee NATURAL JOIN department
The result appears slightly different, however, because only one DepartmentID column occurs in the joined table.
DepartmentID
|
Employee.LastName
|
Department.DepartmentName
|
34
|
Smith
|
Clerical
|
33
|
Jones
|
Engineering
|
34
|
Robinson
|
Clerical
|
33
|
Steinberg
|
Engineering
|
31
|
Rafferty
|
Sales
|
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 join-condition is absent in statement.
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.
Example of an explicit cross join:
SELECT * FROM employee CROSS JOIN department
Example of an implicit cross join:
SELECT *
FROM employee, department;
Employee.
LastName
|
Employee.
DepartmentID
|
Department.
DepartmentName
|
Department.
DepartmentID
|
Rafferty
|
31
|
Sales
|
31
|
Jones
|
33
|
Sales
|
31
|
Steinberg
|
33
|
Sales
|
31
|
Smith
|
34
|
Sales
|
31
|
Robinson
|
34
|
Sales
|
31
|
Jasper
|
NULL
|
Sales
|
31
|
Rafferty
|
31
|
Engineering
|
33
|
Jones
|
33
|
Engineering
|
33
|
Steinberg
|
33
|
Engineering
|
33
|
Smith
|
34
|
Engineering
|
33
|
Robinson
|
34
|
Engineering
|
33
|
Jasper
|
NULL
|
Engineering
|
33
|
Rafferty
|
31
|
Clerical
|
34
|
Jones
|
33
|
Clerical
|
34
|
Steinberg
|
33
|
Clerical
|
34
|
Smith
|
34
|
Clerical
|
34
|
Robinson
|
34
|
Clerical
|
34
|
Jasper
|
NULL
|
Clerical
|
34
|
Rafferty
|
31
|
Marketing
|
35
|
Jones
|
33
|
Marketing
|
35
|
Steinberg
|
33
|
Marketing
|
35
|
Smith
|
34
|
Marketing
|
35
|
Robinson
|
34
|
Marketing
|
35
|
Jasper
|
NULL
|
Marketing
|
35
|
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).
Left outer join
The result of a left outer join (or simply left join) for tables 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).
SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
Employee.
LastName
|
Employee.
DepartmentID
|
Department.
DepartmentName
|
Department.
DepartmentID
|
Jones
|
33
|
Engineering
|
33
|
Rafferty
|
31
|
Sales
|
31
|
Robinson
|
34
|
Clerical
|
34
|
Smith
|
34
|
Clerical
|
34
|
Jasper
|
NULL
|
NULL
|
NULL
|
Steinberg
|
33
|
Engineering
|
33
|
Right outer joins
A right outer join (or right join) closely resembles a left outer join, except with 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 A.
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). SELECT * FROM employee RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
Employee.
LastName
|
Employee.
DepartmentID
|
Department.
DepartmentName
|
Department.
DepartmentID
|
Smith
|
34
|
Clerical
|
34
|
Jones
|
33
|
Engineering
|
33
|
Robinson
|
34
|
Clerical
|
34
|
Steinberg
|
33
|
Engineering
|
33
|
Rafferty
|
31
|
Sales
|
31
|
NULL
|
NULL
|
Marketing
|
35
|
In practice, explicit right outer joins are rarely used, since they can always be replaced with left outer joins and provide no additional functionality.
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.
Example full outer join:
SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
Employee.
LastName
|
Employee.
DepartmentID
|
Department.
DepartmentName
|
Department.
DepartmentID
|
Smith
|
34
|
Clerical
|
34
|
Jones
|
33
|
Engineering
|
33
|
Robinson
|
34
|
Clerical
|
34
|
Jasper
|
NULL
|
NULL
|
NULL
|
Steinberg
|
33
|
Engineering
|
33
|
Rafferty
|
31
|
Sales
|
31
|
NULL
|
NULL
|
Marketing
|
35
|
Self-join
A self-join is joining a table to itself.
Example
A query to find all pairings of two employees in the same country is desired. If you had two separate tables for employees and a query which requested employees in the first table having the same country as employees in the second table, you could use a normal join operation to find the answer table. However, all the employee information is contained within a single large table.
Considering a modified Employee table such as the following:
Employee Table
| |||
EmployeeID
|
LastName
|
Country
|
DepartmentID
|
123
|
Rafferty
|
Australia
|
31
|
124
|
Jones
|
Australia
|
33
|
145
|
Steinberg
|
Australia
|
33
|
201
|
Robinson
|
United States
|
34
|
305
|
Smith
|
United Kingdom
|
34
|
306
|
Jasper
|
United Kingdom
|
NULL
|
An example solution query could be as follows:
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country FROM Employee F, Employee S WHERE F.Country = S.Country AND F.EmployeeID < S.EmployeeID ORDER BY F.EmployeeID, S.EmployeeID;
Which results in the following table being generated.
Employee Table after Self-join by Country
| ||||
EmployeeID
|
LastName
|
EmployeeID
|
LastName
|
Country
|
123
|
Rafferty
|
124
|
Jones
|
Australia
|
123
|
Rafferty
|
145
|
Steinberg
|
Australia
|
124
|
Jones
|
145
|
Steinberg
|
Australia
|
305
|
Smith
|
306
|
Jasper
|
United Kingdom
|
