SQL Select Statement Prof Yitzchak Rosenthal Syntax for

SQL Select Statement Prof. Yitzchak Rosenthal

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

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. . .

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)

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 clause Each 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".

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.

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

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

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

WHERE vs HAVING

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.

Example

Example: Table • The example on the following slides will use the following table: Table: Invoices Vendor. Id Payment. Total Invoice. Total 001 5 20 001 10 20 001 15 1500 002 10 10 002 20 4000 002 30 4000

Example of HAVING clause without a WHERE clause

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 vendor. Id, avg(Payment. Total) as avg. Payment. Total FROM invoices GROUP BY vendor. Id HAVING avg(Payment. Total) <=10 ORDER BY avg. Payment. Total

Processing the select without WHERE Step 1: Create the groups based on the GROUP BY Step 2: Generate the aggregate information (e. g. avg) for each group. Interim result set Table: Invoices Vendor. Id Payment. Total Invoice. Total 001 5 20 001 10 20 001 15 1500 002 10 10 002 20 4000 002 30 4000 group 1 group 2 avg. Payment. Total

Processing the select without WHERE Step 3: Remove records from the result set based on the HAVING clause Final Results Table: Invoices Vendor. Id Payment. Total Invoice. Total 001 5 20 001 10 20 001 15 1500 002 10 10 002 20 4000 002 30 4000 group 1 group 2 avg. Payment. Total

Adding a WHERE clause to the example

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 vendor. Id, avg(Payment. Total) as avg. Payment. Total FROM invoices WHERE invoice. Total < 1000 GROUP BY vendor. Id HAVING avg(Payment. Total) <=10 ORDER BY avg. Payment. Total

Processing the select with where Step 1: Process WHERE clause to eliminate some rows from consideration Tablen. Name: Invoices Vendor. Id Payment. Total Invoice. Total 001 5 20 001 10 20 001 15 1500 002 10 10 002 20 4000 002 30 4000

Processing the select with where Step 2: Process the GROUP BY to create groups from the remaining rows. Interim result set Tablen. Name: Invoices avg. Payment. Total Vendor. Id Payment. Total Invoice. Total 001 5 20 001 10 20 001 15 1500 002 10 10 002 20 4000 002 30 4000 group 1 group 2

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 Tablen. Name: Invoices avg. Payment. Total Vendor. Id Payment. Total Invoice. Total 001 5 20 001 10 20 001 15 1500 002 10 10 002 20 4000 002 30 4000 group 1 group 2