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

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

                               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

                              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

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 

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.