Consider the following sql
select d.deptno, d.location , e.ename, e.init from emp e, departments d where e.deptno = d.deptno order by d.deptno, e.ename;
The result shows no rows for department 40, since department does exist in the DEPARTMENTS table.
If tuple variable d refers to department 40, there is not a single row e in the EMPLOYEES table to make the WHERE clause evaluate to TRUE.
To include department 40 in your join results, use an outer join.
For outer joins in Oracle, you can choose between two syntax options:
SQL> SQL> drop table emp; Table dropped.-- from ww w . java 2 s . c om SQL> create table emp( 2 empno NUMBER(4) primary key, 3 ename VARCHAR2(8) not null , 4 init VARCHAR2(5) not null , 5 job VARCHAR2(8) , 6 mgr NUMBER(4) , 7 bdate DATE not null , 8 msal NUMBER(6,2) not null , 9 comm NUMBER(6,2) , 10 deptno NUMBER(2) default 10) ; SQL> SQL> insert into emp values(7001,'SMITH','N', 'TRAINER', 7902,date '1975-12-17', 1800 , NULL, 20); SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20', 1600, 300, 30); SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02', 1250, 500, 10); SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02', 2975, NULL, 20); SQL> insert into emp values(7005,'BROWN','P', 'SALESREP',7006,date '1976-09-28', 1250, 1400, 30); SQL> insert into emp values(7006,'BLAKE','R', 'MANAGER', 7009,date '1973-11-01', 2850, NULL, 10); SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09', 2450, NULL, 10); SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26', 3000, NULL, 20); SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17', 5000, NULL, 10); SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28', 1500, 0, 30); SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30', 1100, NULL, 20); SQL> insert into emp values(7012,'JONES','R', 'ADMIN', 7006,date '1979-10-03', 8000, NULL, 30); SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13', 3000, NULL, 20); SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN', 7007,date '1972-01-23', 1300, NULL, 10); SQL> SQL> drop table departments; Table dropped. SQL> SQL> create table departments( 2 deptno NUMBER(2) primary key, 3 dname VARCHAR2(10) not null unique check (dname = upper(dname)), 4 location VARCHAR2(8) not null check (location = upper(location)), 5 mgr NUMBER(4)) ; SQL> SQL> insert into departments values (10,'ACCOUNTING','NEW YORK',7007); SQL> insert into departments values (20,'TRAINING', 'DALLAS', 7004); SQL> insert into departments values (30,'SALES', 'CHICAGO', 7006); SQL> insert into departments values (40,'HR', 'BOSTON', 7009); SQL> SQL> select d.deptno, d.location 2 , e.ename, e.init 3 from emp e, departments d 4 where e.deptno = d.deptno 5 order by d.deptno, e.ename; DEPTNO | LOCATION | ENAME | INIT --------- | -------- | -------- | ----- 1 | NEW YORK | BLAKE | R 1 | NEW YORK | CLARK | AB 1 | NEW YORK | KING | CC 1 | NEW YORK | MARY | ABC 1 | NEW YORK | WARD | TF 2 | DALLAS | ADAMS | AA 2 | DALLAS | FORD | MG 2 | DALLAS | JACK | JM 2 | DALLAS | SCOTT | DEF 2 | DALLAS | SMITH | N 30 | CHICAGO | ALLEN | JAM DEPTNO | LOCATION | ENAME | INIT --------- | -------- | -------- | ----- 30 | CHICAGO | BREAD | JJ 30 | CHICAGO | BROWN | P 30 | CHICAGO | JONES | R 14 rows selected. SQL>