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 |
+-------------------+----------+--------------+



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.



Trending Articles


Incorrect table definition there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Google Betting Big on AI Future

How to solve IE/Edge CORS error ?

4 Tips to Succeed in Online Marketing

To get the best out of your computer

Offline Methods to Get Visitors

Infix To Postfix Conversion Using Stack in Javascript

Are dual rear cameras on chinese phones fake?