SQL Join With Types

The Join in SQL is used to combine rows from two or more tables, based on a common field between them. The JOIN is frequently used in many applications. 

Types of Join in SQL :- In real there are mainly three types of Join in SQL server:-

1. CROSS JOIN
2. INNER JOIN
3. OUTER JOIN
   • Left Join or Left Outer Join
   • Right Join or Right Outer Join
   • Full Join or Full Outer Join

S.No.	Joins	Description	Syntax
1.	INNER JOIN	Returns all rows when there is at least one match in BOTH tables. OR---> Returns only the matching rows. Non matching rows are eliminated.	SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name OR SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name=table2.column_name
2.	LEFT JOIN	Return all rows from the left table, and the matched rows from the right table. OR--->Returns all the matching rows + non matching rows from the left table.	SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name OR SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name
3.	RIGHT JOIN	Return all rows from the right table, and the matched rows from the left table. OR---> Returns all the matching rows+ non matching rows from the right table.	SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name OR SELECT column_name(s) FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name=table2.column_name
4.	FULL JOIN	Return all rows when there is a match in ONE of the tables. OR--->Returns all the rows from both tables, including non-matching rows.	SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name
5.	CROSS JOIN	Returns Cartesian product of the tables involved in the join.

Note :- There is one special type of join known as SELF JOIN 

SELF JOIN :- SELF JOIN is not a different type of JOIN. It can be classified under any type of JOIN - INNER, OUTER or CROSS Joins. 

First of all we create the two tables named is tblStudentRecord and tblAdmission and insert some reocords in both tables as shown in below

1. tblStudentRecord Table:- 

  
CREATE TABLE tblStudentRecord
(
Name varchar(100), Rollno int primary key, Branch varchar(30),Email varchar(100),Address varchar(200)
)


Insert some records :-

INSERT INTO tblStudentRecord VALUES('Santosh kumar singh',1001,'Information Technology','sa@gmail.com','A-3033')
INSERT INTO tblStudentRecord VALUES('Er. Reena Tanti',1002,'ECE','err@gmail.com','T-387, Gaya')
INSERT INTO tblStudentRecord VALUES('Chandan kumar',1003,'CSE','chn@gmail.com','Gaya')
INSERT INTO tblStudentRecord VALUES('Nand kishor kumar',1004,'BCA','nk@gmail.com','A-3033,Darbhanga')
INSERT INTO tblStudentRecord VALUES('Abhinav kumar',1005,'MCA','ab@gmail.com','H-837')
INSERT INTO tblStudentRecord VALUES('Santosh kumar singh',1006,'CSE','santosh@gmail.com','A-3031')
INSERT INTO tblStudentRecord VALUES('Neha sharma',1007,'MCA','h@gmail.com','D-34,Patna')
INSERT INTO tblStudentRecord VALUES('Pooja',1008,'ECE','pr@gmail.com','A-90, Jaipur, Rajashthan')
INSERT INTO tblStudentRecord VALUES('Jaya Laxmi',1009,'CSE','jl@gmail.com','Kerla')
INSERT INTO tblStudentRecord VALUES('Pakaj Kumar',1010,'BCA','pk@gmail.com','A-30,Darbhanga,Bihar')
INSERT INTO tblStudentRecord VALUES('Abhinav kumar',1011,'ME','ab1@gmail.com','K-837')
INSERT INTO tblStudentRecord VALUES('Suneeta',1012,'MCA','su@gmail.com','UP')
INSERT INTO tblStudentRecord VALUES('Suman Kumar',1013,'IT','sk@gmail.com','Block-T,Darbhanga')
INSERT INTO tblStudentRecord VALUES('Ajay kumar',1014,'ECE','aj@gmail.com','D-89')

2. tblAdmission Table:-

  
CREATE TABLE tblAdmission
(
Rollno int primary key, Name varchar(100),MobileNo bigint,Fee int,AdmissionDate varchar(30)
)

INSERT SOME RECORDS IN tblAdmission TABLE:-

Insert into tblAdmission values(1001,'Santosh kumar singh',99999,35000,'26-08-2009')
Insert into tblAdmission values(1002,'Er. Reena Tanti',99566,39000,'20-07-2008')
Insert into tblAdmission values(1003,'Chandan kumar',95689,38000,'22-08-2009')
INSERT INTO tblAdmission VALUES(1004,'Nand kishor kumar',78999,25000,'04-05-2012')
Insert into tblAdmission values(1011,'Abhinav kumar',78999,35000,'26-07-2009')
INSERT INTO tblAdmission VALUES(1012,'Suneeta',72999,26000,'14-12-2011')
INSERT INTO tblAdmission VALUES(1015,'Er. Gagan',85899,36000,'25-05-2008')
INSERT INTO tblAdmission VALUES(1016,'Rajeev kumar',78009,39000,'28-09-2014')

1. Inner Join or Join :-The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the "tblStudentRecord" table that do not have matches in "tblAdmission", these customers will NOT be listed. 

Example :-

  
SELECT s.Name,s.Branch,s.Email,a.Fee,a.AdmissionDate
FROM tblStudentRecord AS s
INNER JOIN tblAdmission AS a
ON s.Rollno=a.Rollno

----------->>>>OR<<<<-------------

SELECT tblStudentRecord.Name,tblStudentRecord.Branch,tblStudentRecord.Email,
tblAdmission.Fee,tblAdmission.AdmissionDate
FROM tblStudentRecord INNER JOIN tblAdmission 
ON tblStudentRecord.Rollno=tblAdmission.Rollno

Note :- We can use Join in place of Innser Join. 

2. LEFT JOIN or LEFT OUTER JOIN Keyword In SQL :-The LEFT JOIN keyword returns all the rows from the left table (tblStudentRecord), even if there are no matches in the right table (tblAdmission). 

Note(1):-The result is NULL in the right side when there is no match. 
Note(2):-We can use LEFT OUTER JOIN in place of LEFT JOIN. 

Example :- 

  

SELECT s.Name,s.Branch,s.Email,a.Fee,a.AdmissionDate
FROM tblStudentRecord AS s
LEFT JOIN tblAdmission AS a
ON s.Rollno=a.Rollno

--------->>>OR<<<---------

SELECT tblStudentRecord.Name,tblStudentRecord.Branch,tblStudentRecord.Email,
tblAdmission.Fee,tblAdmission.AdmissionDate
FROM tblStudentRecord LEFT JOIN tblAdmission 
ON tblStudentRecord.Rollno=tblAdmission.Rollno

3. RIGHT JOIN or RIGHT OUTER JOIN Keyword :- The RIGHT JOIN keyword returns all the rows from the right table (tblAdmission), even if there are no matches in the left table (tblStudentRecord). 

Note(1):-The result is NULL in the left side when there is no match.
Note(1):-We can use RIGHT OUTER JOIN in place of RIGHT JOIN. 

Example :- 

  

SELECT s.Name,s.Branch,s.Email,a.Fee,a.AdmissionDate
FROM tblStudentRecord AS s
RIGHT JOIN tblAdmission AS a
ON s.Rollno=a.Rollno

--------->>>OR<<<---------

SELECT tblStudentRecord.Name,tblStudentRecord.Branch,tblStudentRecord.Email,tblAdmission.Fee,
tblAdmission.AdmissionDate
FROM tblStudentRecord RIGHT JOIN tblAdmission 
ON tblStudentRecord.Rollno=tblAdmission.Rollno

4. FULL JOIN or FULL OUTER JOIN Keyword in SQL :-The FULL OUTER JOIN keyword returns all the rows from the left table (tblStudentRecord), and all the rows from the right table (tblAdmission). If there are rows in "tblStudentRecord" that do not have matches in "tblAdmission", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.

Note(1):-The FULL JOIN keyword combines the result of both LEFT and RIGHT joins. 
Note(2):-We can use FULL OUTER JOIN in place of FULL JOIN.

Example :-

  

SELECT s.Name,s.Branch,s.Email,a.Fee,a.AdmissionDate
FROM tblStudentRecord AS s
FULL JOIN tblAdmission AS a
ON s.Rollno=a.Rollno

--------->>>OR<<<---------

SELECT tblStudentRecord.Name,tblStudentRecord.Branch,tblStudentRecord.Email,
tblAdmission.Fee,tblAdmission.AdmissionDate
FROM tblStudentRecord FULL JOIN tblAdmission 
ON tblStudentRecord.Rollno=tblAdmission.Rollno

5. CROSS JOIN in SQL :- The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product. If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN. An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause. 

Syntax :-

  

SELECT * FROM table1 CROSS JOIN table2;

Example :- We create two tables named are tblEmployee and tblDepartment and insert some records in both tables as shown in below.

---------Employee Table---------


CREATE TABLE tblEmployee
(
EmpId int primary key,Name varchar(100),Email varchar(max)
)

---------Insert three records into tblEmployee-------
INSERT INTO tblEmployee VALUES(1001,'Santosh kuamr singh','s@gmail.com')
INSERT INTO tblEmployee VALUES(1002,'Er. Reena Tanti','er@gmail.com')
INSERT INTO tblEmployee VALUES(1003,'Pushpanjali','p@gmail.com')


---------Department Table---------
CREATE TABLE tblDepartment
(
Id int primary key,Department varchar(100),Salary int
)

---------Insert three records into tblDepartment-------
INSERT INTO tblDepartment VALUES(1001,'Information Technology',1000)
INSERT INTO tblDepartment VALUES(1002,'ECE',2000)
INSERT INTO tblDepartment VALUES(1003,'CSE',600)

Now, we use here CROSS JOIN as shown in below:-

  

SELECT Name,Email,Salary FROM tblEmployee CROSS JOIN tblDepartment

SELF JOIN :-The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement. 

Syntax :-

  

SELECT x.column_name, y.column_name...
FROM table1 x, table1 y
WHERE x.common_field = y.common_field;

Here, WHERE clause could be any given expression based on your requirement.

Example :- We use here tblAdmission for Self Join which is already created above.

  

SELECT x.Name,y.Rollno,x.AdmissionDate FROM tblAdmission x, tblAdmission y WHERE x.Fee=y.Fee