Overview

Left Join as the name suggests it returns all the result from the left table i.e. table 1 and all the rows that matches the ON clause from right table i.e. table 2. So Left Join will return all the rows from left table and it will return either matching rows from right table or null for non matching rows.

Even if it will not find any matching rows in right table at all in that case also it will return all the rows from left table.

Syntax

Syntax for SQL Left Join is

SELECT left_table.columns, right_table.columns FROM left_table LEFT JOIN right_table ON left_table.field = right_table.field WHERE [conditions].

Examples

Let us have a look at an example having two tables

i) hostel_list containing list of hostels.

+----+-------------------+-----------+----------+
| id | name | occupancy | capacity |
+----+-------------------+-----------+----------+
|  1 | ASHOK BHAVAN | 2 | 1090 |
|  2 | KAILASH BHAVAN | 2 | 990 |
|  3 | RUDRAKSH BHAVAN | 2 | 990 |
|  4 | KAVERI BHAVAN | 1 | 690 |
|  5 | BHAGIRATHI BHAVAN | 4 | 2140 |
|  6 | NARMADA BHAVAN | 6 | 3240 |
|  7 | GANGA BHAVAN | 1 | 780 |
|  8 | YAMUNA BHAVAN | 8 | 4580 |
+----+-------------------+-----------+----------+

ii) student_list containing list of students.

+-----+------------------+--------+-----------+
| sid | name | year | hostel_id |
+-----+------------------+--------+-----------+
|   1 | Ankur Rastogi | First | 4 |
|   2 | Amit Kumar | Second | 8 |
|   3 | Ram Kumar | Second | 8 |
|   4 | Shiv Saxena | First | 6 |
|   5 | Aman Aggarwal | Third | 3 |
|   6 | Nipun Gupta | Fourth | 2 |
|   7 | Arpit Gupta | Third | 1 |
|   8 | Shantanu Rastogi | Second | 3 |
|   9 | Ramesh Kumar | Third | 1 |
+-----+------------------+--------+-----------+

Now Let us fetch the list of hostels and students living their which have capacity between 1000 and 4000.

SELECT hostel.name, hostel.capacity, student.name FROM hostel_list hostel LEFT JOIN student_list student ON hostel.id = student.hostel_id WHERE hostel.capacity BETWEEN 1000 and 4000;
+-------------------+----------+--------------+
| name | capacity | name |
+-------------------+----------+--------------+
| NARMADA BHAVAN | 3240 | Shiv Saxena |
| ASHOK BHAVAN | 1090 | Arpit Gupta |
| ASHOK BHAVAN | 1090 | Ramesh Kumar |
| BHAGIRATHI BHAVAN | 2140 | NULL |
+-------------------+----------+--------------+


This article is contributed by Ankur Rastogi on Thu Mar 24 2016 16:21:14 GMT+0530 (IST)

If you want to add more comments to the article or you see any thing incorrect please write a comment below and we will surely get back to you.