MySQL Left join.
MySQL left joins join the table and find all matching column entries corresponding to first table in second table. If no entry is found the result will be printed as null.
Find two tables as below.
mysql> select * from company;
+------------+---------------+--------------+
| COMPANY_ID | COMPANY_NAME | COMPANY_CITY |
+------------+---------------+--------------+
| 18 | Order All | Boston |
| 15 | Jack Hill Ltd | London |
| 16 | Akas Foods | Delhi |
| 17 | Foodies | London |
| 19 | sip-n-Bite | New York |
+------------+---------------+--------------+
5 rows in set (0.01 sec)
mysql> select * from foods;
+---------+--------------+-----------+------------+
| ITEM_ID | ITEM_NAME | ITEM_UNIT | COMPANY_ID |
+---------+--------------+-----------+------------+
| 1 | Chex Mix | Pcs | 16 |
| 6 | Cheez-It | Pcs | 15 |
| 2 | BN Biscuit | Pcs | 15 |
| 3 | Mighty Munch | Pcs | 17 |
| 4 | Pot Rice | Pcs | 15 |
| 5 | Jaffa Cakes | Pcs | 18 |
| 7 | Salt n Shake | Pcs | 0 |
+---------+--------------+-----------+------------+
7 rows in set (0.01 sec)
mysql>
I am combining two tables and printing only 3 columns company.COMPANY_ID,company.COMPANY_CITY,food.COMPANY_ID to get a clarity.
mysql> select c.COMPANY_ID,c.COMPANY_CITY,f.COMPANY_ID from company c, foods f;
+------------+--------------+------------+
| COMPANY_ID | COMPANY_CITY | COMPANY_ID |
+------------+--------------+------------+
| 18 | Boston | 16 |
| 15 | London | 16 |
| 16 | Delhi | 16 | <=======
| 17 | London | 16 |
| 19 | New York | 16 |
| 18 | Boston | 15 |
| 15 | London | 15 | <=======
| 16 | Delhi | 15 |
| 17 | London | 15 |
| 19 | New York | 15 |
| 18 | Boston | 15 |
| 15 | London | 15 | <=======
| 16 | Delhi | 15 |
| 17 | London | 15 |
| 19 | New York | 15 |
| 18 | Boston | 17 |
| 15 | London | 17 |
| 16 | Delhi | 17 |
| 17 | London | 17 | <=======
| 19 | New York | 17 |
| 18 | Boston | 15 |
| 15 | London | 15 | <=======
| 16 | Delhi | 15 |
| 17 | London | 15 |
| 19 | New York | 15 |
| 18 | Boston | 18 | <=======
| 15 | London | 18 |
| 16 | Delhi | 18 |
| 17 | London | 18 |
| 19 | New York | 18 |
| 18 | Boston | 0 |
| 15 | London | 0 |
| 16 | Delhi | 0 |
| 17 | London | 0 |
| 19 | New York | 0 |
+------------+--------------+------------+
35 rows in set (0.02 sec)
mysql>
Now, I am doing a left join. So it will find the matching rows on the first table on the second table and print the corresponding row. This has been highlighted above with arrows. Now, no entry corresponding to the the COMPANY_ID. So the output will be null.
So, the syntax and the output of the left join is as below.
mysql> select c.COMPANY_ID,c.COMPANY_CITY,f.COMPANY_ID from company c LEFT JOIN foods f ON c.COMPANY_ID=f.COMPANY_ID;
+------------+--------------+------------+
| COMPANY_ID | COMPANY_CITY | COMPANY_ID |
+------------+--------------+------------+
| 18 | Boston | 18 |
| 15 | London | 15 |
| 15 | London | 15 |
| 15 | London | 15 |
| 16 | Delhi | 16 |
| 17 | London | 17 |
| 19 | New York | NULL |
+------------+--------------+------------+
7 rows in set (0.02 sec)
mysql>
Refer the below URL to know more about this.
URL : http://www.w3resource.com/sql/joins/perform-a-left-join.php
MySQL left joins join the table and find all matching column entries corresponding to first table in second table. If no entry is found the result will be printed as null.
Find two tables as below.
mysql> select * from company;
+------------+---------------+--------------+
| COMPANY_ID | COMPANY_NAME | COMPANY_CITY |
+------------+---------------+--------------+
| 18 | Order All | Boston |
| 15 | Jack Hill Ltd | London |
| 16 | Akas Foods | Delhi |
| 17 | Foodies | London |
| 19 | sip-n-Bite | New York |
+------------+---------------+--------------+
5 rows in set (0.01 sec)
mysql> select * from foods;
+---------+--------------+-----------+------------+
| ITEM_ID | ITEM_NAME | ITEM_UNIT | COMPANY_ID |
+---------+--------------+-----------+------------+
| 1 | Chex Mix | Pcs | 16 |
| 6 | Cheez-It | Pcs | 15 |
| 2 | BN Biscuit | Pcs | 15 |
| 3 | Mighty Munch | Pcs | 17 |
| 4 | Pot Rice | Pcs | 15 |
| 5 | Jaffa Cakes | Pcs | 18 |
| 7 | Salt n Shake | Pcs | 0 |
+---------+--------------+-----------+------------+
7 rows in set (0.01 sec)
mysql>
I am combining two tables and printing only 3 columns company.COMPANY_ID,company.COMPANY_CITY,food.COMPANY_ID to get a clarity.
mysql> select c.COMPANY_ID,c.COMPANY_CITY,f.COMPANY_ID from company c, foods f;
+------------+--------------+------------+
| COMPANY_ID | COMPANY_CITY | COMPANY_ID |
+------------+--------------+------------+
| 18 | Boston | 16 |
| 15 | London | 16 |
| 16 | Delhi | 16 | <=======
| 17 | London | 16 |
| 19 | New York | 16 |
| 18 | Boston | 15 |
| 15 | London | 15 | <=======
| 16 | Delhi | 15 |
| 17 | London | 15 |
| 19 | New York | 15 |
| 18 | Boston | 15 |
| 15 | London | 15 | <=======
| 16 | Delhi | 15 |
| 17 | London | 15 |
| 19 | New York | 15 |
| 18 | Boston | 17 |
| 15 | London | 17 |
| 16 | Delhi | 17 |
| 17 | London | 17 | <=======
| 19 | New York | 17 |
| 18 | Boston | 15 |
| 15 | London | 15 | <=======
| 16 | Delhi | 15 |
| 17 | London | 15 |
| 19 | New York | 15 |
| 18 | Boston | 18 | <=======
| 15 | London | 18 |
| 16 | Delhi | 18 |
| 17 | London | 18 |
| 19 | New York | 18 |
| 18 | Boston | 0 |
| 15 | London | 0 |
| 16 | Delhi | 0 |
| 17 | London | 0 |
| 19 | New York | 0 |
+------------+--------------+------------+
35 rows in set (0.02 sec)
mysql>
Now, I am doing a left join. So it will find the matching rows on the first table on the second table and print the corresponding row. This has been highlighted above with arrows. Now, no entry corresponding to the the COMPANY_ID. So the output will be null.
So, the syntax and the output of the left join is as below.
mysql> select c.COMPANY_ID,c.COMPANY_CITY,f.COMPANY_ID from company c LEFT JOIN foods f ON c.COMPANY_ID=f.COMPANY_ID;
+------------+--------------+------------+
| COMPANY_ID | COMPANY_CITY | COMPANY_ID |
+------------+--------------+------------+
| 18 | Boston | 18 |
| 15 | London | 15 |
| 15 | London | 15 |
| 15 | London | 15 |
| 16 | Delhi | 16 |
| 17 | London | 17 |
| 19 | New York | NULL |
+------------+--------------+------------+
7 rows in set (0.02 sec)
mysql>
Refer the below URL to know more about this.
URL : http://www.w3resource.com/sql/joins/perform-a-left-join.php