Last updated on May 10th, 2016 at 01:41 am

VN:F [1.9.22_1171]
Rating: 7.5/10 (4 votes cast)

How to use left join in sql ?

I am trying to approach left join in sql with very simple explanation. The basic syntax of LEFT JOIN is as follows:

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

Here given condition could be any given expression based on your requirement.
Example:
Consider following two tables, (a) CUSTOMERS table is as follows:

Table 1

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   |  Total   |
+----+----------+-----+-----------+----------+
|  1 | Cathy    |  32 | US        |  2000.00 |
|  2 | Fedric   |  25 | UK        |  1500.00 |
|  3 | Gary     |  23 | Aus       |  2000.00 |
|  4 | Rose     |  25 | England   |  6500.00 |
|  5 | Edward   |  27 | US        |  8500.00 |
|  6 | Craig    |  22 | NZ        |  4500.00 |
|  7 | Steve    |  24 | SA        | 10000.00 |
+----+----------+-----+-----------+----------+
 

(b) Another table is ORDERS as follows:

Table 2
——-

+-----+---------------------+-------------+--------+
| OID | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 112 | 2013-10-08 00:00:00 |           3 |   3000 |
| 120 | 2013-10-08 00:00:00 |           3 |   1500 |
| 121 | 2013-11-20 00:00:00 |           2 |   1560 |
| 133 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+
 

Now let us join these two tables using LEFT JOIN as follows:

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

This would produce following result:

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                |
+----+----------+--------+---------------------+
|  1 | Cathy    |   NULL | NULL                |
|  2 | Fedric   |   1560 | 2013-11-20 00:00:00 |
|  3 | Gary     |   3000 | 2013-10-08 00:00:00 |
|  3 | Gary     |   1500 | 2013-10-08 00:00:00 |
|  4 | Rose     |   2060 | 2008-05-20 00:00:00 |
|  5 | Edward   |   NULL | NULL                |
|  6 | Craig    |   NULL | NULL                |
|  7 | Steve    |   NULL | NULL                |
+----+----------+--------+---------------------+
VN:F [1.9.22_1171]
Rating: 7.5/10 (4 votes cast)

Leave a Reply

Your email address will not be published. Required fields are marked *