Join more than two tables

Joins can be used to connect any number of tables.


SQL> create table ord(
  2           order_no               integer          primary key
  3          ,cust_no                integer
  4          ,order_date             date not null
  5          ,total_order_price      number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment_method         varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,gift_message           varchar2(100)
 12  );
insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
values(1,1,'14-Feb-2002', 23.00, '14-Feb-2002', '12 noon', 'CA',1, null, 'Gift for wife');
insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
values(2,1,'14-Feb-2003', 510.98, '14-feb-2003', '5 pm', 'NY',7, 'Rose Ted', 'Happy Valentines Day to Mother');
insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
values(3, 2,'14-Feb-2004', 315.99, '14-feb-2004', '3 pm', 'VS',2, 'Ani Forest', 'Happy Valentines Day to Father');
insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
values(4, 2,'14-Feb-1999', 191.95, '14-feb-1999', '2 pm', 'NJ',2, 'O. John', 'Happy Valentines Day');
insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message    )
values(5, 6,'4-mar-2002', 101.95, '5-mar-2002', '2:30 pm', 'MO'   , 2, 'Cora', 'Happy Birthday from John');
insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
values(6, 9,'7-apr-2003', 221.95, '7-apr-2003', '3 pm', 'MA', 2, 'Sake Keith', 'Happy Birthday from Joe' );
insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
values(7, 9,'20-jun-2004', 315.95, '21-jun-2004', '12 noon', 'BC', 2, 'Jessica Li', 'Happy Birthday from Jessica');
insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
values (8, 12, '31-dec-1999', 135.95, '1-jan-2000', '12 noon', 'DI',      3, 'Larry', 'Happy New Year from Lawrence');
insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
values (9, 12, '26-dec-2003', 715.95, '2-jan-2004', '12 noon', 'SK',7, 'Did', 'Happy Birthday from Nancy' );
insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
values(10, 4, sysdate-1, 119.95, sysdate+2, '6:30 pm', 'VG',2, 'P. Jing', 'Happy Valentines Day to Jason');
insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
values(11, 2, sysdate, 310.00, sysdate+2, '3:30 pm', 'DC',2, 'C. Late', 'Happy Birthday Day to Jack');
insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
values(12, 7, sysdate-3, 121.95, sysdate-2, '1:30 pm', 'AC',2, 'W. Last', 'Happy Birthday Day to You');
insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
values(13, 7, sysdate, 211.95, sysdate-4, '4:30 pm', 'CA',2, 'J. Bond', 'Thanks for hard working');

SQL> create table department(
  2          dept_no                 integer      primary key
  3         ,dept_name               varchar(20)      not null
  4         ,mgr_no                  integer
  5  );
insert into department(dept_no, dept_name, mgr_no)values(1, 'Design', 1);
insert into department(dept_no, dept_name, mgr_no)values(2, 'Sales', 1);
insert into department(dept_no, dept_name, mgr_no)values(3, 'Development', 1);

SQL> create table employee(
  2           emp_no                 integer         primary key
  3          ,lastname               varchar2(20)    not null
  4          ,firstname              varchar2(15)    not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,zip_4                  varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,salary                 number(3)
 14          ,birthdate              date
 15          ,hiredate               date
 16          ,title                  varchar2(20)
 17          ,dept_no                integer
 18          ,mgr                    integer
 19          ,region                 number
 20          ,division               number
 21          ,total_sales            number
 22  );
insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
values (1,'Anderson','Nancy','N','33 Ave','London','NY','11111','1111','212','234-1111',3.75,'21-mar-1927','1-feb-1947','Sales Manager',2,null,100,10,40000);
insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
values (2,'Last','First','F','12 Ave','Paris','CA','22222','2222','111','867-2222',7.75,'14-feb-1976','15-mar-1985','Sales Clerk',2,1,100,10,10000);
insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
values (3,'Wash','Georgia','G','1 Street14','Barton','NJ','33333','3333','214','340-3333',11.50,'2-jul-1977','21-apr-2004','Designer',1,2,100,10,40000);
insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
values (4,'Bush','Dave','D','56 Street','Island','RI','44444','4444','215','777-4444',21.65,'15-may-1945','2-aug-1975','Designer',1,2,100,10,40000);
insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
values (5,'Will','Robin','W','56 Street','Island','MA','55555','5555','216','777-5555',24.65,'10-dec-1980','2-aug-2007','Designer',1,5,100,10,40000);
insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
values (6,'Pete','Mona','M','13 Ave','York','MO','66666','6666','217','111-6666',9,'14-feb-1966','15-mar-1985','Sales Clerk',2,5,100,10,40000);
insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
values (7,'Roke','John','J','67 Ave','New York','BC','77777','7777','218','122-7777',10.00,'14-jun-1955','15-mar-1975','Accountant',3,2,100,10,40000);
insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
values (8,'Horry','Tedi','T','1236 Lane','Newton','NY','88888','8888','219','222-8888',13.00,'10-jun-1955','15-aug-1985','Sales Representative',3,2,100,10,50000);
insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
values (9,'Bar','Candi','C','400 East Street','Yorken','NY','99999','9999','220','321-9999',12.00,'10-oct-1933','15-jan-1969','Sales Representative',3,5,100,10,35000);

SQL> select d.dept_name, e.lastname, o.order_date
  2  from department d, employee e, ord o
  3  where d.dept_no = e.dept_no
  4  and e.emp_no = o.emp_no
  5  /
DEPT_NAME            LASTNAME             ORDER_DATE
-------------------- -------------------- --------------------
Sales                Anderson             14-FEB-2002 00:00:00
Development          Roke                 14-FEB-2003 00:00:00
Sales                Last                 14-FEB-2004 00:00:00
Sales                Last                 14-FEB-1999 00:00:00
Sales                Last                 04-MAR-2002 00:00:00
Sales                Last                 07-APR-2003 00:00:00
Sales                Last                 20-JUN-2004 00:00:00
Design               Wash                 31-DEC-1999 00:00:00
Development          Roke                 26-DEC-2003 00:00:00
Sales                Last                 15-JUN-2008 17:39:15
Sales                Last                 16-JUN-2008 17:39:16
Sales                Last                 13-JUN-2008 17:39:17
Sales                Last                 16-JUN-2008 17:39:17
Home »
Oracle »
Select » 

Join:
  1. Table Join
  2. Using Table name to reference duplicate names
  3. Table Alias
  4. Cartesian Products
  5. Join more than two tables
  6. Join Conditions and Join Types
  7. Outer Joins
  8. Left and Right Outer Joins
  9. Outer join Error
  10. Self Join
  11. Outer Self Join
  12. Inner Joins Using SQL/92
  13. Joins with USING Keyword
  14. Inner Joins with More than Two Tables Using SQL/92
  15. Inner Joins on Multiple Columns Using SQL/92
  16. Outer Joins in SQL/92 Syntax
  17. Self Joins Using SQL/92
  18. Cross Joins Using SQL/92
Related: