Joins are one of the remarkable features of SQL, where its ability to join tables together based on common features and the retrieval of data based on certain conditions allows data for the analysis process. In this tutorial, we will learn about the two types of SQL Joins, i.e. Equi Join and Non Equi Join, that match data in tables using various types of relationships.
Equi Join in SQL
The Equi Join is an SQL join technique that merges two tables based on the matching column between them. To compare the data between two columns, it uses the equality (=) symbol. If the data matches, it fetches it. Equi Join compares each column of data in the initial table to each value in the matching target table, and if they are equal, it retrieves them.
Syntax:
SELECT columns
FROM table01, table02,..
WHERE table01.column = table02.column;Code language: SQL (Structured Query Language) (sql)OR
SELECT *
FROM table01
JOIN table02
ON table1.column = table2.column;Code language: SQL (Structured Query Language) (sql)Where,
- table01,table02: Tables to be joined.
- column: Column name common in both tables.
- JOIN Keyword: Specifies data we want to combine from both tables.
- ON Keyword: Defines the criteria for joining the table (equality of values in the provided column).
Example of Equi Join
Let’s say we have two tables named “User” and “Items”, which contain various details about the user and the items they have purchased.
Now, let’s perform an Equi Join query to select all columns from each table where the common column is “user_id”.
SELECT *
FROM User
JOIN Items ON User.user_id = Items.user_id;Code language: SQL (Structured Query Language) (sql)Output:
We can try the above command with the second syntax:
SELECT *
FROM User, Items
WHERE User.user_id = Items.user_id;Code language: SQL (Structured Query Language) (sql)Output:
Therefore, we can fetch data from the two tables using these two methods
Also Read: How To Convert SQL Subquery To Join?
Non-Equi Join in SQL
The Non-Equi Join is a SQL join technique that merges two tables based on conditions other than equality between the columns. Non-Equi join uses comparison operators such as less than(<), greater than(>), less than or equal to(<=), greater than or equal to(>=) and others. If the data matches, it fetches it.
Syntax:
SELECT *
FROM table01
JOIN table02
ON table01.column >= table02.column;Code language: SQL (Structured Query Language) (sql)The above syntax will return a joined table where the condition must be true such that table01’s column is greater than table02’s column.
Example of Non-Equi Join
We are going to use the same above-defined tables for the example.
SELECT *
FROM User
JOIN Items ON User.user_id < Items.user_id
WHERE User.age > 28;Code language: SQL (Structured Query Language) (sql)Output:
In the above example, we are combining tables based on their user_id. All the records are fetched whose user_id in the User table is less than the user_id in the Items table and the age of the user in the User table is greater than 28.
Key Differences Between Equi Join and Non-Equi Join
- Equi Joins are based on equality comparisons, whereas Non-Equi Join uses comparison operators apart from equality.
- Equi Join is the most frequent type of Join, which finds matching rows based on exact column matches.
- Non-Equi are less common, but they are beneficial in more complicated situations where exact matches are not enough.
Conclusion
In this tutorial, we learned about the two essential types of joins: Equi and Non-Equi Join. We have also discussed their syntax with multiple examples for a better understanding. Also, we have learned where and how to use which join type. Joins could ease data analysis and give helpful information about the business. We hope you find this article informative and enjoyable.