Joins and Its Types in SQL
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
SELECT column1, column2, … FROM table1 INNER JOIN table2 ON table1.column = table2.column; |
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:
SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id; |
write your code here: Coding Playground
Output
customer_id order_id order_date 1 4 2016/04/20 2 2 2016/04/18 4 1 2016/04/18 5 3 2016/04/19 |
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
SELECT column1, column2, … FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column; |
Note
In some DBMSs, the keyword “OUTER” can be omitted.
Example
SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customers.customer_id; |
write your code here: Coding Playground
Output
customer_id order_id order_date 1 4 2016/04/20 2 2 2016/04/18 3 NULL NULL 4 1 2016/04/18 5 3 2016/04/19 6 NULL NULL |
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
SELECT column1, column2, … FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column; |
Example
SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers RIGHT OUTER JOIN orders ON orders.customer_id=customers.customer_id ORDER BY customers.customer_id; |
write your code here: Coding Playground
Output
customer_id order_id order_date NULL 5 2016/05/01 1 4 2016/04/20 2 2 2016/04/18 4 1 2016/04/18 5 3 2016/04/19 |
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
SELECT column1, column2, … FROM table1 FULL [OUTER] JOIN table2 ON table1.column = table2.column; |
Example
SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers FULL OUTER JOIN orders ON orders.customer_id=customers.customer_id ORDER BY customers.customer_id; |
write your code here: Coding Playground
Output
customer_id order_id order_date NULL 5 2016/05/01 1 4 2016/04/20 2 2 2016/04/18 3 NULL NULL 4 1 2016/04/18 5 3 2016/04/19 6 NULL NULL |