Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    2

    Unanswered: What's the difference between following queries?

    Hello everybody,

    I have a big problem with course assignmet. Namely, professor says that the following two queries give the different output and I need to make them to be the same. The second query shoud be written using INNEER JOIN. However I don't see why output would be different.

    Original query:
    SELECT o_orderpriority, COUNT(*), AS order_count
    FROM orders
    WHERE o_orderdate >= DATE(1993-07-01)
    AND o_orderdate < DATE(1993-07-01) + 3 MONTHS
    AND EXISTS
    (SELECT *
    FROM lineitem
    WHERE l_orderkey = o_orderkey AND
    L_commitdate < l_receiptdate)
    GROUP BY o_orderpriority
    ORDER BY o_orderpriority

    Re-written query:
    SELECT o_orderpriority, COUNT(*) AS order_count
    FROM orders INNER JOIN lineitem ON o_orderkey = l_orderkey
    WHERE o_orderdate BETWEEN '1993-07-01' AND '1993-09-30'
    AND l_commitdate < l_receiptdate
    GROUP BY o_orderpriority
    ORDER BY o_orderpriority


    Definition of tables:
    CREATE TABLE ORDERS (O_ORDERKEY INTEGER PRIMARY KEY,
    O_CUSTKEY INTEGER NOT NULL,
    O_ORDERSTATUS CHAR(1) NOT NULL,
    O_TOTALPRICE FLOAT NOT NULL,
    O_ORDERDATE DATE NOT NULL,
    O_ORDERPRIORITY CHAR(15) NOT NULL,
    O_CLERK CHAR(15) NOT NULL,
    O_SHIPPRIORITY INTEGER NOT NULL,
    O_COMMENT VARCHAR(79) NOT NULL) ;

    CREATE TABLE LINEITEM (L_ORDERKEY INTEGER PRIMARY KEY,
    L_PARTKEY INTEGER NOT NULL,
    L_SUPPKEY INTEGER NOT NULL,
    L_LINENUMBER INTEGER PRIMARY KEY,
    L_QUANTITY FLOAT NOT NULL,
    L_EXTENDEDPRICE FLOAT NOT NULL,
    L_DISCOUNT FLOAT NOT NULL,
    L_TAX FLOAT NOT NULL,
    L_DISCOUNT DECIMAL(15,2) NOT NULL,
    L_TAX DECIMAL(15,2) NOT NULL,
    L_RETURNFLAG CHAR(1) NOT NULL,
    L_LINESTATUS CHAR(1) NOT NULL,
    L_SHIPDATE DATE NOT NULL,
    L_COMMITDATE DATE NOT NULL,
    L_RECEIPTDATE DATE NOT NULL,
    L_SHIPINSTRUCT CHAR(25) NOT NULL,
    L_SHIPMODE CHAR(10) NOT NULL,
    L_COMMENT VARCHAR(44) NOT NULL) ;

    Thank you in advance for help!

    Nikola

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The first query counts orders that have at least 1 line item. The second counts order line items.

    Andy

  3. #3
    Join Date
    Feb 2010
    Posts
    2
    Thank you very much for quick response.

    nikola

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •