What are Equi-Join and Non-Equi Join Examples?

I have already explained about SQL joins in other articles. Here in this article my focus is on SQL join examples for equi join and non equi join. The most used concept in real life scenarios are nothing but SQL Joins. Although in reporting, stand alone applications development, Web application development the concept of join is really important. Joins are nothing but Combining the records from two or more tables. You will get Basic information about all the joins in this article. I will explain the Real life scenarios for all the joins in the article.

CLICK HERE TO GET MORE INFORMATION ABOUT INNER JOIN AND OUTER JOIN

Types of SQL Joins with SQL joins scenarios :

1.Joins using Operators -> Equi Join, Non Equi Join

2.Joins using Concept-> Inner Join, Outer Join, Cross Join, Self Join

Joins Using Operator:

When two or more tables are joined using Operators is nothing but the Joins Using operator.

1.Equi Join: When two or more tables has been joined using equal to operator then this category is called as equi join. Equi join should be inner joins or outer join. Just we

need to concentrate on condition is equal to(=) between the columns in the table. Make sure that we are using where clause to apply the condition between two tables. If the condition of join misses or there is not relation between the tables in the join then Equi join fails and the result will be the Cartesian product or cross join.

CLICK HERE TO GET MORE INFORMATION ON CARTESIAN JOIN

Syntax :

Select alias1.column_name1,alias1.column_name2,alias2.column_name1..

from table1 alias1,table2 alias2

where table1.column=table2.column;     ---Equi join condition

SQL Join Examples for Equi join :  

Question: Query to fetch the Employees with its deparment_name.

Consider there are 2 tables.Employee table which has following columns:

Employee_num	Employee_name	Department ID	Salary
1	Amit	100	680000
2	Rohan	100	550000
3	Rohit	101	430000

There is another table called as Depatment which has following structure:

Department ID	Department Name
100	OBIEE
101	Oracle PLSQL
102	COGNOS

SQL Query:

Select  a.Employee_name,b.Department_name From Employee a,Department b

where a.Department_ID=b.Department_ID;

Output:

Employee Name	Department Name
Amit	OBIEE
Rohan	OBIEE
Rohit	Oracle PLSQL

2.Non Equi Join:

When two or more tables are joining without Equal to condition then that join is known as Non Equi join. The use of non Equi join is very rare in real life scenarios. You can join tables using any other attributes except equal to operator. You can use any operator for non Equi join that is <>,!=,<,>,Between.

SQL Join examples for Non Equi join :  

Consider 2 table which as given above.one is Employee table and other is department table.

Question: Tell me the department which is not allocated to any of the Employee.

Consider there are 2 tables. Employee table which has following columns:

Employee_num	Employee_name	Department ID	Salary
1	Amit	100	680000
2	Rohan	100	550000
3	Rohit	101	430000

There is another table called as Depatment which has following structure:

Department ID	Department Name
100	OBIEE
101	Oracle PLSQL
102	COGNOS

SQL Query:

Select b.Department_ID,b.Department_name from

Employee a,Department b where a.Department_id <> b.Department_ID;

The Above Query will return following output:

102

COGNOS

These kind of real life scenarios we need to face in day to day life. I will explain other joins in next article. Hope you like this article. Kindly comment if you like the article or if any suggestions.

CLICK HERE TO GET 20 MOST IMPORTANT COMPLEX SQL QUERIES FOR INTERVIEW

HOME