Using SQL JOINS

SQL JOINS are used to join the data from different tables which are related to each other with the help of some common data in their columns. For example we can have a ORDERS table which will have details abut the order made by some customer from CUSTOMER table and the common column between these two tables could be the CUSTOMER ID and with the help of this common column CUSTOMER ID we can easily get the whole details about the ORDERS.

+----+--------------+-------+---------------+------+
| ID | ITEM_ORDERED | PRICE | PURCHASE_DATE | C_ID |
+----+--------------+-------+---------------+------+
|  1 | APPLE WATCH  | 35000 | 2018-05-15    |    1 |
|  2 | IPHONE       | 55000 | 2018-05-25    |    2 |
|  3 | IPHONE       | 55000 | 2018-05-25    |    5 |
|  4 | IPHONE       | 55000 | 2018-05-25    |    3 |
|  5 | MACBOOK      | 59000 | 2018-05-25    |    4 |
|  6 | MACBOOK      | 59000 | 2018-05-25    |    1 |
|  7 | MACBOOK      | 59000 | 2018-05-25    |    6 |
+----+--------------+-------+---------------+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM CUSTOMER;
+----+--------+---------+------+-----------+---------------------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   | CREATION_DATE       |
+----+--------+---------+------+-----------+---------------------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     | 2018-05-23 00:16:35 |
|  2 | JONTY  | RHODES  |   40 | CHINA     | 2018-05-18 12:21:12 |
|  3 | JOHNY  | DEPP    |   45 | INDIA     | 2018-05-23 00:16:35 |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA | 2018-05-18 12:21:12 |
|  5 | JESSIE | JACKSON |   22 | CANADA    | 2018-05-23 00:16:35 |
|  6 | NICOLE | KIDMAN  |   50 | USA       | 2018-05-18 12:21:12 |
+----+--------+---------+------+-----------+---------------------+
6 rows in set (0.00 sec)

Now from above two tables we cannot get much information about the purchase unless we see both the tables together ans try to consolidate the data. So in order to get data from both these tables based on some common data we use SQL JOINS

SQL JOINS syntax with example -

Suppose we want to get the details of orders made by customers we can use the following SQL statement -

 SELECT C.*, O.* FROM CUSTOMER AS C INNER JOIN ORDERS AS O ON  C.ID = O.C_ID;

+----+--------+---------+------+-----------+---------------------+----+--------------+-------+---------------+------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   | CREATION_DATE       | ID | ITEM_ORDERED | PRICE | PURCHASE_DATE | C_ID |
+----+--------+---------+------+-----------+---------------------+----+--------------+-------+---------------+------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     | 2018-05-23 00:16:35 |  1 | APPLE WATCH  | 35000 | 2018-05-15    |    1 |
|  1 | AKSHAY | KUMAR   |   12 | INDIA     | 2018-05-23 00:16:35 |  6 | MACBOOK      | 59000 | 2018-05-25    |    1 |
|  2 | JONTY  | RHODES  |   40 | CHINA     | 2018-05-18 12:21:12 |  2 | IPHONE       | 55000 | 2018-05-25    |    2 |
|  3 | JOHNY  | DEPP    |   45 | INDIA     | 2018-05-23 00:16:35 |  4 | IPHONE       | 55000 | 2018-05-25    |    3 |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA | 2018-05-18 12:21:12 |  5 | MACBOOK      | 59000 | 2018-05-25    |    4 |
|  5 | JESSIE | JACKSON |   22 | CANADA    | 2018-05-23 00:16:35 |  3 | IPHONE       | 55000 | 2018-05-25    |    5 |
|  6 | NICOLE | KIDMAN  |   50 | USA       | 2018-05-18 12:21:12 |  7 | MACBOOK      | 59000 | 2018-05-25    |    6 |
+----+--------+---------+------+-----------+---------------------+----+--------------+-------+---------------+------+
7 rows in set (0.06 sec)

Here we can see that we have merged the rows of both CUSTOMERS and ORDERS tables and got the details of ORDERS made by each customer in a single table form.
You can notice that in the above example we have used INNER join, similarly there some other kind of joins that are used to combine the tables data in different scenarios.

SQL type of JOINS and their usage -

MySQl supports the following joins -
1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. CROSS JOIN
MySQl does not support OUTER JOIN. We will learn about these joins and their usages in next tutorials.