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 IndLEFT JOIN Publisher AS PubON 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 IndRIGHT JOIN Publisher AS PubON 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
| |||||||||||||||||||||||||||||||||||||||||
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 IndFULL JOIN Publisher AS PubON 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
|
