A Join A Day – Equi-Join vs. Nonequi-Join

Introduction

This is the eighteenth post in my A Join A Day series about SQL Server Joins. Make sure to let me know how I am doing or ask your burning join related questions by leaving a comment below.

So far all examples in this series have joined two tables on one or more columns using the "equals" comparison. If all columns in the ON clause are matched on equality the join is called "Equi-Join". If one or more columns are using an inequality comparison, the join is called "Nonequi-Join".

(Non)Equi-Join Example

For an example let's reuse our tables TblA and TblB:

The type of query we have looked at several times by now is an Equi-Join:

This returns only rows where a.TblA_Val and b.TblB_Val are equal:

Now let's alter the query a little:

Now all combinations of rows from TblA and TblB are returned where the value of a.TblA_Val is greater than the value of b.TblB_Val:

Nonequi-Joins are often used in the context of Self-Joins. With a Nonequi-Join you can do things like calculate a running total:

This query calculates for each row in TblA the sum of all the TblA_Val values that are smaller or equal than the current rows TblA_Val value.

If you are working with SQL Server 2012 however, you can use window functions to do running total calculations much more efficient. A good introduction to windowing functions you can find in this book by Itzik Ben-Gan: Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

(Non)Equi-Join Syntax

Any join that compares one or more columns in the on clause and uses only the "equals" comparison is an Equi-Join. This is independent of the logical join type. So you can have an Inner Equi-Join as well as a Right Anti Semi Equi-Join. Any join that uses another comparison operator for at least one column is a Nonequi-Join.

You can have a mixed situation too, where some columns use the "equals" comparison and other columns use another comparison. Those are commonly also called Nonequi-Joins even though they look more like an Equi-Join with an additional filter to SQL Server.

(Non)Equi-Join Operator

There is no special operator for Equi-Joins or Nonequi-Joins either. All three algorithms can handle Equi-Joins and mixed joins. However, with one special case exception that we will talk about in a few days, only the Nested Loop operator can handle plain Nonequi-Joins.

Summary

A join that is using only the "equals" comparison in the join condition is called Equi-Join. A Join that has at least one comparison in the join condition that is not an "equals" comparison is called a Nonequi-Join. Nonequi Joins are often used for calculations of running totals and similar metrics.

A Join A Day

This post is part of my December 2012 "A Join A Day" blog post series. You can find the table of contents with all posts published so far in the introductory post: A Join A Day – Introduction. Check back there frequently throughout the month.