SQL LEFT JOIN
SQL LEFT JOIN
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.
LEFT JOIN Syntax:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Customer table:
id | name | age | address | salary |
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
Orders table:
oid | date | customer_id | Amount |
102 | 2009-10-08 | 3 | 3000 |
100 | 2009-10-08 | 3 | 1500 |
101 | 2009-11-20 | 2 | 1560 |
| 103 | 2008-05-20 | 4 | 2060 |
Example :
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
The OUTPUT for the above query:
id | name | amount | date |
1 | Ramesh | NULL | NULL |
2 | Khilan | 1560 | 2009-11-20 |
3 | kaushik | 3000 | 2009-10-08 |
| 3 | kaushik | 1500 | 2009-10-08 |
| 4 | Chaitali | 2060 | 2008-05-20 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |