Wednesday, May 22, 2013

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.

No comments:

Post a Comment