Introduction
This is an article about joins in sql server . The article covers all aspects of joins using proper examples and Venn diagrams.
What is meant by joins in SQL server ?
Joins are the commands used to combine data from two or more tables based on relation between them. The relation between them is specified using columns from each tables and relational operators like =,<,> and <>.
General Syntax : –
|
1 2 3 4 |
SELECT <column_list> FROM table_1 type_of_join table_2 ON table_1.column_name Operator table_2.column_name --Valid Operators =,<,>,<> |
Types Of joins in sql server
You can following types of joins in sql server.
- Inner Join
- Outer Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Self Join
- Cross Join
Before getting into each of them let me introduce table Employee and Job. These two tables will help to discuss each joins with pra.
|
1 2 3 4 5 |
SELECT * FROM Employee SELECT * FROM Job |
⌛️ Employee
| employeeId | name | supervisorId |
| 1 | Steven | 1 |
| 2 | Nicole | 1 |
| 3 | Jhon | 1 |
| 4 | Sarah | 3 |
⌛️ Job
| jobId | jobNumber | employeeId |
| 1 | JB104 | 2 |
| 2 | JB122 | 3 |
| 3 | JB312 | 4 |
| 4 | JB452 | 0 |
Inner Join
Inner join displays only rows that matches the join condition in both tables.
Syntax : –
|
1 2 3 |
SELECT t1.column_name,t2.column_name FROM table_1 t1 INNER JOIN table-2 t2 ON t1.column_name=t2.column_name |
Instead of INNER JOIN you can use JOIN also both are same.
Venn diagram representation
Illustration :- inner shaded region of Venn diagram represents rows that satisfies joining condition hence it is knows as Inner Join.
Point of Interest (Employee,Job) : – Get Jobs of all employees who has at least one job ?
Ans :
|
1 2 3 |
SELECT E.name,J.jobNumber FROM EMPLOYEE E INNER JOIN JOB J ON E.employeeId=J.employeeId |
⌛️ Output
| name | jobNumber |
| Nicole | JB104 |
| Jhon | JB122 |
| Sarah | JB312 |
Left Outer Join
This type of join returns all rows from the left(first) table with second table rows that matches the join condition. rows without match will have NULL values for second table columns.
Both left outer join and left join are refers exact same operation.
Syntax :-
|
1 2 3 |
SELECT t1.column_name,t2.column_name FROM table_1 t1 LEFT JOIN table-2 t2 ON t1.column_name=t2.column_name |
Venn diagram representation
Point of Interest (Employee,Job) : – Get all employee with their jobs?
Ans :
|
1 2 3 |
SELECT E.name,J.jobNumber FROM EMPLOYEE E LEFT JOIN JOB J ON E.employeeId=J.employeeId |
⌛️ Output
| name | jobNumber |
| Steven | NULL |
| Nicole | JB104 |
| Jhon | JB122 |
| Sarah | JB312 |
Right Outer Join
Right join is just an opposite for left join, All rows from right(second) table with matching rows in first table will displayed. If no match found NULL values will be returned for first table columns.
Both Right Outer Join and Right Join are same.
Syntax :-
|
1 2 3 |
SELECT t1.column_name,t2.column_name FROM table_1 t1 RIGHT JOIN table-2 t2 ON t1.column_name=t2.column_name |
Venn diagram representation
Point of Interest (Employee,Job) : – Get all Jobs with employee in charge?
Ans :-
|
1 2 3 |
SELECT E.name,J.jobNumber FROM EMPLOYEE E RIGHT JOIN JOB J ON E.employeeId=J.employeeId |
⌛️ Output
| name | jobNumber |
| Nicole | JB104 |
| Jhon | JB122 |
| Sarah | JB312 |
| NULL | JB452 |
Full Outer Join
Full Outer Join returns all rows from both left and right tables. Since it can be treated as combined result of both LEFT and RIGHT joins.
Syntax :-
|
1 2 3 |
SELECT t1.column_name,t2.column_name FROM table_1 t1 FULL JOIN table-2 t2 ON t1.column_name=t2.column_name |
Venn diagram representation
Point of Interest (Employee,Job) : – List Out Employee-Job Relation ?
Ans :-
|
1 2 3 |
SELECT E.name,J.jobNumber FROM EMPLOYEE E FULL JOIN JOB J ON E.employeeId=J.employeeId |
⌛️ Output
| name | jobNumber |
| Steven | NULL |
| Nicole | JB104 |
| Jhon | JB122 |
| Sarah | JB312 |
| NULL | JB452 |
Self Join
Self Join is a type of join where both left and right tables are same table.
Syntax :-
|
1 2 3 |
SELECT t1.column_name,t2.column_name FROM table_1 t1 TYPE_OF_JOIN table-1 t2 ON t1.column_name=t2.column_name |
In the syntax, you can see TYPE_OF_JOIN it can be any of the following joins like inner join or outer joins or cross join.
In case of self join you must use alias names for joining tables to differentiate one from another.
Point of Interest (Employee,Job) : where would we use it ?, well consider the following situation.
Query to get details of employee and his supervisor details in one row ?Ans :-
|
1 2 3 |
SELECT E1.name as Employee,E2.name as Supervisor FROM Employee E1 INNER JOIN Employee E2 ON E1.supervisorId = E2.employeeId |
⌛️ Output
| Employee | Supervisor |
| Steven | Steven |
| Nicole | Steven |
| Jhon | Steven |
| Sarah | Jhon |
Cross Join (Cartesian Join)
Cross join returns Cartesian product of the tables hence this type of join also knows as Cartesian Join.
Syntax :-
|
1 2 |
SELECT t1.column_name,t2.column_name FROM table_1 t1 CROSS JOIN table-2 t2 |
OR
|
1 2 |
SELECT t1.column_name,t2.column_name FROM table_1 t1 , table-2 t2 |
In case of cross join ON clause is not allowed. instead of ON we may use WHERE for additional filter operation.
Visual Representation
Consider above two tables Car and Color for explaining cross join, Cross join will return Cartesian product of the tables. Represented as Car X Color
Point of Interest (Car,Color) :- can u picture it’s practical use ?
Q: Get all the color combination for available cars ?
Ans :
|
1 2 |
SELECT Car.carName,Color.color FROM Car cross join Color |
⌛️ Output
Well, you can imagine the output of this cross join, can you guess number of rows returned by this cross join
Yes, it will 16(4*4), Number of rows returned from cross join operation is the product of number of rows in both tables.
This is interesting!😇
If you change join conditions in ON clause as 1=1 for inner joins and outer join resulting output will be cross join.
Example :
|
1 2 3 |
SELECT E.name,J.jobNumber FROM EMPLOYEE E FULL JOIN JOB J ON 1=1 |
Try it your self.
Nice, thank you!!