Equi Join in SQL

In this article, we will take a look at the Inner Join in SQL according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.

Table of Contents

What is Equi Join in SQL?
- - Syntax
- Example
Practice Questions on Equi Join in SQL
FAQs

What is Equi Join in SQL?

Of all the joins that are used in SQL, the EQUI JOIN is the most common one. It is also usually known as the INNER JOIN.

The EQUI JOIN creates a new result table when it combines the values of columns of two tables (like the table_a and the table_b) on the basis of the join-predicate. In case the join predicate gets satisfied, the values of the columns for every matched pair of the A and B rows get combined into a resultant row.

Syntax

Here is the basic syntax of the EQUIJOIN:

SELECT table_a.column_a, table_b.column_b…

FROM table_a

INNER JOIN table_b

ON table_a.common_field = table_b.common_field;

Now let us take a look at an example.

Example

We will now consider the two tables given as follows:

Table 1 − The CLIENTS Table is given as follows:

ID	NAMETAG	ADDRESS	AGE	WAGE
1	Rimona	Ahmedabad	32	2000.00
2	Kujo	Delhi	25	1500.00
3	Kiara	Kota	23	2000.00
4	Chilly	Mumbai	25	6500.00
5	Helen	Bhopal	27	8500.00
6	Kitty	MP	22	4500.00
7	Mindy	Indore	24	10000.00

Table 2 − The ORDERS Table is given as follows:

OID	DATE	CLIENT_ID	TOTAL
102	2019-10-18 00:00:00	3	3000
100	2019-10-18 00:00:00	3	1500
101	2019-11-10 00:00:00	2	1560
103	2018-05-10 00:00:00	4	2060

Now, we will join the given two tables as follows using the LEFT JOIN:

SQL> SELECT ID, NAMETAG, AMOUNT, DATE

FROM CLIENTS

INNER JOIN ORDERS

ON CLIENTS.ID = ORDERS.CLIENT_ID;

As a result, it would produce the result as follows:

ID	NAMETAG	TOTAL	DATE
3	Kiara	3000	2019-10-18 00:00:00
3	Kiara	1500	2019-10-18 00:00:00
2	Kujo	1560	2019-11-10 00:00:00
4	Chilly	2060	2018-05-10 00:00:00

Practice Questions on Equi Join in SQL

1. Which of these conditions must be satisfied if we want the INNER JOIN or EQUI JOIN to work?

A. The columns that we use for joining must consist of the same name

B. The columns that we use for joining must consist of different names

C. The columns that we use for joining can consist of the same or different names

D. There is no specification

Answer: C. Columns that we use for joining can consist of the same or different names

2. Which of these is also known as an INNER JOIN?

A. NON-EQUI JOIN

B. SELF JOIN

C. EQUI JOIN

D. None of these

Answer: C. EQUI JOIN

FAQs

What is EQUIJOIN?

The EQUI JOIN in SQL performs a JOIN against a column of equality or the matching column(s) values that have the associated tables. Here, we use an equal sign (=) as a comparison operator in our ‘where’ clause to refer to equality.
We can also perform EQUI JOIN by when we use the JOIN keyword followed by the ON keyword and then by specifying the names of the columns and their associated tables in order to check equality.
The Syntax would be as follows:
SELECT column_list
FROM table_x, table_y….
WHERE table_x.column_name =
table_y.column_name;
or
SELECT *
FROM table_x
JOIN table_y
[ON (join_condition)]

Is Equi join the same as outer join?

The Equi Join is the same as the outer join, the exception being that one of the duplicate columns here is actually eliminated in the resultant table.

What is the Equi join predicate?

The Equi Join predicate refers to a predicate where one of the columns is compared to some other column in another table with the help of the = operator. A predicate is always optimizable in case it provides some starting or some stopping point, and it allows the use of an index.

What is Equi Join and Non-Equi Join?

The Equi Join refers to a join that uses only the “equals” comparison in the given join condition. On the other hand, a Non-Equi Join refers to a Join that consists of at least a single comparison in the available join condition (that’s not an “equals” comparison).

Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus, GATE Previous Year Question Paper, and more.

Also Explore,