SQL 在 Oracle 的集合运算符中使用 to_char(null)、to_number(null) 等
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14449104/
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
SQL Using to_char(null), to_number(null), etc in set operators with Oracle
提问by Troy D
I'm a student entering the world of SQL for the first time and I cannot find any clear and understandable documentation on how to use to_somedatatype(null) to substitute for missing columns in a UNION, INTERSECT, etc. I'm using the HR schema that comes build in with Oracle Database for my first few projects and the following query works (I get a list of the employee_id numbers for only those employees with dependents):
我是第一次进入 SQL 世界的学生，我找不到任何关于如何使用 to_somedatatype(null) 替换 UNION、INTERSECT 等中缺失列的清晰易懂的文档。我正在使用 HR为我的前几个项目内置于 Oracle 数据库中的模式和以下查询有效（我仅获得了那些有受抚养人的员工的 employee_id 编号列表）：
SELECT employee_id "Employees with Dependents"
FROM employees
INTERSECT
SELECT relative_id
FROM dependents;
However, as soon as I want to include the names of the employees and dependent birth dates, I'm stuck. This variation perform an error-free search but yields no results (outputs "no rows selected"):
但是，只要我想包括员工的姓名和受抚养人的出生日期，我就被卡住了。此变体执行无错误搜索但不产生任何结果（输出“未选择行”）：
SELECT e.employee_id "Employees with Dependents",
e.first_name,
e.last_name,
to_date(NULL) "Dependent Birthdate"
FROM employees e
INTERSECT
SELECT d.relative_id,
TO_CHAR(NULL),
TO_CHAR(NULL),
d.birthdate
FROM dependents d;
Several variations replacing to_date with to_number or just plain null and to_char with to_varchar2 or null fail to produce any rows.
将 to_date 替换为 to_number 或仅替换为 null 和 to_char 替换为 to_varchar2 或 null 的几种变体无法生成任何行。
What I'd like to produce is a single row for each dependent which displays the guardian's employee number, first name, last name, and the birth date of the dependent. I'm confident that my problem is the use of these null placeholders, combined with limited understanding of set operators in general. (My understanding is that, when I used a null placeholder like to_date(null), it will not be included in the comparison since there's nothing there to be compared to.)
我想为每个受抚养人生成一行，其中显示监护人的雇员编号、名字、姓氏和受抚养人的出生日期。我相信我的问题是使用这些空占位符，再加上对集合运算符的一般理解有限。（我的理解是，当我使用像 to_date(null) 这样的空占位符时，它不会被包含在比较中，因为没有什么可以比较的。）
Can someone please explain to me how to properly use these placeholders?
有人可以向我解释如何正确使用这些占位符吗？
采纳答案by Gordon Linoff
The reason your queries don't work is that you have no NULL values in the corresponding fields. You are using INTERSECT. Actually, you want to join the tables together, something like:
您的查询不起作用的原因是相应字段中没有 NULL 值。您正在使用INTERSECT. 实际上，您想将表连接在一起，例如：
SELECT e.employee_id "Employees with Dependents",
e.first_name,
e.last_name,
d.birthdate "Dependent Birthdate"
FROM employees e join
dependents d
on d.relative_id = e.employee_id
回答by Niyonshuti Rebero Yves
You must match the data type (using the TO_CHAR function or any other conversion functions) when columns do not exist in one or the other table. for example:
当一个或另一个表中不存在列时，您必须匹配数据类型（使用 TO_CHAR 函数或任何其他转换函数）。例如：
SELECT location_id, department_name "Department", TO_CHAR(NULL) "Warehouse location" FROM departments UNION SELECT location_id, TO_CHAR(NULL) "Department", state_province FROM locations;
SELECT location_id, Department_name "Department", TO_CHAR(NULL) "仓库位置" FROM 部门 UNION SELECT location_id, TO_CHAR(NULL) "Department", state_province FROM location;
let you relate with your scenario
让你与你的场景联系起来
