Last updated on May 10th, 2016 at 01:41 am
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 | +----+----------+--------+---------------------+
admin