Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle JOIN Clause      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 269 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?