Mega Code Archive

Demo the ANSI outer join

SQL> SQL> SQL> create table ord_item( 2 order_no integer 3 ,product_id integer 4 ,quantity number(4,0) 5 ,item_price number(7,2) 6 ,total_order_item_price number(9,2) 7 ,primary key (order_no, product_id) 8 ); Table created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(1, 2, 10, 23.00 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(2, 1, 1, 23.00 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(2, 5, 1, 10.50 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(2, 8, 1, 17.48 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(3, 8, 1, 35.99 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(4, 7, 1, 19.95 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(5, 5, 1, 10.95 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(6, 8, 1, 22.95 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(7, 1, 6, 15.00 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(7, 5, 1, 10.50 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(7, 8, 1, 10.45 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(8, 8, 1, 35.95 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(9, 8, 1, 65.45 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(9, 5, 1, 10.50 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(10, 3, 1, 19.95 ); 1 row created. SQL> insert into ord_item(order_no, product_id, quantity, item_price)values(11, 8, 1, 30.00); 1 row created. SQL> SQL> create table product( 2 product_id integer primary key 3 ,price number(7,2) 4 ,description varchar2(75) 5 ,onhand number(5,0) 6 ,reorder number(5,0) 7 ,supplier_no integer 8 ); Table created. SQL> insert into product(product_id, price, description, onhand, reorder)values (1,2.50,'Oracle',100,20); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (2,23.00,'SQL Server',null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (3,null,'MySQL',null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (4,1.50,'DB2',50,10); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (5,10.50,'Java',100,20); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (6,45.00,'C++',null,null); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (7,19.99,'Javascript',3,5); 1 row created. SQL> insert into product(product_id, price, description, onhand, reorder)values (8,4.50,'Ruby',null,null); 1 row created. SQL> SQL> SQL> SQL> SELECT description, nvl(quantity,0) 2 FROM product LEFT OUTER JOIN ord_item 3 USING (product_id); DESCRIPTION NVL(QUANTITY,0) --------------------------------------------------------------------------- --------------- SQL Server 10 Oracle 1 Java 1 Ruby 1 Ruby 1 Javascript 1 Java 1 Ruby 1 Oracle 6 Java 1 Ruby 1 Ruby 1 Ruby 1 Java 1 MySQL 1 Ruby 1 C++ 0 DB2 0 18 rows selected. SQL> SQL> -- Same result with this query: SQL> SELECT p.description, nvl(o.quantity,0) 2 FROM product p LEFT OUTER JOIN ord_item o 3 ON p.product_id = o.product_id 4 / DESCRIPTION NVL(O.QUANTITY,0) --------------------------------------------------------------------------- ----------------- SQL Server 10 Oracle 1 Java 1 Ruby 1 Ruby 1 Javascript 1 Java 1 Ruby 1 Oracle 6 Java 1 Ruby 1 Ruby 1 Ruby 1 Java 1 MySQL 1 Ruby 1 C++ 0 DB2 0 18 rows selected. SQL> --Eqivalent traditional syntax: SQL> SELECT p.description, nvl(o.quantity,0) 2 FROM product p, ord_item o 3 WHERE p.product_id = o.product_id(+) 4 / DESCRIPTION NVL(O.QUANTITY,0) --------------------------------------------------------------------------- ----------------- SQL Server 10 Oracle 1 Java 1 Ruby 1 Ruby 1 Javascript 1 Java 1 Ruby 1 Oracle 6 Java 1 Ruby 1 Ruby 1 Ruby 1 Java 1 MySQL 1 Ruby 1 C++ 0 DB2 0 18 rows selected. SQL> SQL> drop table product; Table dropped. SQL> drop table ord_item; Table dropped. SQL> SQL> --