What is JOIN? Explain types of JOIN with example.

JOIN: Introduction

JOIN in Database Programming is a binary operation, which allows us to combine join product and selection in one single statement. The goal of creating a JOIN condition is that it helps us to explain how different tables are related. The tables in DBMS are associated using the primary key and foreign keys, thus join also keeps our database normalized. Normalization reduces the data redundancy so that we can decrease the number of data anomalies in our application when we delete or update a record.

Here is an example of SQL JOINs which helps to join two tables based on the common columns and selects data that have matching values in these columns.

Example

Student

Student ID	Student Name
01	Kuber Acharya
02	Rabin Gajurel
03	Bashudev Bastola
04	Dibash Pradhan
05	Yamini Adhikari
06	Puja Sangroula

Registration

Student ID	Fee Amount
01	RS. 2300
02	RS. 4300
03	RS. 5000
04	RS. 1200
05	RS. 800
06	RS. 780

Let's start JOIN Operation (Student ⋈ Registration)

Output

Student ID	Student Name	Fee Amount
01	Kuber Acharya	RS. 2300
02	Rabin Gajurel	RS. 4300
03	Bashudev Bastola	RS. 5000
04	Dibash Pradhan	RS. 1200
05	Yamini Adhikari	RS. 800
06	Puja Sangroula	RS. 780

We can understand What is JOINs from above example, and now let's see about different types of JOINs that we can use for different results.

JOIN: Types

• Inner JOINs
• Outer JOINs
• Cross JOINs

1. Inner JOINS

In SQL, INNER JOIN selects and joins the tables that have matching values in both as long as the condition is satisfied. It is the most widely used join operation and can be considered as a default join-type.
The Syntax for Inner Join is :

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

The Syntax for Inner Join with Where Clause is :

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
WHERE condition;

Inner JOINs further divided into the three subtypes:-

A : Theta JOIN
The general case of JOIN operation is called a Theta join. Theta JOIN allows us to combine two tables based on the condition represented by theta. It is denoted by symbol θ.
The Syntax for Theta Join is :

A ⋈θ B

A and B are two different tables which having relations attributes, that is A ∩ B = Φ. Theta join can be used by all kinds of comparison operators like (>, <, =<, =>, =, and ==)etc. Example:

Student
SID	Name	Std
01	Bashudev	12
02	Rabin	11

Subjects
Class	Subject
12	C Programming
13	DBMS

Theta Operation: Student_detail

STUDENT ⋈Student.Std > Subject.Class SUBJECT

Student_detail
SID	Name	Std
02	Rabin	11

B : EQUI JOIN

EQUI Join is done when a Theta join uses only the equivalence condition. EQUI join is the most difficult operation to implement efficiently in an RDBMS, and one reason why RDBMS have essential performance problems.

Example:

Student
SID	Name	Std
01	Bashudev	12
02	Rabin	11

Subjects
Class	Subject
12	C Programming
12	DBMS
11	Java
11	E-Commerce

EQUI Operation: Student_detail

STUDENT ⋈Student.Std = Subject.Class SUBJECT

Student_detail
SID	Name	Std	Class	Subject
01	Bashudev	12	12	C Programming
01	Bashudev	12	12	DBMS
02	Rabin	11	11	Java
02	Rabin	11	11	E-Commerce

C : Natural JOIN(⋈)

Natural join does not use any of the comparison operator. It does not concatenate the way a Cartesian product does. We can perform a Natural Join only if there is at least one common attribute that exists between two relations. In this type of join, the attributes must have the same name and domain. It performs selection forming equality on those attributes which appear in both relations and eliminates the duplicate attributes.

Example:

Student
SID	Name	Course
01	Bashudev	BCA
02	Rabin	BBA

Subjects
Course	Subject
BCA	C Programming
BBA	DBMS

Natural Operation: Student_detail

STUDENT ⋈Student.Std ⋈ Subject.Class SUBJECT

Student_detail
SID	Name	Course	Subject
01	Bashudev	BCA	C Programming
02	Rabin	BBA	DBMS

2. Outer JOINS

An Outer Join doesn’t require each record in the two join tables to have a matching record. It can be used to return the records in both the tables whether it has matching records in both the tables or not. In simple words, Outer Join is based on both matched and unmatched data. It is subdivide further into,

A : Left Outer JOIN

The left join operation returns all record from left table and matching records from the right table. If there is no matching result in the right table, it will return null values in that field. The left outer join returns a resultset table with the matched data from the two tables and then the remaining rows of the left table and null from the right table's columns.

The Syntax for Left Outer JOINs is :

SELECT column_name(s)
FROM table1
LEFT JOIN Table2 
ON Table1.Column_Name=table2.column_name;

Example:

Table A: Student
Course	Name
BCA	Bashudev
BBA	Rabin

Table B: Subjects
Course	Subject
BCA	C Programming
BBS	Account

A ⋈ B

Table A ⋈ B: Student Details
Course	Name	Subject
BCA	Bashudev	C Programming
BBA	Rabin	Null

B : Right Outer JOIN

The Right join operation returns all record from right table and matching records from the left table. If there is no matching result in the left table, it will return null values in that field. The right outer join returns a resultset table with the matched data from the two tables and then the remaining rows of the right table and null from the left table's columns.Right Outer Join is the opposite of Left Outer Join.

The Syntax for Right Outer JOINs is :

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Example: A ⋈ B

Table A ⋈ B: Student Details
Course	Name	Subject
BCA	Bashudev	C Programming
BBS	Null	Account

C : Full Outer JOIN

The Full-Outer join contains all the values of both the tables whether they have matching values in them or not. The Keywords FULL OUTER JOIN and FULL JOIN are the same.

The Syntax for Full Outer JOINs is :

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Example: A ⋈ B

Table A ⋈ B: Student Details
Course	Name	Subject
BCA	Bashudev	C Programming
BBA	Rabin	Null
BBS	Null	Account

3. Cross JOINS

The CROSS JOINs is used to generate a paired combination of each row of the first table with each row of the second table. It is also known as the Cartesian join since it returns the Cartesian product of the sets of rows from the joined tables.

The Syntax for Cross JOINs is :

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Example:

Table A: Student_Name
S.N.	Name
1	Bashudev
2	Rabin

Table B: Student_Subjects
S.N.	Subjects
1	C Programming
2	Database

Cross JOIN Final Result

Table Cross Join: Student_Info
S.N.	Name	Subjects
1	Bashudev	C Programming
2	Rabin	C Programming
3	Bashudev	Database
4	Rabin	Database