INNER JOIN WITH EXAMPLES

create table Emp(

id int identity(1,1) primary key,
Username varchar(50),
FirstName varchar(50),
LastName varchar(50),
DepartID int

)

insert into emp values (‘nirampatel’,’niram’,’patel’,1)
insert into emp values (‘rajivpat’,’rajiv’,’pat’,1)
insert into emp values (‘madhurpani’,’madhur’,’pani’,2)
insert into emp values (‘gouravkhan’,’gourav’,’khan’,2)
insert into emp values (‘akshaykumar’,’akshay’,’kumar’,3)
insert into emp values (‘sardarpandit’,’sardar’,’pandit’,null)

create table Depart(

id int identity(1,1) primary key,
DepartmentName varchar(50)

)

insert into depart values (‘it’),(‘cse’),(‘eee’),(‘ece’),(‘mech’),(‘civil’)

inner join:

select e1.Username,e1.FirstName,e1.LastName,e2.DepartmentName
from Emp e1 inner join Depart e2 on e1.DepartID=e2.id

join:
SELECT * FROM Emp e1 JOIN Depart e2 ON e1.DepartID = e2.id

left outer join:
SELECT * FROM Emp e1 LEFT OUTER JOIN Depart e2
ON e1.DepartID = e2.id

right outer join:
SELECT * FROM Emp e1 RIGHT OUTER JOIN Depart e2
ON e1.DepartID = e2.id

full outer join:
SELECT * FROM Emp e1 FULL OUTER JOIN Depart e2
ON e1.DepartID = e2.id

cross join:
SELECT * FROM Emp cross join Depart e2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s