This clause is used to retrieve data from two or more 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.
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.
Nested JOINs
Any of the JOIN types can be mixed in any sequence to create a nested join. The nested joins still require that you specify one column from each table to join on. When nesting joins, it is important to use the correct sequence of parenthesis, along with a correlation for each join.
In the example below, notice the two sets of parenthesis, which all begin after the FROM keyword and end after the linking columns. Also note the "J1" and "J2" correlations specified for each join.
SELECT ALL FROM (( TABLE1 t1 +
INNER JOIN TABLE2 t2 ON t1.FieldT2=t2.FieldT2) J1 +
INNER JOIN TABLE3 t3 ON t3.FieldT3=j1.FieldT3) J2
Examples
INNER JOIN Example:
The following example list an employee's total sales for each day.
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
Nested INNER JOIN Example:
The following example lists a specific product, all of the locations where it resides, and the components used within the product.
SELECT ProdName, Location, CompID FROM +
((Product t1 INNER JOIN ProdLocation t2 ON t1.Model=t2.Model) J1 +
INNER JOIN CompUsed t3 ON t3.Model=j1.Model) J2 +
WHERE Model = 'CX3000'
ProdName Location CompID
----------------------------------- -------- --------
Standard SVGA Color PC A-1 X1010
Standard SVGA Color PC A-1 X2000
Standard SVGA Color PC A-1 X3000
Standard SVGA Color PC B-1 X1010
Standard SVGA Color PC B-1 X2000
Standard SVGA Color PC B-1 X3000
Standard SVGA Color PC C-10 X1010
Standard SVGA Color PC C-10 X2000
Standard SVGA Color PC C-10 X3000
