What is Join in Sql Server? How many types of Joins? - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

What is Join in Sql Server? How many types of Joins?


Join is a query type. As its name suggest Join is used to display/fetch the records from two or more tables.
I have created two tables.
BOOK (here ID is primary key).
ID
Int
BOOK_NAME
varchar(50)
BOOK_PRICE
varchar(50)
BOOK_PUBLICATION
varchar(50)


AUTHOR (Here ID is primary key).
ID
Int
AUTHOR_NAME
varchar(50)


Joins are of three types:

1.       Inner join: Display only matched records from both tables.
Example:
SELECT * from dbo.BOOK bk inner join dbo.AUTHOR au on bk.ID = au.ID
Or
select bk.BOOK_NAME,bk.BOOK_PRICE,bk.BOOK_PUBLICATION,au.AUTHOR_NAME from dbo.BOOK bk inner join dbo.AUTHOR au on bk.ID = au.ID
Join in Sql Server


2.       Outer join: Outer are of three types:
                                 i.            Left outer Join: It displays all the result from left (first) table and display null for right (second) table if value is not matched.
Example:
select * from dbo.BOOK bk left outer join dbo.AUTHOR au on bk.ID = au.ID
Or
select bk.BOOK_NAME,bk.BOOK_PRICE,bk.BOOK_PUBLICATION,au.AUTHOR_NAME from dbo.BOOK bk left outer join dbo.AUTHOR au on bk.ID = au.ID

Join in Sql Server

                               ii.            Right outer Join: It displays all the result from right (second) table and display null for left (first) table if value is not matched.
Example:
select * from dbo.BOOK bk right outer join dbo.AUTHOR au on bk.ID = au.ID
Or
select bk.BOOK_NAME,bk.BOOK_PRICE,bk.BOOK_PUBLICATION,au.AUTHOR_NAME from dbo.BOOK bk right outer join dbo.AUTHOR au on bk.ID = au.ID
Join in Sql Server

                              iii.            Full outer Join: It displays all result from both table but where id is not matched display null.
Example:
select * from dbo.BOOK bk full outer join dbo.AUTHOR au on bk.ID = au.ID
Or
select bk.BOOK_NAME,bk.BOOK_PRICE,bk.BOOK_PUBLICATION,au.AUTHOR_NAME from dbo.BOOK bk full outer join dbo.AUTHOR au on bk.ID = au.ID

Join in Sql Server

3.       Cross join: Cross join multiply the records present in both tables. E.g. one table has 6 records and second table has 5 records then cross join display the 30 records.
Example:
select * from dbo.BOOK cross join dbo.AUTHOR
Or
select bk.BOOK_NAME,bk.BOOK_PRICE, bk.BOOK_PUBLICATION, au.AUTHOR_NAME from dbo.BOOK  bk cross join dbo.AUTHOR au 
Join in Sql Server

Is it helpful?

If yes post your comment to admire my work. You can like me on Facebook, Google+, Linkedin and Twitter via hit on Follow us Button and also can get update follow by Email.

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon