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?
Possibly related:
- SQL Joins as Seen on a Diagram
- Back to basics: self joins
- Back to basics: cross joins
- Taxes, new laptop and back to basics
- Give Me One Minute And I’ll Tell You If You Are Liberal Or Conservative
Tagged concepts, join | Post a Comment
Hope everything is working out...
Hi, Addie
April 27th, 2006, at 1:40 am #I’ve been thinking about equi-joins.
What if we have an IN clause? Will it be an equi or nonequijoin?
Thanks.
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.
April 27th, 2006, at 11:53 am #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.
May 2nd, 2007, at 1:35 pm #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).
May 2nd, 2007, at 11:17 pm #Hi Eddie, Will you please tell about <> or != operator, does that make an equi join or non equi join?
May 10th, 2007, at 4:46 am #What is the difference between Equi and Inner Joins?
October 10th, 2007, at 11:47 pm #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
March 17th, 2008, at 8:13 am #