SQL

SQL Joins With Examples (Inner Join, Left Join, Right Join, Full Join, Self Join)

SQL Joins :

SQL joins are mostly used to connect multiple tables and get the record according to our requirement.

There are 5 types of SQL joins

  1. Inner Join

  2. Left Join

  3. Right Join

  4. Full Join

  5. Self Join

SQL Inner Join

This is the most commonly join used in the real-time project.  By using INNER JOIN we can generate a new result by combining two tables or more than that based on our requirement. Here query compares each row of table_name_1 with each row of table_name_2 to find all pairs of rows.

Note: We can combine as many tables as per our requirement. Let’s see how it works. First, we will see the syntax of the inner join.

Syntax:

SELECT column_names
FROM table_name_1
INNER JOIN table_name_2 ON table_name_1.column_name = table_name_2.column_name;

For example, just see below and try to understand

Table -1 CUSTOMERS Table is as follows.     

+------------+--------------+-------------+
| CustomerId | CustomerName | CustomerAge |
+------------+--------------+-------------+
|          1 | John         |          25 |
|          2 | Smith        |          27 |
|          3 | Lulia        |          32 |
|          4 | Kristin      |          26 |
|          5 | Martin       |          22 |
+------------+--------------+-------------+

 Table -2  Orders Table is as follows.

+---------+-----------+------------+--------+
| OrderId | OrderName | CustomerId | Amount |
+---------+-----------+------------+--------+
|       1 | Pizza     |          2 |    150 |
|       2 | Burger    |          2 |     50 |
|       3 | Noddle    |          4 |     30 |
|       4 | Ice Cream |          5 |     20 |
+---------+-----------+------------+--------+

Let’s we want to find customer name along with the order name and amount.

Appling Inner join

SELECT customers.CustomerName,orders.OrderName,orders.Amount
FROM customers
INNER JOIN orders on orders.CustomerId=customers.CustomerId

Below is the result for above query

+--------------+-----------+--------+
| CustomerName | OrderName | Amount |
+--------------+-----------+--------+
| Smith        | Pizza     |    150 |
| Smith        | Burger    |     50 |
| Kristin      | Noddle    |     30 |
| Martin       | Ice Cream |     20 |
+--------------+-----------+--------+

For more detail, you can check here

SQL Left Join

The SQL LEFT JOIN will return all the rows from the left table also it returns the null value in the column where it does not match value from the right table.

Syntax:

SELECT column_names
FROM table_name_1
LEFT JOIN table_name_2 ON table_name_1.column_name = table_name_2.column_name;

For example, just see below and try to understand

Table -1 CUSTOMERS Table is as follows.   

+------------+--------------+-------------+
| CustomerId | CustomerName | CustomerAge |
+------------+--------------+-------------+
|          1 | John         |          25 |
|          2 | Smith        |          27 |
|          3 | Lulia        |          32 |
|          4 | Kristin      |          26 |
|          5 | Martin       |          22 |
+------------+--------------+-------------+

 Table -2  Orders Table is as follows.

+---------+-----------+------------+--------+
| OrderId | OrderName | CustomerId | Amount |
+---------+-----------+------------+--------+
|       1 | Pizza     |          2 |    150 |
|       2 | Burger    |          2 |     50 |
|       3 | Noddle    |          4 |     30 |
|       4 | Ice Cream |          5 |     20 |
+---------+-----------+------------+--------+
SELECT customers.CustomerName,orders.OrderName,orders.Amount
FROM customers
LEFT JOIN orders on orders.CustomerId=customers.CustomerId

As a result for above query

+--------------+-----------+--------+
| CustomerName | OrderName | Amount |
+--------------+-----------+--------+
| Smith        | Pizza     |    150 |
| Smith        | Burger    |     50 |
| Kristin      | Noddle    |     30 |
| Martin       | Ice Cream |     20 |
| John         | NULL      |   NULL |
| Lulia        | NULL      |   NULL |
+--------------+-----------+--------+

For more detail, you can check here

SQL Right Join

The SQL RIGHT JOIN returns all rows from the right table. Suppose there are no matches in the left table then also the join will still return a row in the result.

This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no match.

SELECT column_names
FROM table_name_1
RIGHT JOIN table_name_2 ON table_name_1.column_name = table_name_2.column_name;

For example, just see below and try to understand

Table -1 CUSTOMERS Table is as follows.   

+------------+--------------+-------------+
| CustomerId | CustomerName | CustomerAge |
+------------+--------------+-------------+
|          1 | John         |          25 |
|          2 | Smith        |          27 |
|          3 | Lulia        |          32 |
|          4 | Kristin      |          26 |
|          5 | Martin       |          22 |
+------------+--------------+-------------+

 Table -2  Orders Table is as follows.

+---------+-----------+------------+--------+
| OrderId | OrderName | CustomerId | Amount |
+---------+-----------+------------+--------+
|       1 | Pizza     |          2 |    150 |
|       2 | Burger    |          2 |     50 |
|       3 | Noddle    |          4 |     30 |
|       4 | Ice Cream |          5 |     20 |
+---------+-----------+------------+--------+
SELECT customers.CustomerName,orders.OrderName,orders.Amount
FROM customers
RIGHT JOIN orders on orders.CustomerId=customers.CustomerId

As a result for above query

+--------------+-----------+--------+
| CustomerName | OrderName | Amount |
+--------------+-----------+--------+
| Smith        | Pizza     |    150 |
| Smith        | Burger    |     50 |
| Kristin      | Noddle    |     30 |
| Martin       | Ice Cream |     20 |
+--------------+-----------+--------+

For more detail, you can check here

SQL Full Join

The SQL FULL JOIN combines the results of both left and right outer joins. In full join all the row listed by join both side.

The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.

SELECT column_names
FROM table_name_1
FULL OUTER JOIN table_name_2 ON table_name_1.column_name = table_name_2.column_name;

For example, just see below and try to understand

Table -1 CUSTOMERS Table is as follows.   

+------------+--------------+-------------+
| CustomerId | CustomerName | CustomerAge |
+------------+--------------+-------------+
|          1 | John         |          25 |
|          2 | Smith        |          27 |
|          3 | Lulia        |          32 |
|          4 | Kristin      |          26 |
|          5 | Martin       |          22 |
+------------+--------------+-------------+

 Table -2  Orders Table is as follows.

+---------+-----------+------------+--------+
| OrderId | OrderName | CustomerId | Amount |
+---------+-----------+------------+--------+
|       1 | Pizza     |          2 |    150 |
|       2 | Burger    |          2 |     50 |
|       3 | Noddle    |          4 |     30 |
|       4 | Ice Cream |          5 |     20 |
+---------+-----------+------------+--------+
SELECT customers.CustomerName,orders.OrderName,orders.Amount
FROM customers
FULL OUTER JOIN orders on orders.CustomerId=customers.CustomerId

As a result for above query

+--------------+-----------+--------+
| CustomerName | OrderName | Amount |
+--------------+-----------+--------+
| John         | NULL      |   NULL |
| Smith        | Pizza     |    150 |
| Smith        | Burger    |     50 |
| Lulia        | NULL      |   NULL |
| Kristin      | Noddle    |     30 |
| Martin       | Ice Cream |     20 |
+--------------+-----------+--------+

For more detail, you can check here

SQL Self Join

The SQL SELF JOIN is used to join a table to itself. Here we renaming the table as temporary as T 1 and T2 for the same table.

Syntax: 

SELECT column_name
FROM table_name_1 T1, table_name_1 T2
WHERE condition;

Table -1 CUSTOMERS Table is as follows.   

+------------+--------------+-------------+
| CustomerId | CustomerName | CustomerAge |
+------------+--------------+-------------+
|          1 | John         |          25 |
|          2 | Smith        |          27 |
|          3 | Lulia        |          32 |
|          4 | Kristin      |          26 |
|          5 | Martin       |          22 |
+------------+--------------+-------------+

After applying self-join in that we apply a condition on customer age as you see below.

SELECT  a.CustomerID, b.CustomerNAME, a.CustomerAge
FROM Customers a, Customers b
WHERE a.CustomerAge < b.CustomerAge;

We get the following result

+------------+--------------+-------------+
| CustomerID | CustomerNAME | CustomerAge |
+------------+--------------+-------------+
|          5 | John         |          22 |
|          1 | Smith        |          25 |
|          4 | Smith        |          26 |
|          5 | Smith        |          22 |
|          1 | Lulia        |          25 |
|          2 | Lulia        |          27 |
|          4 | Lulia        |          26 |
|          5 | Lulia        |          22 |
|          1 | Kristin      |          25 |
|          5 | Kristin      |          22 |
+------------+--------------+-------------+

 

Also Read  SQL RIGHT JOIN With Examples

About the author

Avtar

I am a blogger and I always believes in sharing a knowledge and happiness because happiness is the last journey of life.

Leave a Comment

Share2
Tweet
Pin
+1
Share
2 Shares