oracle 总是包含空记录的左连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4548201/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Left Join that always includes null records
提问by Mike
I'm using Oracle 11gR2and I am trying to write a query that returns address data from two tables, CUSTOMERSand LOCATIONS. A given customer may (or may not) have different locations, each with their own address.
我正在使用Oracle 11gR2并且正在尝试编写一个查询，该查询从两个表中返回地址数据，CUSTOMERS并且LOCATIONS. 给定的客户可能（或可能没有）有不同的位置，每个位置都有自己的地址。
I would like to return the address for every customer, and all their locations. For example, if the tables contained data like:
我想返回每个客户的地址及其所有位置。例如，如果表包含如下数据：
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)
The problem with that is that when a customer does have locations, it does not return a row with null values for location data, so I don't get a row with the address in the CUSTOMERStable. It gives me something like this:
问题在于，当客户确实有位置时，它不会为位置数据返回具有空值的行，因此我没有在CUSTOMERS表中获得带有地址的行。它给了我这样的东西：
CUSTOMER_ID LOCATION_ID ADDRESS 1 1 "New Jersey" 2 "California"
It's missing the New Yorkaddress for customer 1. I tried this...
它缺少 的New York地址customer 1。我试过这个...
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)
But it gave me the same results as the first query. Is there a way to return a null record for the second table even when there is a match on the join condition?
但它给了我与第一个查询相同的结果。即使连接条件匹配，有没有办法为第二个表返回空记录？
回答by Quassnoi
You don't need a join here at all:
您根本不需要在这里加入：
SELECT customer_id, NULL AS location_id, address
FROM customers
UNION ALL
SELECT customer_id, location_id, address
FROM locations
回答by markthewizard1234
If you want to join the two tables even when there is a non match, you will need to use IS NULLon your joined columns.
如果您想在不匹配的情况下连接两个表，则需要IS NULL在连接的列上使用。
For example.
例如。
Table 1:
CustomerID
CustomerName
.
.
Table 2:
CustomerID
CustomerEmail
.
.
Select,
CustomerID,
CustomerName,
ISNULL (CustomerEmail, NULL) AS CustomerEmail
FROM table1
LEFT JOIN table2
ON table1.CustomerID = table2.CustomerID
This wil bring back results with NULL
这将带回结果 NULL
回答by Dan Iverson
You can try a full outer join. For example:
您可以尝试完整的外部联接。例如：
SELECT
CUSTOMERS.CUSTOMER_ID,
LOCATIONS.LOCATION_ID,
NVL(LOCATIONS.ADDRESS,CUSTOMERS.ADDRESS) ADDRESS
FROM CUSTOMERS
FULL OUTER JOIN LOCATIONS ON (CUSTOMERS.CUSTOMER_ID=LOCATIONS.CUSTOMER_ID)
