SQL LEFT JOIN(左連接)

SQL LEFT JOIN返回所有行左表,即使存在左表中沒有匹配。這意味着,如果在ON子句在右邊的表匹配0(零)個記錄,則連接將仍然在結果返回一行,但用NULL顯示在右表中的每一列中。

這意味着,一個左連接從左表中返回所有值,再加上如果右表沒有匹配就使用NULL值代替返回。

語法

LEFT JOIN的基本語法如下:

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

在這裏,給定的條件可能是根據您的需要任意給定表達式。

例子:

考慮下面的兩個表中,(a)CUSTOMERS表如下:

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

(b)另一個ORDERS 表如下:

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

現在,讓我們使用LEFT JOIN在這兩個表如下:

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

這將產生以下結果:

+----+----------+--------+---------------------+ | ID | NAME | AMOUNT | DATE | +----+----------+--------+---------------------+ | 1 | Ramesh | NULL | NULL | | 2 | Khilan | 1560 | 2009-11-20 00:00:00 | | 3 | kaushik | 3000 | 2009-10-08 00:00:00 | | 3 | kaushik | 1500 | 2009-10-08 00:00:00 | | 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | | 5 | Hardik | NULL | NULL | | 6 | Komal | NULL | NULL | | 7 | Muffy | NULL | NULL | +----+----------+--------+---------------------+

0 條評論,你可以發表評論,我們會進行改進
Comment author placeholder