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

The first query counts orders that have at least 1 line item. The second counts order line items.

Andy

Thank you very much for quick response.

nikola