JOINS
The process of combining data from more than one table using a single select statement is called joining. Within a relational database, the data will not be stored in a single table. Hence a join is needed when we need to combine data from more than one table.
I have two tables to implement joins, Mas_Employee and Mas_Department.
Joining in SQL Server is classified as follows:
- Inner Join
- Outer Join: Left Outer Join, Right Outer Join, Full Outer Join
- Cross Join
- Self Join
The basic syntax to implement joins is as in the following:
- Select {Column_List}
- From {Main_Table}
- JoinType {Refernce_Table}
- On {Condition}
INNER JOIN
It returns only the matching rows from the various tables. By default a join is an Inner Join. In the following example the output (the employee ID 6, 7 Employee details) are not shown, because the DeptId 5, 4 do not exist in the Mas_Department.
Example: Display Employee Details along with department name.
- Select E.Name, E.Salary, D.DeptName
- From Mas_Employee E
- Join Mas_Department D
- On E.DeptId = D.DeptId
OutputOUTER JOIN: Outer Joins are again divided into the following 3 types.
- Left Join or Left Outer Join
- Right Join or Right Outer Join
- Full Join or Full Outer Join
Left Outer Join
It returns all the matching rows and non-matching rows from the left table. A Left Outer Join can also be called a Left Join.
Example
- Select E.Name, E.Salary, D.DeptName
- From Mas_Employee E
- Left Join Mas_Department D
- On E.DeptId = D.DeptId
OutputRight Outer Join
It returns all the matching rows and non-matching rows from the right table. A Right Outer Join can also be called a Right Join.
Example
- Select E.Name, E.Salary, D.DeptName
- From Mas_Employee E
- Right Join Mas_Department D
- On E.DeptId = D.DeptId
OutputFull Outer Join
It returns all the matching rows and non-matching rows from both tables. A Full Outer Join can also be called a Full Join.
Example
- Select E.Name, E.Salary, D.DeptName
- From Mas_Employee E
- Full Join Mas_Department D
- On E.DeptId = D.DeptId
OutputCROSS JOIN
The join statement without any join condition is called a Cross Join. The result of this Cross Join is called a Cartesian Product. In other words, if Mas_Employee has 9 rows and Mas_Department has 3 rows then generate 9 X 3 = 27 rows in the output.
Example: Display Employee Details by associating every employee with every department.
- Select E.Name, E.Salary, D.DeptName
- From Mas_Employee E
- Right Join Mas_Department D
SELF JOIN
The join statement that joins a table to itself is called a Self Join.
Example: Display Employee Details along with his/her manager's name.
- Select E.ID, E.Name, E.Sal, E.Manager, M.Name
- From Employee E
- Join Employee M
- On E.Manager=M.ID
Note: A rule to be followed when writing a join statement is the number of join conditions must be one less than the number of tables on which you join.