What's the difference between an SQL inner join and equijoin?

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause Using the SQL date function to find aggregate totals by month Oracle development languages How to check SQL query construction with the Mimer Validator Understanding SQL string functions The top advice from Oracle experts in 2008 Using LEFT OUTER JOIN query to get zero row counts in SQL How to return multiple values for THEN clause in an SQL CASE expression Can I concatenate row values in SQL? Should I try to avoid a LEFT OUTER JOIN in SQL? Tips for derived tables in SQL and using FULL OUTER JOINs How to write an SQL query for two foreign keys to the same table How to create an SQL CHECK constraint for two letters

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

They are unrelated.

Almost every join is an equijoin, because the condition for matching rows is based on the equality of two values—one from each of the tables being joined. So that's what makes it an equijoin: the ON condition is equality. This includes inner joins and all three types of outer joins.

Inner joins, on the other hand, can be based on equality to match rows, or on some other condition entirely. If it's not an equijoin, then it's usually called a theta join, although to be precise, an equijoin is just one of the possible theta joins; other theta joins use less than, less than or equal, etc., as the comparison operator. As long as the comparison evaluates to TRUE, the matched rows qualify for the join.

Here's an example. Suppose we have a table of cities, and amongst all the columns of data for a city, is one called avg_temp, which gives the city's average temperature. Then we could list our cities, and for each city, give a count of how many cities are hotter.

SELECT this.cityname
     , this.avg_temp
     , COUNT(others.cityname) AS hotter_cities
  FROM cities AS this
LEFT OUTER
  JOIN cities AS others
    ON others.avg_temp > this.avg_temp
GROUP
    BY this.cityname
     , this.avg_temp

This must, of course, be an outer join, otherwise the hottest city would have no matching rows (no other cities would be hotter).

Look at the join condition in the ON clause. It is not equality, so this is a theta join.