sql select statement prof. yitzchak rosenthal. syntax for select statement clauses must be written...
TRANSCRIPT
![Page 1: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/1.jpg)
SQL Select Statement
Prof. Yitzchak Rosenthal
![Page 2: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/2.jpg)
Syntax for SELECT statement
• Clauses must be written in the following order– SELECT– FROM– WHERE– GROUP BY– HAVING– ORDER BY
![Page 3: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/3.jpg)
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 ...
![Page 4: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/4.jpg)
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)
![Page 5: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/5.jpg)
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".
![Page 6: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/6.jpg)
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.
![Page 7: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/7.jpg)
Step 4: HAVING
• Step 4: HAVING– Filter out all groups that don't match the conditions in
the HAVING clause
![Page 8: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/8.jpg)
Step 5: SELECT
• Step 5: SELECT– Figure out what values will actually be included in the
final result set by processing the SELECT clause
![Page 9: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/9.jpg)
Step 6: ORDER BY
• Step 6: ORDER BY– Sort the result set in the order specified in the ORDER
BY clause
![Page 10: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/10.jpg)
WHERE vs HAVING
![Page 11: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/11.jpg)
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.
![Page 12: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/12.jpg)
Example
![Page 13: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/13.jpg)
Example: Table
• The example on the following slides will use the following table:
Table: Invoices
VendorId PaymentTotal InvoiceTotal
001 5 20
001 10 20
001 15 1500
002 10 10
002 20 4000
002 30 4000
![Page 14: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/14.jpg)
Example of HAVING clause without a WHERE clause
![Page 15: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/15.jpg)
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) <=10ORDER BY avgPaymentTotal
![Page 16: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/16.jpg)
Processing the select without WHERE
Table: Invoices
VendorId PaymentTotal InvoiceTotal
001 5 20
001 10 20
001 15 1500
002 10 10
002 20 4000
002 30 4000
avgPaymentTotal
group2
Step 1: Create the groups based on the GROUP BYStep 2: Generate the aggregate information (e.g. avg) for each group.
group1
Interim result set
![Page 17: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/17.jpg)
Processing the select without WHERE
Table: Invoices
VendorId PaymentTotal InvoiceTotal
001 5 20
001 10 20
001 15 1500
002 10 10
002 20 4000
002 30 4000
avgPaymentTotal
group1
group2
Step 3: Remove records from the result set based on the HAVING clause
Final Results
![Page 18: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/18.jpg)
Adding a WHERE clause to the example
![Page 19: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/19.jpg)
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 < 1000GROUP BY vendorIdHAVING avg(PaymentTotal) <=10ORDER BY avgPaymentTotal
![Page 20: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/20.jpg)
Processing the select with where
TablenName: Invoices
VendorId PaymentTotal InvoiceTotal
001 5 20
001 10 20
001 15 1500
002 10 10
002 20 4000
002 30 4000
Step 1: Process WHERE clause to eliminate some rows from consideration
![Page 21: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/21.jpg)
Processing the select with where
TablenName: Invoices
VendorId PaymentTotal InvoiceTotal
001 5 20
001 10 20
001 15 1500
002 10 10
002 20 4000
002 30 4000
Step 2: Process the GROUP BY to create groups from the remaining rows.
avgPaymentTotal
group1
group2
Interim result set
![Page 22: SQL Select Statement Prof. Yitzchak Rosenthal. Syntax for SELECT statement Clauses must be written in the following order –SELECT –FROM –WHERE –GROUP](https://reader031.vdocuments.us/reader031/viewer/2022013122/56649d345503460f94a0b282/html5/thumbnails/22.jpg)
Processing the select with where
TablenName: Invoices
VendorId PaymentTotal InvoiceTotal
001 5 20
001 10 20
001 15 1500
002 10 10
002 20 4000
002 30 4000
Step 3: Process the HAVING clause to possibly remove some rows from the result set (in this example no rows need to be removed)
avgPaymentTotal
group1
group2
Final results