Overview
Joins in SQL are used for retrieving data from several relations. A join is formed whenever multiple relations are listed in the same SQL statement.
There are four kinds of joins in SQL-inner, left outer, right outer, and full outer join. In this article, we shall learn about all these types of joins in SQL.
Scope
- Inner join
- Left outer join
- Right inner join
- Full outer join
Inner join
Inner join is the simplest type of join in SQL. It returns all the rows from various relations where that meet the join condition.
Syntax
Example
For demonstration purposes, we will use the following tables as a reference throughout the article:
Customers:
| customer_id | last_name | first_name | website |
|---|---|---|---|
| 1 | Jackson | Joe | techonthenet.com |
| 2 | Smith | Jane | digminecraft.com |
| 3 | Ferguson | Samantha | bigactivities.com |
| 4 | Reynolds | Allen | checkyourmath.com |
| 5 | Anderson | Paige | NULL |
| 6 | Johnson | Derekn | techonthenet.com |
Orders:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 4 | 2016/04/18 |
| 2 | 2 | 2016/04/18 |
| 3 | 5 | 2016/04/19 |
| 4 | 1 | 2016/04/20 |
| 5 | NULL | 2016/05/01 |
Following code block shows how we perform an inner join on the relations above:
write your code here: Coding Playground
Output
Left join
A left join returns all the rows from the table that is mentioned on the left-hand side in the SQL join statement, along with all those rows from the right-hand side table where the join condition is met.
Syntax
Note
In some DBMSs, the keyword “OUTER” can be omitted.
Example
write your code here: Coding Playground
Output
Right Join
A right join returns all the rows from the relation mentioned on the right-hand side in the “on” part of the SQL join statement and all those from the other table where the join condition is met.
Syntax
Example
write your code here: Coding Playground
Output
Full Join
A full join returns all the rows from the left-hand side as well as the right-hand side tables with a NULL value in places where the join condition is not met.
Syntax
Example
write your code here: Coding Playground
Output