SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
9999
15 rows selected.
SQL> select * from emp3;
ENAME JOB COMM
---------- ---------- ----------
SMITH CLERK
ADAMS CLERK
JAMES CLERK
MILLER CLERK
SQL> select * from emp where COMM is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
9999
11 rows selected.
SQL> SELECT empno, ename, job, comm, deptno
FROM emp
WHERE (ename , job , comm) IN (SELECT ename, job , comm FROM emp3); 2 3
no rows selected
为什么会返回空行呢?
因为COMM列有空值
SQL> SELECT empno, ename, job, comm, deptno
FROM emp
WHERE ename || job || comm IN (SELECT ename || job || comm FROM emp3);
2 3
EMPNO ENAME JOB COMM DEPTNO
---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 20
7876 ADAMS CLERK 20
7900 JAMES CLERK 30
7934 MILLER CLERK 10
利用||拼接符号有空值会继续返回值
SQL> select ename || job || comm from emp where comm is null;
ENAME||JOB||COMM
------------------------------------------------------------
SMITHCLERK
JONESMANAGER
BLAKEMANAGER
CLARKMANAGER
SCOTTANALYST
KINGPRESIDENT
ADAMSCLERK
JAMESCLERK
FORDANALYST
MILLERCLERK
11 rows selected.
继续测试:
SQL> create table c1(id char(10));
Table created.
SQL> create table c2(id char(10));
Table created.
SQL> insert into c1 values(null);
1 row created.
SQL> insert into c2 values(null);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from c1;
ID
----------
SQL> select * from c2;
ID
----------
SQL> select c1.* from c1 where c1.id in (select c2.id from c2);
no rows selected
SQL> select c1.* from c1 where c1.id||'a' in (select c2.id||'a' from c2);
ID
----------
SQL>