Open In App

SQL | EQUI Join and NON EQUI JOIN

Improve
Improve
Like Article
Like
Save
Share
Report

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

Last Updated : 08 Sep, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads