|
<< Click to Display Table of Contents >> Equi-join |
  
|
|
<< Click to Display Table of Contents >> Equi-join |
|
Joins two tables based on column values common between the two, excluding non-matches.
SELECT column_list
FROM table_reference, table_reference [, table_reference...]
WHERE predicate [AND predicate...]
Use equi-join to join two tables, a source and joining table, that have values from one or more columns in common. One or more columns from each table are compared in the WHERE clause for equal values. For rows in the source table that have a match in the joining table, the data for the source table rows and matching joining table rows are included in the result set. Rows in the source table without matches in the joining table are excluded from the joined result set. In the statement below, the CUSTOMER and ORDERS tables are joined based on values in the CUSTNO column, which each table contains.
SELECT *
FROM Customer C, Orders O
WHERE (C.CustNo = O.CustNo)
More that one table may be joined with an equi-join. One column comparison predicate in the WHERE clause is required for each column compared to join each two tables. The statement below joins the CUSTOMER table to ORDERS, and then ORDERS to ITEMS. In this case, the joining table ORDERS acts as a source table for the joining table ITEMS.
SELECT *
FROM Customer C, Orders O, Items I
WHERE (C.CustNo = O.CustNo) AND
(O.OrderNo = I.OrderNo)
Tables may also be joined using a concatenation of multiple column values to produce a single value for the join comparison predicate. Here, the ID1 and ID2 columns in JOINING are concatenated and compared with the values in the single column ID in SOURCE.
SELECT *
FROM Source S, Joining J
WHERE (S.ID = J.ID1 || J.ID2)
An ORDER BY clause in equi-join statements can use columns from any table specified in the FROM clause to sort the result set.
