SQL Select Statement

1
SQL Select Statement

• Prof. Yitzchak Rosenthal

2
Syntax for SELECT statement

• Clauses must be written in the following order
• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY

3
Order that the DBMS processes a SELECT statement

• Order that the DBMS processes a SELECT statement
• Step 1 FROM
• Step 2 WHERE
• Step 3 GROUP BY
• Step 4 HAVING
• Step 5 SELECT (this must be "writtten" first)
• Step 6 ORDER BY
• At each step the DBMS keeps track of the "interim
  result set" which is then further refined by the
  next step
• Keep reading for more info ...

4
Step 1 FROM clause

• Step 1 FROM clause
• This step is processed slightly differently
  depending on whether the SQL 92 syntax is used or
  the pre-SQL 92 syntax is used
• SQL 92 Syntax (i.e. JOIN/ON in FROM clause)
• generate cartesian product (ie "cross join") of
  (1) the first table and (2) the table in the
  first JOIN clause
• filter out records from cartesian product that
  don't match the first ON clause
• generate cartesian product of (1) the results so
  far and (2) the table in the next JOIN clause
• filter out records from cartesian product that
  don't match the associated "ON" clause
• keep proceeding in this way until all tables are
  joined
• Older Syntax (i.e. no JOIN/ON in FROM clause)
• generate cartesian product (ie "cross join") of
  all the tables
• (the filtering will hapen later when the "WHERE"
  clause is processed)

5
Step 2 WHERE clause

• Step 2 WHERE clause
• Filter out records from the "interim result set"
  generated in Step 1 by filtering out (i.e.
  "throwing out") records that don't match the
  conditions in the WHERE clauseEach record in
  the interim result set is looked at separately
  and the results of the WHERE clause is
  calculated. If the result of the WHERE clause
  for that row is TRUE then the row is kept. If the
  result of the WHERE clause for that row is FALSE
  then the row is "thrown away".

6
Step 3 GROUP BY

• Step 3 GROUP BY
• Create separate groups of rows that match in all
  of the values listed in the GROUP BY list.
  There may be a single group for all records in
  the interim result set or there may be many
  groups. There is ALWAYS at least one group.

7
Step 4 HAVING

• Step 4 HAVING
• Filter out all groups that don't match the
  conditions in the HAVING clause

8
Step 5 SELECT

• Step 5 SELECT
• Figure out what values will actually be included
  in the final result set by processing the SELECT
  clause

9
Step 6 ORDER BY

• Step 6 ORDER BY
• Sort the result set in the order specified in the
  ORDER BY clause

10
WHERE vs HAVING
11
WHERE vs. HAVING

• Similarities
• The WHERE and HAVING clauses are both used to
  exclude records from the result set.
• Differences
• WHERE clause
• The WHERE clause is processed before the groups
  are created
• Therefore, the WHERE clause can refer to any
  value in the original tables
• HAVING clause
• The HAVING clause is processed after the groups
  are created
• Therefore, the HAVING clause can only refer to
  aggregate information for the group (including
  fields that are part of the GROUP BY clause).
• The HAVING clause CANNOT refer to individual
  columns from a table that are not also part of
  the group.

12
Example
13
Example Table

• The example on the following slides will use the
  following table

14
Example of HAVING clause without a WHERE clause
15
HAVING clause but NO WHERE clause

• In the following SELECT statement
• There is a HAVING clause but no WHERE clause
• The GROUP BY clause works to group several rows
  from the original table together to get aggregate
  information about the group.
• The HAVING clause eliminates some of the
  resulting rows of aggregate information.
• SELECT vendorId, avg(PaymentTotal) as
  avgPaymentTotalFROM invoicesGROUP BY
  vendorIdHAVING avg(PaymentTotal) lt10ORDER BY
  avgPaymentTotal

16
Processing the select without WHERE
Step 1 Create the groups based on the GROUP
BYStep 2 Generate the aggregate information
(e.g. avg) for each group.
Interim result set
avgPaymentTotal
10
group1
20
group2
17
Processing the select without WHERE
Step 3 Remove records from the result set based
on the HAVING clause
Final Results
avgPaymentTotal
10
group1
20
group2
18
Adding a WHERE clause to the example
19
Same select statement with WHERE

• We will now examine what happens when we add a
  WHERE clause to the same SELECT statement we used
  above.
• SELECT vendorId, avg(PaymentTotal) as
  avgPaymentTotalFROM invoicesWHERE invoiceTotal
  lt 1000GROUP BY vendorIdHAVING avg(PaymentTotal)
  lt10ORDER BY avgPaymentTotal

20
Processing the select with where
Step 1 Process WHERE clause to eliminate some
rows from consideration
21
Processing the select with where
Step 2 Process the GROUP BY to create groups
from the remaining rows.
Interim result set
avgPaymentTotal
7.5
group1
10
group2
22
Processing the select with where
Step 3 Process the HAVING clause to possibly
remove some rows from the result set (in this
example no rows need to be removed)
Final results
avgPaymentTotal
7.5
group1
10
group2