Table of contents
1.
Introduction
2.
Joins
3.
Equi Join
3.1.
Syntax
3.1.1.
Explanation
3.2.
Example
3.2.1.
SQL Command
3.2.2.
Output
3.3.
Use Cases for Equi Join
4.
Non-Equi Join
4.1.
Syntax
4.1.1.
Explanation
4.2.
Example
4.2.1.
SQL Command
4.2.2.
Output
4.3.
Use Cases for Non-Equi Join
5.
Frequently Asked Questions
5.1.
What is an Equi Join in SQL?
5.2.
What is a Non-Equi Join in SQL?
5.3.
When should I use an Equi Join in SQL?
5.4.
When should I use a Non-Equi Join in SQL?
5.5.
Can I use more than one column to join tables in SQL?
6.
Conclusion
Last Updated: Mar 27, 2024
Medium

Equi Join and Non-Equi Join in SQL

Author Pallavi singh
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Hello Ninjas! SQL, or Structured Query Language, is a popular programming language for manipulating and managing data stored in relational databases. One of the remarkable features of SQL is the ability to join tables together based on common fields, allowing for complex queries and data analysis from multiple sources. Two types of SQL joins are Equi Join and Non-Equi Join, which match records (mostly columns) in tables based on different types of relationships.

Equi Join and Non-Equi Join in SQL

Joins

In a Database Management System (DBMS), a Join is a relational operation that combines two or more tables on the basis of a common column or set of columns. Joins retrieve data by combining multiple tables in a single query, enabling users to view the data more meaningfully. There are numerous joins in DBMS, like, inner Join, left join, right Join, etc. 

For more information on Join, visit this blog.

Equi Join

An Equi Join is a join operation in SQL that combines two tables based on a matching column between them. Simply put, an Equi Join returns all rows from both tables where the values in the specified columns are equal.

Syntax

SELECT *
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;


OR

SELECT column_name(s)  
FROM table1, table2.
WHERE table1.column_name = table2.column_name;

Explanation

In the above code, table1 and table2 are the two tables being joined, and column_name is the column's name common in both tables. The ON keyword specifies the condition for combining the tables: the equality between the values in the given column.

Example

Let’s create the tables from scratch and then explore both Joins. 
First, we will create a database ‘joins’ where we can create the table.

CREATE DATABASE joins;
USE joins;


Now, we will create a table ‘User’ which has 5 fields.

CREATE TABLE User (
    user_id INT PRIMARY KEY,
    name VARCHAR(255),
    dob DATE,
    gender VARCHAR(10),
    age INT
);

 

Now inserting the data in the ‘User’ table.

INSERT INTO User (user_id, name, dob, gender, age)
VALUES 
    (1, 'John Smith', '1990-05-10', 'Male', 31),
    (2, 'Sarah Johnson', '1995-02-20', 'Female', 26),
    (3, 'Michael Brown', '1988-12-15', 'Male', 33),
    (4, 'Emily Davis', '1992-08-08', 'Female', 29),
    (5, 'Robert Lee', '1991-03-22', 'Male', 30),
    (6, 'Jessica Clark', '1994-10-05', 'Female', 27),
    (7, 'David Kim', '1987-07-14', 'Male', 34),
    (8, 'Linda Jones', '1993-01-18', 'Female', 28);

 

User Table

Users table

Now, we will create a table ‘Orders’ which has 4 fields. Notice we have ‘user_id’ common in both tables.

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    item VARCHAR(255),
    quantity INT
);

 

Inserting the 10 rows of data in the ‘Orders’ table.

INSERT INTO Orders (order_id, user_id, item, quantity) 
VALUES 
    (1, 1, 'Apple', 5),
    (2, 2, 'Banana', 2),
    (3, 3, 'Orange', 3),
    (4, 1, 'Pear', 1),
    (5, 4, 'Grapes', 4),
    (6, 2, 'Watermelon', 2),
    (7, 5, 'Pineapple', 1),
    (8, 3, 'Mango', 3),
    (9, 4, 'Kiwi', 2),
    (10, 1, 'Strawberry', 6);


Orders Table

Orders table

Now, let's see the SQL command to select all columns from both tables where the "user_id" values match.

SQL Command

SELECT *
FROM User
JOIN Orders ON User.user_id = Orders.user_id;

 

The JOIN keyword specifies that we want to combine data from both tables, and the ON keyword specifies the condition for the Join, which is that the "user_id" values must match. We can adjust the columns we want to select by listing them explicitly in the SELECT clause.

Output

equi join output

Use Cases for Equi Join

Equi join is used to combine rows from two tables based on a matching condition between two columns. Here are a few cases:

  • Combining data from different tables: Equi Join can be used to combine data from two tables to get a wider view of information. For example, we can match the customer ID in the customer table with the customer ID in the order table to combine the relevant data.
     
  • Analyzing merged data: Equi join can be used to analyze data by combining two tables based on some common criteria. For example, we can match the product ID in the sales table with the product ID in the product table to get additional information about the products that were sold.

Non-Equi Join

A Non-Equi Join is a join operation in SQL that combines two tables based on a condition that is not specifically an equality operator (i.e., not "equals to"). Instead, the condition can use any other comparison operators such as less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=), or not equal to (<>).

Syntax

SELECT *
FROM table1
JOIN table2
ON table1.column_name >= table2.column_name;

Explanation

In the above code, we are joining table1 and table2 because the values in table1.column_name are greater than or equal to the values in table2.column_name. This would return all rows where this condition is true.

Example

We will use the same above-defined tables.

SQL Command

SELECT *
FROM User
JOIN Orders ON User.user_id < Orders.user_id
WHERE User.age > 25;

 

The above command will select all columns from both tables where the "user_id" value in the "User" table is less than the "user_id" value in the "Orders" table, and the user's age is greater than 25. The "< "operator is used as the join condition instead of "=", and the WHERE clause specifies the additional condition that the user's age must be greater than 25. We can adjust the columns we want to select by listing them explicitly in the SELECT clause. 

Output

Non equi join output

Use Cases for Non-Equi Join

Non-Equi Join combines rows from two tables based on a non-equality condition between two columns. Here are a few cases:

  • Filtering data based on a range: Non-Equi Join can filter data based on a range. For example, if you have a sales and product data table, you can use a Non-Equi Join to filter the sales data based on a range of product prices.
     
  • Finding gaps in data: Non-Equi Join can be used to find gaps in data. For example, if we have a table of customer orders and a table of product inventory, you can use a non-Equi Join to find gaps in the inventory data where there are no matching orders.
     
  • Analyzing trends over time: Non-Equi Join can be used to analyze trends. For example, if you have a table for customer orders and a table of product prices, you can use a Non-Equi Join to analyze changes in product prices over time.
     
  • Combining multiple conditions: Non-Equi Join can connect multiple conditions in a single Join statement. For example, you can use a Non-Equi Join to combine customer data from multiple tables based on a range of ages and income levels.

 

Recommended topics, Schema in DBMS and Tcl Commands in SQL

Must Read SQL Clauses

Frequently Asked Questions

What is an Equi Join in SQL?

An Equi Join is a Join in SQL that uses the equality operator (=) to join two tables based on one or more columns with matching values.

What is a Non-Equi Join in SQL?

A Non-Equi Join is a join in SQL that uses comparison operators other than the equality operator (=) to join two tables based on one or more columns with matching values.

When should I use an Equi Join in SQL?

We should use an Equi Join in SQL when we want to combine two tables based on a single column with matching values.

When should I use a Non-Equi Join in SQL?

We should use a Non-Equi Join in SQL when combining two tables based on a range of values, such as all the values greater than a certain number.

Can I use more than one column to join tables in SQL?

Yes, we can use multiple columns to join tables in SQL as long as the columns have matching values.

Conclusion

In this article, we discussed Joins in SQL. The two main types of joins in SQL are Equi Join and Non-Equi Join. Both of them have their use cases and performance considerations. When using Joins, it's essential to consider the accuracy of Join conditions and performance implications before executing the query. With these considerations, joins can significantly simplify data analysis and provide valuable information regarding the business. 

For more information on Joins in SQL, visit our other blogs:

You may refer to our Guided Path on Code Studios to enhance your skill set on DSACompetitive ProgrammingSystem Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning, Ninjas!

Live masterclass