Term: JOIN

Definition:
The JOIN clause is used to combine records from two or more tables in a database. This is done by combining fields from the joined tables by using values common to each table. In other words, JOINS are used to find data from two or more tables based on the relationship between certain columns in these tables.

Standard ANSII SQL specifies four types of JOINs:

INNER
OUTER
LEFT
RIGHT

In certain cases, a TABLE or VIEW can JOIN to itself in what is called a "SELF JOIN".

The join predicate in the SQL statement is what contains the rules or conditions that identify what records are to be joined. When the SQL is evaluated, the predicate conditions are tested. If the predicate conditions are true then the combined record is produced.

An INNER JOIN is the most common join operation and is generally the default join type. An inner join creates a result by combining column values of two tables (A and B) based upon the join predicate. The join query compares each row of table A with each row of table B to find all pairs of rows which satisfy the join predicate.

The INNER JOIN example below will only return a result when there is at least one valid match in both tables. If there are rows in "Users" that do not have matches in "Sales", those rows will not be in the result set (not returned).

Inner Join Example:

SELECT Users.Last_Name, Users.First_Name, Sales.Order_Number

  FROM Users

  INNER JOIN Sales

  ON Users.U_Id=SALES.U_Id

ORDER_BY Users.Last_Name

An OUTER JOIN does not require each record in the two joined tables to have a matching record. The joined table keeps each record even if no other matching record exists. Outer joins can be classified further into "left outer joins", "right outer joins", and "full outer joins", depending on which tables the rows are retained from (left, right, or both).

The OUTER JOIN clause is a variation of the SQL JOIN clause which enables a SELECT statement to process more than one table. Use the OUTER JOIN to return results from multiple tables when there aren't any matches between the JOINed tables. The example OUTER JOIN below will select from the Users and Orders tables all of the distinct users (first and last names) and the total amount they've spent:

SELECT Users.FirstName, Users.LastName, SUM(Orders.OrdersAmount) AS OrdersPerUser

  FROM Users JOIN Orders

  ON Users.UserID = Orders.UserID

GROUP_BY Users.FirstName, Users.LastName;

NOTE: A "cartesian product" will result if there are no matching join columns specified. This means that a row will be returned for EVERY combination of the two tables. If one table has 1,000 rows and the second table has 3,000 rows the result set will contain 3,000,000 (three million!) rows. This is probably NOT what you want.

A SELF JOIN is a query in which a table is joined (compared) to itself. Self-joins are useful to compare values in a specific column with other values in the same column in the same table (related data that is stored in a single table). The self join can be done by using table aliases to treat one table as though it were a different table and then joining them together. Another common use for self-joins is obtaining running counts and totals in a query.

The general rule for writing self-joins is:

Write the query doing the selects from the same table listed twice with different aliases
Set up the comparison pedicates or tests
Remove any cases where a given value would be equal to itself.

This example self join selects users who have the same ZipCode (the column names in this example are 'UserName', 'Address', 'City', and 'ZipCode'):

SELECT DISTINCT c1.UserName, c1.Address, c1.City, c1.ZipCode

FROM Users AS c1, Users AS c2

  WHERE c1.ZipCode = c2.ZipCode

  AND c1.UserName <> c2.UserName

ORDER_BY c1.ZipCode, c1.UserName;

Related Links:

Oracle Join Examples & Syntax

Related Code Snippets:

Date Functions: WHERE Clause Joins - Examples of using dates in WHERE Clause Joins.
WHERE Clause: Joins - The WHERE Clause servers two different purposes. One is to join two or more ...
INNER JOIN example and syntax - The INNER JOIN keyword return rows when there is at least one matc...
LEFT JOIN example and syntax - The LEFT JOIN (also called LEFT OUTER JOIN) keyword returns all row...
RIGHT JOIN example and syntax - The RIGHT JOIN (or RIGHT OUTER JOIN) keyword Return all rows from ...
FULL JOIN example and syntax - The FULL JOIN keyword return rows when there is a match in one of t...
Self-join example and syntax - A self-join is a way of joining a table to itself. The example ...
ANSI Joins: INNER JOIN - Examples of an ANSI-style INNER JOIN.
ANSI Joins: CROSS JOIN - Example of an ANSI-style CROSS JOIN.
ANSI Joins: OUTER JOIN - Example of an ANSI-style OUTER JOIN
ANSI Joins: FULL JOIN - Example of an ANSI-style FULL JOIN.
INDEXES: Bitmap Join Indexes - In a Bitmap Index, each distinct value for the specified column is ...