Re: Are the two SQLs equivalent?

Jinshan Li wrote:
>
> Are the two SQLs equivalent? And , you think which is better.
> 1.FROM table1
> WHERE
> RTRIM(START_DATE||BNK_CD||STN_CD) IN
> ( SELECT MAX(START_DATE)||BNK_CD||STN_CD
> FROM table1 KYO, table2 SYS
> WHERE START_DATE <= SYS.CNTR_DATE
> AND ( DEL_DATE < SYS.CNTR_DATE OR DEL_DATE IS NULL)
> GROUP BY BNK_CD,STN_CD
> )
>
> 2.FROM table1 BT
> WHERE
> EXISTS
> ( SELECT 'A'
> FROM table1 KYO, table12 SYS
> WHERE KYO.START_DATE <= SYS.CNTR_DATE
> AND ( KYO.DEL_DATE < SYS.CNTR_DATE OR KYO.DEL_DATE IS NULL)
> AND BT.BNK_CD = KYO.BNK_CD AND BT.STN_CD = KYO.STN_CD
> having BT.START_DATE = MAX(START_DATE)
> )

Others have indicated that 1. has a coupla problems: + The concatenation can easily produce incorrect results unless you use unique delimiters.
+ The concatenation reduces optimization possibilities.

Unfortunately, the optimization issues are much bigger than that. Most DBMSs have extremely quirky optimizers ... especially Oracle (see some of Celko's comments on this group). This probably means that testing is required to answer to question. The best answer is to get a DBMS with a true (uniform) optimizer.

That said, I still would reformulate 2. to:

 2.FROM table1 BT

     WHERE
       BT.START_DATE =
      (  SELECT MAX(START_DATE)
         FROM table1    KYO,  table12 SYS
         WHERE KYO.START_DATE <= SYS.CNTR_DATE
           AND ( KYO.DEL_DATE < SYS.CNTR_DATE OR KYO.DEL_DATE IS NULL)
           AND BT.BNK_CD = KYO.BNK_CD    AND BT.STN_CD = KYO.STN_CD
      )

Note: the use of '=' rather than 'IN' for the subquery is a minor optimization.

Lastly, when using EXISTS the preferred form for the SELECT clause is 'SELECT *'. Unless the optimizer is crazy it should make no difference and is clearer.

-- 
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS  (http://www.firstsql.com)