SELECT ... LEFT JOIN

---

SELECT Query with LEFT join

Returns all the records from the left table and the matched records from the right table.This join is also referred as LEFT OUTER JOIN

Syntax

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

Example

Consider the following two tables,

Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ranith  |  32 | Ahmedabad |  2000.00  |
|  2 | Krupa   |  25 | Delhi     |  1500.00  |
|  3 | kaulik  |  23 | Kota      |  2000.00  |
|  4 | Chaitan |  25 | Mumbai    |  6500.00  |
|  5 | Munny   |  24 | Indore    | 10000.00  |
+----+----------+-----+-----------+----------+

Table 2 − Orders Table is as follows.

+-----+---------------------+-------------+--------+
| OID | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Now, using the LEFT JOIN let us join these two tables as follows.

SELECT  ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                |
+----+----------+--------+---------------------+
|  1 | Ranith   |   NULL | NULL                |
|  2 | Krupa    |   1560 | 2009-11-20 00:00:00 |
|  3 | kaulik   |   3000 | 2009-10-08 00:00:00 |
|  3 | kaulik   |   1500 | 2009-10-08 00:00:00 |
|  4 | Chaitan  |   2060 | 2008-05-20 00:00:00 |
|  7 | Munny    |   NULL | NULL                |
+----+----------+--------+---------------------+