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> --
Related examples in the same category