Different Types of SQL Joins

S

ql joins are used to fetch/retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in Sql join. Basically data tables are related to each other with keys.

I have 2 Tables  in sql server,

Table 1 with Primary Key

Table2 with Foreign Key

Types of Joins

In Sql Server we have only three types of joins. Using these joins we fetch the data from multiple tables based on condition.

Inner Join

Inner join returns only those records/rows that match/exists in both the tables. Syntax for Inner Join is as

Outer Join

  

    refer the  following url : Click here

Cross Join

Cross join is a cartesian join means cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table. Syntax for right outer Join is as :

Self Join 

Self join is used to join a database table to itself, particularly when the table has a Foreign key that references its own Primary Key. Basically we have only three types of joins : Inner join, Outer join and Cross join. We use any of these three JOINS to join a table to itself. Hence Self join is not a type of Sql join.

To understand Self Join, suppose we following two tables and data in these two tables is shown in figure.

CREATE TABLE emp

(

id int NOT NULL primary key,

name varchar(100) NULL,

designation varchar(50) NULL,

supid int foreign key references emp(id) ) -- In this table we have a Foreign key supid that references its own Primary Key id. We use it for Self Join

INSERT INTO emp(id,name,designation) VALUES(1,'mohan','Manger')

INSERT INTO emp(id,name,designation,supid) VALUES(2,'raj kumar','SE',1)

 INSERT INTO emp(id,name,designation) VALUES(3,'bipul kumar','Manager')

 INSERT INTO emp(id,name,designation,supid) VALUES(4,'mrinal kumar','SE',2)

 INSERT INTO emp(id,name,designation,supid) VALUES(5,'jitendra kumar','SE',2)

 select * from emp

Code for SelfJoin is : 

Regards: Suraj K Mad.