Types of joins in SQL Equi and Non_Equi join

 

Types of Joins in SQL:

As the starter of SQL when you will apply any query it has some limitation that the resultant table is generated from one table only but there is some situations arise in which we may have to retrieve data from multiple tables and in that case we will use JOIN operation. If we want to generate the joined table (which will be the resultant table) from row pairing then two tables must have columns with the same values. Joining is the process of combining rows from multiple tables. Through joining, we can generate a short-lived table with all accessed columns from multiple tables that are specified in join. So here we will discuss different types of joins in SQL.

How can we perform SQL joins:

For performing JOIN we can use more than one table name after FROM keyword. These are obviously the name of those tables from which we want to extract data. Both table names should be written by using a separator or comma between them. WHERE Clause is used to specify the join condition. Both tables must have at least one matching column to be joined. If we join two tables then how many rows exactly we can retrieve can be calculated through CARTESIAN Product by multiplying the number of rows of both tables. Let suppose b and c are two tables. B has 4 rows and c table have 5 then total rows that can be accessed through join will be 4x5= 20.

SQL Aliases:

Alias is a Temporary name of the table that is used to shorten the name of the table while writing a query. Alias makes a query more readable. It is used instead of the actual name of the table. An alias can be set up through the “AS” keyword.

Syntax of Alias:

SELECT column_name
FROM table_name AS alias_name;

 

For example when we write the query for joining the data of two tables then we have to specify the table name with column let suppose the data is extracted from an Employee and Department table then the query will start like this :

Select Employee.Emp_no, Employee.Emp_name, Employee.Dept_no, Department.Dept_no, Department.Dept_name

From Employee, Department

WHERE Employee.Dept_no =  Department.Dept_no;

Instead of it by using a short name or Alias we can make this query more decipherable. Like this:

Select E.Emp_no, E.Emp_name, E.Dept_no, D.Dept_no, D.Dept_name

From Employee AS E, Department AS D

WHERE E.Dept_no =  D.Dept_no;

There are three types of SQL Joins these are:

Here we will discuss Simple Join and its types.

Simple Join:

Simple join works the same as an inner join. you can say both terms relate to the same processing. It is the most common type of join. It is used to retrieve the rows from two tables. Both tables should have a common column on the basis of which the two tables can be joined. It is further classified into two joins.

Equi_Join in SQL:

This type of join works on bases of equality means that you have to choose one common column between two tables and join those tables on the bases of matching rows of that column. It retrieves the identical rows of the specified matching column.

Syntax of Equi_Join:

SELECT table1.columnN, table2. ColumnN

FROM table_name 1 , table_name 2

WHERE table1. Column = table2. Column;

 

Syntax of Inner Join:

 

SELECT table1.columnN, table2. ColumnN

FROM table 1
INNER JOIN table2
ON table1. column_name= table2. column_name;

Let suppose there are two tables.

Teacher_table:

 

Student table:

Now if we want to apply inner join or Equi join on it we have one common column Std_id so on the basis of Std_id we can access the matching rows from both tables in the resultant table. so the query according to the syntax of Equi join will be:

Query according to the syntax of Inner Join will be:

Result:

In the above example s.Std_Id = t.Std_Id Perform the join operation and extract the matching rows from both tables.  Table Alias is prefixed by column name because both tables have the same column Std_Id.

Non Equi Join:

Non-Equi Join is used to retrieve matching and non-matching rows from both joined tables. we can use non-Equi join to compare one value of the first table with the range of values of another table hence it supports one-to-many relation. In this type of join, we’ll not use the ‘=’ sign however use other conditional operators to join the tables. These operators can be :

Operator	Meaning
>	Greater than
<	Less than
>=	Greater than or Equal to
<=	Less than Equal to
!=	Not equal to. Some version support <> symbol.
Between	Compare range of values

Why we use the non-Equi join:

Non-equi join is used to list all non-matching pair of items using not equal operator ‘<>’ as well as can determine duplicates and can also use to identify the list of item with certain range using Between operator.

Syntax of non-Equi join:

SELECT table1.columnN,Table2.columnN

FROM table_name 1 , table_name 2

WHERE table1. Column [> | < | >= | <= |<>| Between] table2. Column;

 

So if we want to extract the rows that have the less Std_Id value in the Student table as compare to the Std_Id column of the Teacher table.  So the query will be

So the question is How Non-Equi join works? and how the result will be generated let me clear this so we just have to compare the Std_Id column of both tables which are Student and Teacher tables. As we know Std_Id is the common column of both tables so let’s compare it.

So here according to the query teacher table, the Std_Id  column’s values should be greater than the Std_Id column value of student table. in the case of 103 and 107 Std_Id value of Teacher table is greater so these rows will be extracted as you can see in the result Std_Id of Teacher table is shown and other corresponding values. So in this way we can apply other operators as well in Non-Equi join like >, >=, <=, <>, Between except = Operator

Result: