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

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.