Re: Curious SQL question

From: Tonkuma <tonkuma_at_jp.ibm.com>
Date: 4 Jan 2007 12:04:53 -0800
Message-ID: <1167941093.742111.73550_at_42g2000cwt.googlegroups.com>

Tonkuma wrote:
> Or
> ------------------------ Commands Entered ------------------------
> SELECT Customer_ID, Product_Name
> FROM (SELECT C.Customer_ID, Product_Name
> FROM (SELECT DISTINCT
> Customer_ID
> FROM Guto.Orders) C
> INNER JOIN
> Guto.Products
> ON 0=0
> ) CP
> EXCEPT
> SELECT O.Customer_ID, Product_Name
> FROM Guto.Orders O
> INNER JOIN
> Guto.Products P
> ON O.Product_ID = P.Product_ID
> ORDER BY
> Customer_ID, Product_Name;
> ------------------------------------------------------------------------------

>

> CUSTOMER_ID PRODUCT_NAME
> ----------- ------------
> john bread
> john wine
> mary shampoo
> mary wine

>
> 4 record(s) selected.

Second sample in my previous post may misleading the meaning of query because of inappropriate indention.
Here is corrected one.

               INNER JOIN
               Guto.Products
                 ON 0=0

These lines moved to left.

------------------------ Commands Entered ------------------------

SELECT Customer_ID, Product_Name
  FROM (SELECT C.Customer_ID, Product_Name

          FROM (SELECT DISTINCT
                       Customer_ID
                  FROM Guto.Orders) C
               INNER JOIN
               Guto.Products
                 ON 0=0
       ) CP
       EXCEPT
       SELECT O.Customer_ID, Product_Name
         FROM Guto.Orders   O
              INNER JOIN
              Guto.Products P
                ON O.Product_ID  = P.Product_ID
 ORDER BY
       Customer_ID, Product_Name;

Depending on your DBMS, following may result better performance. SELECT Customer_ID, Product_Name
  FROM (

        (SELECT C.Customer_ID, P.Product_ID
           FROM (SELECT DISTINCT
                        Customer_ID
                   FROM Guto.Orders) C
                INNER JOIN
                Guto.Products P
                  ON 0=0
        )
        EXCEPT
        SELECT Customer_ID, Product_ID
          FROM Guto.Orders
       ) NP
       INNER JOIN
       Guto.Products P
         ON P.Product_ID = NP.Product_ID
 ORDER BY
       Customer_ID, Product_Name;

Received on Thu Jan 04 2007 - 21:04:53 CET