msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Back to basics: Equi and non-equijoins

The join condition determines whether a join is an equijoin or a non-equijoin. An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns. When a join condition relates two tables by an operator other than equality, it is a non-equijoin. A query may contain equijoins as well as non-equijoins.

Equijoins are the most commonly used. An example of an equijoin:

SELECT e.first_name, d.department_name
  FROM employees e INNER JOIN departments d
       ON e.department_id = d.department_id
/
       
     FIRST_NAME              DEPARTMENT_NAME        
-------------------- ------------------------------ 
Steven               Executive                      
Neena                Executive                      
Lex                  Executive                      
Alexander            IT                             
Bruce                IT                             

Non-equijoins are less frequently used. An example of a non-equijoin:

SELECT zip_codes.zip_code, zones.ID AS zip_zone,
       zones.low_zip, zones.high_zip
  FROM zones INNER JOIN zip_codes
       ON zip_codes.zip_code BETWEEN zones.low_zip
                                 AND zones.high_zip
/

ZIP_CODE ZIP_ZONE LOW_ZIP HIGH_ZIP
-------- -------- ------- --------
57000    1        57000   57999 
84006    2        84000   84999 

Can you think of another non-equijoin example?


Filed in Joins, Oracle on 26 Apr 06 | Tags: ,


Reader's Comments

  1. |

    Hi, Addie I’ve been thinking about equi-joins. What if we have an IN clause? Will it be an equi or nonequijoin? Thanks.

  2. |

    Good question Vladimir. For example, is the following query an equijoin?

    SELECT e.first_name,
           d.department_name
      FROM employees e INNER JOIN departments d
           ON e.department_id IN (d.department_id, 9999)
    

    An equijoin is a join with a join condition containing an equality operator. Since the above query does not have the equality operator (=) in the join condition, that makes it a non-equijoin.

  3. |

    So if you used the IN clause it is a non-equijoin, but if you rewrote it as multiple equality statements separated by OR, it IS and equijoin?

    My first opinion would definitely be to refer to IN as an equijoin.

  4. |

    Don, I see your point. But, strictly following the definition of the equi-join which says that it “is a join with a join condition containing an equality operator”, I still think that it is not an equi-join (unless IN is an equality operator).

  5. |

    Hi Eddie, Will you please tell about <> or != operator, does that make an equi join or non equi join?

  6. |

    What is the difference between Equi and Inner Joins?

  7. |

    Equi joins and inner joins are terms using different classification methods. Just like one can classify movies awarded by Oscars or not and by movie duration time. Whether movie is awarded with Oscar has nothing to do with movie duration time and vice versa. I’ve tried to show the classification of joins using ER meta model here: http://gplivna.blogspot.com/2008/01/sql-join-types-im-studying-bit-sql.html