Open In App

SQL | EQUI Join and NON EQUI JOIN

Last Updated : 08 Sep, 2020
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

Similar Reads

SQL | Join (Cartesian Join & Self Join)
SQL| JOIN(Inner, Left, Right and Full Joins) In this article, we will discuss about the remaining two JOINS: CARTESIAN JOIN SELF JOIN Consider the two tables below: StudentCourse CARTESIAN JOIN: The CARTESIAN JOIN is also known as CROSS JOIN. In a CARTESIAN JOIN there is a join for each row of one table to every row of another table. This usually h
2 min read
SQL Full Outer Join Using Left and Right Outer Join and Union Clause
An SQL join statement is used to combine rows or information from two or more than two tables on the basis of a common attribute or field. There are basically four types of JOINS in SQL. In this article, we will discuss FULL OUTER JOIN using LEFT OUTER Join, RIGHT OUTER JOIN, and UNION clause. Consider the two tables below: Sample Input Table 1: Pu
3 min read
Difference between Inner Join and Outer Join in SQL
1. Inner Join : It is a type of join operation in SQL. Inner join is an operation that returns combined tuples between two or more tables where at least one attribute is in common. If there is no attribute in common between tables then it will return nothing. Syntax: select * from table1 INNER JOIN table2 on table1.column_name = table2.column_name;
2 min read
Difference between Natural join and Inner Join in SQL
The join operation merges the two tables based on the same attribute name and their datatypes are known as Natural join Unlike INNER JOIN, which requires you to specify the columns and conditions for the join explicitly. In this article, we will also see the differences between them. Let's start with Natural Join. Example: If you have two tables "S
3 min read
Full join and Inner join in MS SQL Server
Prerequisite - Introduction of MS SQL Server 1. Full Join : Full join selects all the rows from left and the right tables along with the matching rows as well. If there are no matching rows, it will be displayed as NULL. Syntax - select select_list from table1 full join table2 on join _predicate (OR) select * from table1 full join table2 2. Inner J
2 min read
Left join and Right join in MS SQL Server
Prerequisite – Introduction of MS SQL Server 1. Left Join : A join combines the set of two tables only. A left join is used when a user wants to extract the left table's data only. Left join not only combines the left table's rows but also the rows that match alongside the right table. Syntax - select select_list from table1 left join table2 on joi
2 min read
Self Join and Cross Join in MS SQL Server
Prerequisite - Introduction of MS SQL Server 1. Self Join : Self-join allows us to join a table itself. It is useful when a user wants to compare the data (rows) within the same table. Syntax - select select_list from T t1 [Inner|Left] Join on T t2 on join_predicate. Here T refers to the table we use for comparison and it is referred twice. To avoi
2 min read
Implicit Join vs Explicit Join in SQL
JOIN clause is used to combine rows from two or more tables, based on a relation between them. There are two different syntax forms to perform JOIN operation: Explicit joinImplicit join Step 1: Creating the Database Use the below SQL statement to create a database called geeks: CREATE DATABASE geeks; Step 2: Using the Database Use the below SQL sta
3 min read
SQL Left Outer Join vs Left Join
In SQL, both "LEFT JOIN" and "LEFT OUTER JOIN" are used to combine data from two or more tables based on a related column, but they are essentially the same operation, and there is no practical difference between them. The keyword "OUTER" is optional in most database systems, including popular ones like MySQL, PostgreSQL, and SQL Server. [caption w
4 min read
Difference between “INNER JOIN” and “OUTER JOIN”
JOINS :Joins in SQL are used to combine rows from multiple tables on a specific condition, which is a relation between the columns of two tables. And there are different types of joins and in this article let us cover INNER JOIN and OUTER JOIN and their differences. Let us consider the two tables student and location and see how the differences wou
4 min read
Article Tags :