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.

SQL JOINS :

• EQUI Join
• NON-EQUI Join

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;

id	name	class	city
3	Hina	3	Delhi
4	Megha	2	Delhi
6	Gouri	2	Delhi

Table name — Record

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

Select * from Record;

id	class	city
9	3	Delhi
10	2	Delhi
12	2	Delhi

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 :

name	id	class	city
Hina	3	3	Delhi
Megha	4	3	Delhi
Gouri	6	3	Delhi
Hina	3	2	Delhi
Megha	4	2	Delhi
Gouri	6	2	Delhi
Hina	3	2	Delhi
Megha	4	2	Delhi
Gouri	6	2	Delhi

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 :

name	id	city
Hina	9	Delhi
Megha	9	Delhi
Gouri	9	Delhi
Hina	10	Delhi
Megha	10	Delhi
Gouri	10	Delhi
Hina	12	Delhi
Megha	12	Delhi
Gouri	12	Delhi

My Personal Notes arrow_drop_up

Save