Equi-join

An equi-join is a join based on equality or matching column values. This equality is indicated with an equal sign (=) as the comparison operator in the WHERE clause, as the following query shows.

Figure 1: Query

SELECT * FROM manufact, stock
   WHERE manufact.manu_code = stock.manu_code;

The query joins the manufact and stock tables on the manu_code column. It retrieves only those rows for which the values of the two columns are equal, some of which the result shows.

Figure 2: Query result

manu_code    SMT
manu_name    Smith
lead_time       3
stock_num    1
manu_code    SMT
description  baseball gloves
unit_price   $450.00
unit         case
unit_descr   10 gloves/case

manu_code    SMT
manu_name    Smith
lead_time       3
stock_num    5
manu_code    SMT
description  tennis racquet
unit_price   $25.00
unit         each
unit_descr   each

manu_code    SMT
manu_name    Smith
lead_time       3
stock_num    6
manu_code    SMT
description  tennis ball
unit_price   $36.00
unit         case
unit_descr   24 cans/case

manu_code    ANZ
manu_name    Anza
lead_time       5
stock_num    5
manu_code    ANZ
description  tennis racquet
unit_price   $19.80
unit         each
unit_descr   each
⋮

In this equi-join, the result includes the manu_code column from both the manufact and stock tables because the select list requested every column.

You can also create an equi-join with additional constraints, where the comparison condition is based on the inequality of values in the joined columns. These joins use a relational operator in addition to the equal sign (=) in the comparison condition that is specified in the WHERE clause.

To join tables that contain columns with the same name, qualify each column name with the name of its table and a period symbol (.), as the following query shows.

Figure 3: Query

SELECT order_num, order_date, ship_date, cust_calls.*
   FROM orders, cust_calls
   WHERE call_dtime >= ship_date
      AND cust_calls.customer_num = orders.customer_num
   ORDER BY orders.customer_num;

The query joins the customer_num column and then selects only those rows where the call_dtime in the cust_calls table is greater than or equal to the ship_date in the orders table. The result shows the combined rows that it returns.

Figure 4: Query result

order_num      1004
order_date     05/22/1998
ship_date      05/30/1998
customer_num   106
call_dtime     1998-06-12 08:20
user_id        maryj
call_code      D
call_descr     Order received okay, but two of the cans of                 
                       ANZ tennis balls within the case were empty
res_dtime      1998-06-12 08:25
res_descr      Authorized credit for two cans to customer,
               issued apology. Called ANZ buyer to report 
               the qa problem.

order_num      1008
order_date     06/07/1998
ship_date      07/06/1998
customer_num   110
call_dtime     1998-07-07 10:24
user_id        richc
call_code      L
call_descr     Order placed one month ago (6/7) not received.
res_dtime      1998-07-07 10:30
res_descr      Checked with shipping (Ed Smith). Order out 
               yesterday-was waiting for goods from ANZ. 
               Next time will call with delay if necessary.

order_num      1023
order_date     07/24/1998
ship_date      07/30/1998
customer_num   127
call_dtime     1998-07-31 14:30
user_id        maryj
call_code      I
call_descr     Received Hero watches (item # 304) instead 
                of ANZ watches
res_dtime      
res_descr      Sent memo to shipping to send ANZ item 304 
               to customer and pickup HRO watches. Should 
               be done tomorrow, 8/1