The following code joins three tables together.
SQL> SQL>-- ww w.j a v a 2s. c o m SQL> drop table emp; Table dropped. 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 salgrades; Table dropped. SQL> create table salgrades( 2 grade NUMBER(2) primary key, 3 lowerlimit NUMBER(6,2) not null check (lowerlimit >= 0), 4 upperlimit NUMBER(6,2) not null, 5 bonus NUMBER(6,2) not null); SQL> SQL> insert into salgrades values (1, 700,1200, 0); SQL> insert into salgrades values (2, 1201,1400, 50); SQL> insert into salgrades values (3, 1401,2000, 100); SQL> insert into salgrades values (4, 2001,3000, 200); SQL> insert into salgrades values (5, 3001,9999, 500); 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> SQL> select e.ename employee 2 , 12*e.msal+s.bonus total_salary 3 , d.dname department 4 from emp e 5 , salgrades s 6 , departments d 7 where e.msal between s.lowerlimit 8 and s.upperlimit 9 and e.deptno = d.deptno; EMPLOYEE | TOTAL_SALARY | DEPARTMENT -------- | ------------ | ---------- ADAMS | 13200.00 | TRAINING WARD | 15050.00 | ACCOUNTING BROWN | 15050.00 | SALES MARY | 15650.00 | ACCOUNTING SMITH | 21700.00 | TRAINING ALLEN | 19300.00 | SALES BREAD | 18100.00 | SALES JACK | 35900.00 | TRAINING BLAKE | 34400.00 | ACCOUNTING CLARK | 29600.00 | ACCOUNTING SCOTT | 36200.00 | TRAINING EMPLOYEE | TOTAL_SALARY | DEPARTMENT -------- | ------------ | ---------- FORD | 36200.00 | TRAINING KING | 60500.00 | ACCOUNTING JONES | 96500.00 | SALES 14 rows selected. SQL>
We now have three free tuple variables (e, s, and d) ranging over three tables.
We need at least two conditions in the WHERE clause to get the correct row combinations in the query result.
SQL language supports table names as default tuple variables, without the need to declare them explicitly in the FROM clause.
select emp.ename, departments.location from emp, departments where emp.deptno = departments.deptno;