oracle in 空值替换,【学习笔记】测试not in或not exists对连接列有空值的处理方法...

【学习笔记】测试not in或not exists对连接列有空值的处理方法

时间:2016-11-02 20:13   来源:Oracle研究中心   作者:HTZ   点击:

次

天萃荷净

Oracle研究中心学习笔记：分享一篇关于SQL语句在连接查询时遇到列值有空值的处理方法，测试not in或not exists对连接列有空值的案例。

1，环境介绍

oracleplus.net> select * from v$version where rownum=1;

BANNER

——————————————————————————

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

oracleplus.net> !uname -a

Linux orcl9i 2.6.9-89.EL #1 Mon Apr 20 10:22:29 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

oracleplus.net> @parameter_hide.sql

oracleplus.net> set echo off

Enter Search Parameter (i.e. max|all) : _optimizer_null_aware_antijoin

PARAMETER SESSION_VALUE INSTANCE_VALUE DESCRIPTION

—————————————- ——————– ——————– —————————–

_optimizer_null_aware_antijoin TRUE TRUE null-aware antijoin parameter

2，创建测试环境

oracleplus.net> create table htz.ht1 (id number,name varchar2(10));

Table created.

oracleplus.net> create table htz.ht2 (id number,name varchar2(10));

Table created.

oracleplus.net> insert into htz.ht1 values (1,’htz1′);

1 row created.

oracleplus.net> insert into htz.ht2 values (1,’htz1′);

1 row created.

oracleplus.net> insert into htz.ht2 values (2,’htz2′);

1 row created.

oracleplus.net> insert into htz.ht1 values (2,’htz2′);

1 row created.

oracleplus.net> insert into htz.ht1 values (3,’htz3′);

1 row created.

oracl