database concepts

Advanced SQL : advanced querying

SELECT processing order

For the query from the reading:

     SELECT i1.CustABN
     FROM INVOICE i1, INVOICE i2
     WHERE i1.CustABN = i2.CustABN
     AND TRUNC(i1.InvDate) = DATE '2004-9-16'
     AND TRUNC(i2.InvDate) = DATE '2004-10-6';

Using the six step process work out the query output after each step.


Step 1: FROM

This creates the cross product for the two instances of the table INVOICE. Every row in the INVOICE table is combined with every other row.

The first lines of the output are:

Showme

Output

Notice that the two dates needed for the query, now appear on the same row.

Step 2: WHERE

All the rows that do not satisfy the where condition i.e.
      WHERE i1.CustABN = i2.CustABN
      AND TRUNC(i1.InvDate) = DATE '2004-9-16'
      AND TRUNC(i2.InvDate) = DATE '2004-10-6';
 are eliminated.

The first lines of the output are:

Showme

output

The spurious rows from Step One are deleted.

Step 3 & 4: GROUP BY & HAVING

These steps are not applicable

Step 5: SELECT

The output columns are now chosen:
     SELECT i1.CustABN

The output becomes:

Showme

output

Step 6: ORDERBY

This steps is not applicable