1. What you write is not what SQL sees
We've covered different query structures. Now let's see how Postgres reads your SQL instructions.
2. Algebraic order of operations
In SQL, as in algebra, there is a lexical order, the order as written. This differs from the logical order, the order as executed.
You may recall learning the algebraic order of operations with a mnemonic. American English uses PEMDAS, while British English uses BOMDAS. Both remind students not to work left to right. Instead, look first for parenthesis or brackets, second for exponents or order, third for multiplication and division, and fourth for addition and subtraction.
3. Applying the order of operations
Using the lexical ordering, 2 plus 8 equals 10. 10 adds to 4 equaling 14. 14 is divided by 2 for an answer of 7.
Using the logical ordering, the first operation is within the parenthesis or brackets, the 8 plus 4 equaling 12. Division follows with 12 divided by 2 equaling 6. Finally, 2 adds to 6 for a result of 8.
This lexical answer of 7 differs from the correct, logical answer of 8.
4. SQL logical order of operations
SQL also has an order of operations. This table shows some common clauses in the order they are performed the logical order.
The FROM clause provides directions to the table or tables if the query includes joins.
The WHERE clause filters or limits records.
GROUP BY places the records into categories.
Using these categories, SQL performs aggregations such as SUM() and COUNT().
SELECT identifies the data to return.
You can see that this ordering differs from a lexical execution of this SQL query which would start with SELECT.
5. Group by and aggregations
How many weather events involve wind? You want to see a count at both the storm and element level so write the SQL query shown.
Upon execution, your query returns an error that storm must appear in the GROUP BY clause.
6. Group by and aggregations order of operations
Understanding the SQL order of operations will explain the error.
The FROM clause returns all columns and rows existing in the weather_events table.
The GROUP BY clause categorizes all the records into the elements water or wind. In this step, you lose the other columns.
The COUNT() only sees the data at the grouped level, which is elements. There is no storm information.
Queries often require using both the COUNT() and GROUP BY clauses at the same level of detail.
7. Group by matches the aggregations
To resolve the error, the GROUP BY statement must match the granularity of the COUNT().
8. Group by matches the aggregations
Once the GROUP BY contains the same non-aggregated columns as the SELECT, the query can return the results of each element-storm combination.
9. SQL logical order of operations continued
Let's return to the SQL execution order by looking at some additional common SQL statements.
The instruction following the SELECT clause is DISTINCT.
The DISTINCT clause filters out duplicates.
ORDER BY sorts the results and LIMIT removes rows.
10. Distinct and limit
Now suppose you want to know how many elements each storm involves. You write a SQL query to show the top three unique records.
11. Distinct and limit
Understanding the SQL order of operations will explain what your query does.
12. Distinct and limit
The FROM clause returns all rows in the weather_events table.
13. Distinct and limit
The SELECT clause limits the columns to storm and elements while returning all the rows for these columns
14. Distinct and limit
The DISTINCT clause filters out the repeated storms and elements, removing table rows 4 and 6.
15. Distinct and limit
ORDER BY storm moves the record with a haboob above the tornadoes.
16. Distinct and limit
Finally, the LIMIT clause removes the last row to return only three records.
17. SQL logical order of operations
Here is a summary table of the common clauses, their purpose, and how they limit the results.
18. Let's practice!
Now it's your turn to test your knowledge about the SQL order of operations.