GeeksforGeeks App
Open App
Browser
Continue

# SQL | EQUI Join and NON EQUI JOIN

Types of SQL Joins are explained in left, right, and full join and SQL | Join (Cartesian Join & Self Join). And Remaining EQUI Join and NON-EQUI will discuss in this article. Let’s discuss one by one.

Example –

Let’s Consider the two tables given below.

Table name — Student

In this table, you have I’d, name, class and city are the fields.

`Select * from Student;`
idnameclasscity
3Hina3Delhi
4Megha2Delhi
6Gouri2Delhi

Table name — Record

In this table, you have I’d, class and city are the fields.

`Select * from Record;`
idclasscity
93Delhi
102Delhi
122Delhi

1. EQUI JOIN :

EQUI JOIN creates a JOIN for equality or matching column(s) values of the relative tables. EQUI JOIN also create JOIN by using JOIN with ON and then providing the names of the columns with their relative tables to check equality using equal sign (=).

Syntax :

```SELECT column_list
FROM table1, table2....
WHERE table1.column_name =
table2.column_name;
```

Example –

```SELECT student.name, student.id, record.class, record.city
FROM student, record
WHERE student.city = record.city;```

Or

Syntax :

```SELECT column_list
FROM table1
JOIN table2
[ON (join_condition)]
```

Example –

```SELECT student.name, student.id, record.class, record.city
FROM student
JOIN record
ON student.city = record.city;```

Output :

nameidclasscity
Hina33Delhi
Megha43Delhi
Gouri63Delhi
Hina32Delhi
Megha42Delhi
Gouri62Delhi
Hina32Delhi
Megha42Delhi
Gouri62Delhi

2. NON EQUI JOIN :

NON EQUI JOIN performs a JOIN using comparison operator other than equal(=) sign like >, <, >=, <= with conditions.

Syntax:

```SELECT *
FROM table_name1, table_name2
WHERE table_name1.column [> |  < |  >= | <= ] table_name2.column;

```

Example –

```SELECT student.name, record.id, record.city
FROM student, record
WHERE Student.id < Record.id ;```

Output :

nameidcity
Hina9Delhi
Megha9Delhi
Gouri9Delhi
Hina10Delhi
Megha10Delhi
Gouri10Delhi
Hina12Delhi
Megha12Delhi
Gouri12Delhi
My Personal Notes arrow_drop_up