ORA-00933 与 NATURAL JOIN

今天被问到与NATURAL JOIN 相关的一个错误，下面简单模拟一下：

 

SQL> SELECT order_id, product_id, unit_price * quantity "Total Price"
  2    FROM order_items
  3   WHERE unit_price * quantity > 50000
  4   natural  join orders
  5  ;

 

SELECT order_id, product_id, unit_price * quantity "Total Price"
  FROM order_items
 WHERE unit_price * quantity > 50000
 natural  join orders

ORA-00933: SQL 命令未正确结束 

 

 

 

 

根据规定，把where 条件放在natural join 的后面，就ok了。

 

SQL> SELECT order_id, product_id, unit_price * quantity "Total Price"
  2    FROM order_items
  3    natural  join orders
  4   WHERE unit_price * quantity > 50000;

     ORDER_ID PRODUCT_ID Total Price
------------- ---------- -----------
         2434       2236     79741.2

 

 

natural join是对两张表中字段名和数据类型都相同的字段进行等值连接，并返回符合条件的结果 。

 

从以下的SQL中我们可以发现ORDER_ITEMS与ORDERS表中ORDER_ID列名和字数据类型都是相同的。

 

SQL> SELECT column_name, data_type, count(1)
  2          FROM user_tab_columns
  3       WHERE table_name IN ('ORDER_ITEMS', 'ORDERS')
  4         GROUP BY column_name, data_type
  5        HAVING count(1) >= 2;

COLUMN_NAME  DATA_TYPE         COUNT(1)
------------          ---------------          ----------
ORDER_ID              NUMBER                     2

 

 

上述SQL可以等价改写为

SQL>  SELECT i.order_id, i.product_id, i.unit_price * quantity "Total Price"
  2    FROM order_items  i  ,orders  o
  3    WHERE  i.order_id=o.order_id
  4    AND unit_price * quantity > 50000;

 

 

上述两种写法，那个效率更高一些呢。实验看一下。

 

SQL>  ALTER SYSTEM FLUSH SHARED_POOL;

System altered

SQL> set timing on;
SQL>
SQL> SELECT order_id, product_id, unit_price * quantity "Total Price"
  2    FROM order_items natural
  3      join orders
  4   WHERE unit_price * quantity > 50000;

     ORDER_ID PRODUCT_ID Total Price
------------- ---------- -----------
         2434       2236     79741.2
         2400       3003     54465.4
         2434       2252     68616.9
         2435       2350     56205.6
         2440       2350     56205.6
         2361       2359       51584
         2367       2350    126462.6
         2371       2350     74940.8
         2385       2350    255267.1
         2388       2350    262292.8
         2446       2350     91334.1
         2458       3127     55677.6

12 rows selected

Executed in 0.172 seconds

 

 

SQL>  ALTER SYSTEM FLUSH SHARED_POOL;

System altered

Executed in 0.031 seconds

SQL>
SQL>  SELECT i.order_id, i.product_id, i.unit_price * quantity "Total Price"
  2    FROM order_items  i  ,orders  o
  3    WHERE  i.order_id=o.order_id
  4    AND unit_price * quantity > 50000;

     ORDER_ID PRODUCT_ID Total Price
------------- ---------- -----------
         2434       2236     79741.2
         2400       3003     54465.4
         2434       2252     68616.9
         2435       2350     56205.6
         2440       2350     56205.6
         2361       2359       51584
         2367       2350    126462.6
         2371       2350     74940.8
         2385       2350    255267.1
         2388       2350    262292.8
         2446       2350     91334.1
         2458       3127     55677.6

12 rows selected

Executed in 0.14 seconds

简单的从执行时间上看，第二中写法的效率优与natural join。

 

 

 

 

 

 

 

 

outer_join_clause

 

[ query_partition_clause ]
{ outer_join_type JOIN
| NATURAL [ outer_join_type ] JOIN
}
table_reference [ query_partition_clause ]
  [ ON condition
  | USING ( column [, column ]...)
  ]