Archive

Posts Tagged ‘Cross Join’

Example for All Types of SQL JOIN (Inner Join, Cross Join, Outer Join, Self Join)

September 9, 2012 Leave a comment

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.

Employee Table

LastName

DepartmentID

Rafferty

31

Jones

33

  Steinberg

33

Robinson

34

Smith

34

Jasper

NULL

Department Table

DepartmentID

DepartmentName

31

Sales

33

Engineering

34

Clerical

35

Marketing

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:
SELECTFROM   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

cross joincartesian 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).

SELECTFROM   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

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

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

 

%d bloggers like this: