Joins in sql server 2005

Joins in sql server 2005

What is Join?

A join is used to combine columns from two or more tables into a single result set. To join data from two tables you write the names of two tables in the FROM clause along with JOIN keyword and an ON phrase that specifies the join condition. The join condition indicates how two tables should be compared. In most cases they are compares on the base on the relationship of primary key of the first table and foreign key of the second table.I will tell you about the following types of joins in that article.

Join Types

You can do two types of Join: ‘Inner’ Join or an ‘Outer’ Join.

Inner Join:

This will only return rows when there is at least one row in both tables that match the join condition.

Syntax:

SELECT * FROM table_name1

INNER JOIN table_name2

ON table_name1.column_name = table_name2.column_name

Example Inner Join Statement

SELECT * FROM Individual

INNER JOIN Publisher

ON Individual.IndividualId = Publisher.IndividualId

Note: We could use table aliases instead of the full table name. This will keep our statement shorter. For example:

SELECT * FROM Individual AS Ind

INNER JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Outer Join:

• LEFT OUTER JOIN (or LEFT JOIN): This will return rows that have data in the left table (left of the JOIN keyword), even if there's no matching rows in the right table.
• RIGHT OUTER JOIN (or RIGHT JOIN): This will return rows that have data in the right table (right of the JOIN keyword), even if there's no matching rows in the left table.
• FULL OUTER JOIN (or FULL JOIN): This will return all rows, as long as there's matching data in one of the tables.

Syntax:

Left Join:

SELECT * FROM table_name1

LEFT JOIN table_name2

ON table_name1.column_name = table_name2.column_name

Right Join:

SELECT * FROM table_name1

RIGHT JOIN table_name2

ON table_name1.column_name = table_name2.column_name

Full Join:

SELECT * FROM table_name1

FULL JOIN table_name2

ON table_name1.column_name = table_name2.column_name

Left Outer Join

Use this when you only want to return rows that have matching data in the left table, even if there's no matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS Ind

LEFT JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table

Id	FirstName	UserName
1	Bharat	bharat
2	Rahul	Rahul
3	Piyush	Piyush
4	Sandy	sandy

Right Table

IndividualId	AccessLevel
1	Programmer
2	Tester
3	Sales Executive
10	Manager

Result

IndividualId	FirstName	UserName		IndividualId	AccessLevel
1	Bharat	Bharat		1	Programmer
2	Rahul	Rahul		2	tester
3	Piyush	Piyush		3	SalesExecutive
4	Sandy	sandy		Null	null

Right Outer Join

Use this when you only want to return rows that have matching data in the right table, even if there's no matching rows in the left table.

Example SQL statement

SELECT * FROM Individual AS Ind

RIGHT JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table

Id	FirstName	UserName
1	Bharat	bharat
2	Rahul	Rahul
3	Piyush	Piyush
4	Sandy	sandy

Right Table

IndividualId	AccessLevel
1	Programmer
2	Tester
3	Sales Executive
10	Manager

Result

IndividualId FirstName UserName IndividualId AccessLevel 1 Bharat Bharat 1 Programmer 2 Rahul Rahul 2 tester 3 Piyush Piyush 3 SalesExecutive Null Null null 10 Manager

Full Outer Join

Use this when you want to all rows, even if there's no matching rows in the right table.

Example SQL statement

SELECT * FROM Individual AS Ind

FULL JOIN Publisher AS Pub

ON Ind.IndividualId = Pub.IndividualId

Source Tables

Left Table

Id	FirstName	UserName
1	Bharat	bharat
2	Rahul	Rahul
3	Piyush	Piyush
4	Sandy	sandy

Right Table

IndividualId	AccessLevel
1	Programmer
2	Tester
3	Sales Executive
10	Manager

Result

IndividualId FirstName UserName IndividualId AccessLevel 1 Bharat Bharat 1 Programmer 2 Rahul Rahul 2 tester 3 Piyush Piyush 3 SalesExecutive 4 Sandy Sandy Null Null Null Null null 10 Manager