您将使用任何具有null类的记录返回交叉产品。你的结果好吗?
我在11gR2中创建了两个示例查询:
WITH a as
(select NULL as class, 5 as columna from dual
UNION
select NULL as class, 7 as columna from dual
UNION
select NULL as class, 9 as columna from dual
UNION
select 'X' as class, 3 as columna from dual
UNION
select 'Y' as class, 2 as columna from dual),
b as
(select NULL as class, 2 as columnb from dual
UNION
select NULL as class, 15 as columnb from dual
UNION
select NULL as class, 5 as columnb from dual
UNION
select 'X' as class, 7 as columnb from dual
UNION
select 'Y' as class, 9 as columnb from dual)
SELECT * from a JOIN b ON (a.class = b.class
OR (a.class is null AND b.class is null))当我在此查询上运行EXPLAIN PLAN时,它表示表(我的情况下是内联视图)通过NESTED LOOPS加入。 NESTED LOOPS连接通过扫描一个表的第一行,然后扫描另一个表的每一行进行匹配,然后扫描第一个表的第二行,在第二个表上查找匹配等等来进行操作。因为您不是直接比较无论是JOIN的OR部分中的表,优化器都必须使用NESTED LOOPS。
在幕后它可能看起来像:
获取表A,第1行。如果class为null,则在结果集中包含表A中的该行。
仍然在表A第1行上,搜索表B,其中所有行都为class。
在表A第1行和表B中的所有行上执行交叉产品
在结果集中包含这些行
获取表A,第2行。如果class为null,则在结果集中包含表A中的此行。
.... etc
当我将SELECT语句更改为SELECT * FROM a JOIN b ON NVL(a.class, 'N/A') = NVL(b.class, 'N/A')时,EXPLAIN指示使用了HASH JOIN。散列连接实质上生成较小表的每个连接键的散列,然后扫描大表,在匹配的每一行的较小表中查找散列。在这种情况下,由于它是一个简单的Equijoin,优化器可以毫无问题地散列驱动表的每一行。
在幕后它可能看起来像:
通过表A,将NULL类值转换为'N / A'
随时随地散列表格A的每一行。
哈希表A现在位于临时空间或内存中。
扫描表B,将NULL类值转换为'N / A',然后计算值的哈希值。散列表中的查找散列(如果存在)包括结果集中表A和B的连接行。
继续扫描B.
如果您对查询运行EXPLAIN PLAN,您可能会发现类似的结果。
尽管最终结果是相同的,因为您没有使用“OR”连接第一个查询中的表,优化程序无法使用更好的连接方法。如果驱动表很大,或者如果要对大型辅助表强制进行全表扫描,则嵌套的LOOPS可能会非常慢。
您可以使用ANSI COALESCE函数来模拟其他数据库系统中的NVL oracle函数。这里真正的问题是你试图加入一个NULL值,你真的应该有一个“NO CLASS”或一些其他方法来识别null = null而不是null = unknown的“null”类。
在评论中回答您的问题的附录:
对于null查询,SQL引擎将执行以下操作:
从表A中读取第1行,类为空,转换为“N / A”。
表B有3行,其中class为null,将每个null转换为'N / A'。
由于第一行与所有3行匹配,因此我们的结果集中添加了3行,一行用于A1B1,A1B2,A1B3。
读取第2行从表A中,类为空,转换为“N / A”/
表B有3行,其中class为null,将每个null转换为'N / A'。
由于第二行与所有3行匹配,因此我们的结果集中添加了3行,一行用于A2B1,A2B2,A2B3。
读取第3行从表A中,类为空,转换为“N / A”/
表B有3行,其中class为null,将每个null转换为'N / A'。
由于第三行与所有3行匹配,因此我们的结果集中添加了3行,一行用于A3B1,A3B2,A3B3。
10 ..第4行和第5行不为空,因此不会在此部分连接中处理它们。
对于'N / A'查询,SQL引擎将执行以下操作:
从表A读取第1行,类为空,转换为'N / A',哈希此值。
从表A读取第2行,类为空,转换为'N / A',哈希此值。
从表A读取第3行,类为空,转换为'N / A',哈希此值。
从表A读取第4行,类not null,哈希此值。
从表A读取第5行,类not null,哈希此值。
哈希表C现在在内存中。
从表B中读取第1行,类为空,转换为“N / A”,哈希值。
将散列值与内存中的散列表进行比较,对于每个匹配,在结果集中添加一行。找到3行,A1,A2和A3。结果添加A1B1,A2B1,A3B1。
从表B读取第2行,类为空,转换为'N / A',哈希值。
将散列值与内存中的散列表进行比较,对于每个匹配,在结果集中添加一行。找到3行,A1,A2和A3。结果添加了A1B2,A2B2,A3B2。
从表B中读取第3行,类为空,转换为'N / A',哈希值。
将散列值与内存中的散列表进行比较,对于每个匹配,在结果集中添加一行。找到3行,A1,A2和A3。结果添加A1B3,A2B3,A3B3。