INNER JOIN

INNER JOIN

This clause is used to retrieve data from two tables.

Options

.column1
Defines the column on which to link.

.column2
Defines the column on which to link.

corr_name
A correlation name is an alias or nickname for a table. It lets you refer to the same table twice in one command, use a shorter name, and explicitly refer to a column when referring to the same column if that column appears in more than one table.

FROM lefttblview
Specifies the left table or view.

lefttblview
Explicitly defines the column on which to link the left table name or view.

INNER JOIN righttblview
Specifies the right table or view.

righttblview
Explicitly defines the column on which to link the right table name or view.

WHERE clause
Limits rows of data. See "WHERE Clause" entry.

About JOIN

When you perform a SQL JOIN, you specify one column from each table to join on. These two columns contain data that is shared across both tables. You can use multiple joins in the same SQL statement to query data from as many tables as you like.

JOIN Types

Depending on your requirements, you can do an "INNER" join or an "OUTER" join. The differences are:

INNER JOIN: This will only return rows when there is at least one row in both tables that match the join condition.

LEFT OUTER JOIN: This will return rows that have data in the left table (left of the JOIN keyword), even if there's no matching rows in the right table.

RIGHT OUTER JOIN: This will return rows that have data in the right table (right of the JOIN keyword), even if there's no matching rows in the left table.

FULL OUTER JOIN: This will return all rows, as long as there's matching data in one of the tables.

Example (INNER JOIN)

The following example lists all of the employees and their total sales, including those employees who have not yet completed a sale.

SELECT t1.empid, t2.netamount, t2.transdate FROM employee t1 +
INNER JOIN transmaster t2 ON t1.empid = t2.empid +
WHERE empid = 129

t1.empid   t2.netamount    t2.TransDate
---------- --------------- ------------
        129       $3,080.00 07/02/2003
        129       $5,385.00 07/08/2003
        129       $6,160.00 07/11/2003
        129       $5,575.00 08/24/2003
        129      $10,445.00 08/24/2003
        129      $10,175.00 08/25/2003
        129       $2,195.00 08/27/2003