我正在使用Oracle 11gR2
,并且试图编写一个查询,该查询返回两个表CUSTOMERS
和LOCATIONS
的地址数据。给定的客户可能(也可能没有)有不同的位置,每个位置都有自己的地址。
我想返回每个客户及其所有位置的地址。例如,如果表包含如下数据:
CUSTOMERS CUSTOMER_ID ADDRESS 1 "New York" 2 "California"LOCATIONS CUSTOMER_ID LOCATION_ID ADDRESS 1 1 "New Jersey"
Then I want the results to look like:
CUSTOMER_ID LOCATION_ID ADDRESS 1 "New York" 1 1 "New Jersey" 2 "California"
My first thought was something like this:
SELECT
CUSTOMERS.CUSTOMER_ID,
LOCATIONS.LOCATION_ID,
NVL(LOCATIONS.ADDRESS,CUSTOMERS.ADDRESS) ADDRESS
FROM
CUSTOMERS
LEFT JOIN
LOCATIONS ON (CUSTOMERS.CUSTOMER_ID=LOCATIONS.CUSTOMER_ID)
这样做的问题是,当客户确实有位置时,它不会返回位置数据为空值的行,因此我在
CUSTOMERS
表中没有得到地址所在的行。它给了我这样的东西:CUSTOMER_ID LOCATION_ID ADDRESS 1 1 "New Jersey" 2 "California"
It's missing the New York
address for customer 1
. I tried this...
SELECT
CUSTOMERS.CUSTOMER_ID,
LOCATIONS.LOCATION_ID,
NVL(LOCATIONS.ADDRESS,CUSTOMERS.ADDRESS) ADDRESS
FROM
CUSTOMERS
LEFT JOIN
LOCATIONS ON (CUSTOMERS.CUSTOMER_ID=LOCATIONS.CUSTOMER_ID OR LOCATIONS.CUSTOMER_ID IS NULL)
但这给了我与第一个查询相同的结果。即使连接条件匹配,有没有办法为第二张表返回空记录?
最佳答案
您完全不需要在这里加入:
SELECT customer_id, NULL AS location_id, address
FROM customers
UNION ALL
SELECT customer_id, location_id, address
FROM locations