Joins In SQL Server
Joins In SQL Server
As per name suggest, Joins used to retrieve data by connecting more than two tables. Joins is a very important queries in SQL Server. In big Organization data distributed in multiple logical tables so we have to retrieve data from multiple tables, then we use joins.
There are different types of Joins in SQL Server:
- Inner join
- Left join
- Right join
- Full outer join
- Cross join
INNER JOIN: Inner join is simple joins which retrieve matching data set from both tables.
Let’s create two tables first and insert some records in the same.
Create table Hackerfriend
(
userid nvarchar(100) not null primary key,
username varchar(50) not null,
email_ID nvarchar(100) not null,
article_name nvarchar(300) not null
)
Create table userdetails
(
userid nvarchar(100) not null primary key,
username varchar(50) not null,
nickname varchar(50) not null,
organisation nvarchar(100) not null
)
For userdetails table:
insert userdetails values('rajan1234','Rajan Kumar','raja','TGGC7NS')
insert userdetails values('rahul423','Rahul Kumar','ravikant','TGGC7NS')
insert userdetails values('abhya4554','Abhya Singh','chhutki','TGGC7NS')
insert userdetails values('vicky1564','Vicky Shastri','Shastri','TCCOS')
insert userdetails values('sameer1444','Sameer kaushik','Nabalik','TGGC7SS')
insert userdetails values('hemant0988','Hemant Kumar','Big Bro','TGG7NS')
For Hackerfriend table:
insert Hackerfriend values('rajan1234','Rajan Kumar','raja1234@gamilcom','Machine Learning')
insert Hackerfriend values('rahul423','Rahul Kumar','ravikant8765@gmail.com','SQL Server')
insert Hackerfriend values('abhya4554','Abhya Singh','chhutki1234@gmail.com','Android')
insert Hackerfriend values('vicky1564','Vicky Shastri','Shastri09876@gmail.com','Data science')
insert Hackerfriend values('sameer1444','Sameer kaushik','Nabalik7654@gmail.com','Java')
insert Hackerfriend values('hemant0988','Hemant Kumar','Bigbro6577@gmail.com','.net mvc')
Now we have records in both tables:
Apply INNER JOIN:
select nickname, hf.article_name
from userdetails
join Hackerfriend hf // join and inner join are same
on hf.userid = userdetails.userid
OUTPUT:
As you seen above, how we can join two tables with the help of INNER JOIN/ JOIN
Let’s move to LEFT JOIN:
Let’s insert another row in table userdetails to clear how LEFT JOIN works:
insert userdetails values('harish5467','Harish Kumar','chacha','TGGC7NS')
Execute below query for left join:
select nickname, hf.article_name from userdetails left join Hackerfriend hf on hf.userid = userdetails.userid
OUTPUT:
Look carefully the output, LEFT JOIN gives the details of Left Table(userdetails).
RIGHT JOIN: To understand this let's insert two rows in hackerfriend table.
insert Hackerfriend values('harish5467','Harish Kumar','chacha34@gmail.com','Full Stack Developer')
insert Hackerfriend values('rohit5667','Rohit Kumar','rohit34@gmail.com','C#')
Now table Hackerfriend has two more entries:
Apply RIGHT JOIN between two tables:
select nickname, hf.article_name from userdetails right join Hackerfriend hf on hf.userid = userdetails.userid
OUTPUT:
Look carefully the output, RIGHT JOIN gives output the whole entries of right table (Hackerfriend).
FULL JOIN:
Apply full join:
select nickname, hf.article_name from userdetails full join Hackerfriend hf on hf.userid = userdetails.userid
OUTPUT:
It shows whole details of both tables.
NOTE: Same result for full outer join
CROSS JOIN: It is basically the cross product of rows of two tables.
Apply CROSS JOIN:
select nickname, hf.article_name from userdetails CROSS JOIN Hackerfriend hf
OUTPUT:
We get 56 rows: Basically it is cross product between two tables
Hcakerfriend table has 8 records and userdetails has 7 records, so 8 X 7 = 56
I hope this article is useful for you.Please comment if you wil find any mistake from my side. Keep learning and Happy Coding!!
Thanks
Rahul Kumar
https://www.facebook.com/people/Rahul-Kumar/100003512118388
Friend of Hackerfriend.
