While many aspects of life give us the freedom to choose the order in which we complete a task, with little to no effect on the outcome, some tasks demand us to complete its steps in a certain order. If you think otherwise, I dare you to bake a cake before breaking the eggs.
Just like in baking, SQL requires you to follow a set order when writing a query. However, this order can often be difficult to remember, hence, I try to recall it using my handy mnemonic:
- SMELLY - Select
- FEET - From
- WILL - Where
- GIVE - Group by
- HORRID - Having
- ODOURS - Order by
But what does each function actually do? Lets have a quick look:
SELECT (SMELLY)
The SELECT statement specifies which columns, fields, or expressions should be returned in our output.
Example:
SELECT customer_name
,state
FROM customers
This query would return the state that each customer lives in from the customers table.
FROM (FEET)
FROM identifies the tables that we want to return values or columns from in out output.
You can see in the SELECT example that our customer information was taken 'from' the customers table.
WHERE (WILL)
WHERE can be thought of as row level filters, whereby, SQL will filter out any row that doesn't meet the WHERE condition. This step can reduce the number of rows before any grouping or aggregation
Example:
SELECT order_id
,sales
FROM orders
WHERE sales > 100
This query will only return a list of orders that are larger than £100.
GROUP BY (GIVE)
GROUP BY is used to group rows based on specified columns (fields). Allowing you to later 'restart' your aggregations at different grouped values.
Example:
SELECT state
,SUM(sales)
FROM orders
GROUP BY state
This will give us the total sales for each state we sell in. In this sense, total sales is 'restarting' which each state name.
HAVING (HORRID)
After creating groups or aggregations, HAVING filters allow you to filter out groups or columns based on aggregate values. It works very similarly to WHERE, but only with grouped, aggregated data.
Example:
SELECT state
,SUM(sales)
FROM orders
GROUP BY state
HAVING SUM(sales) >10,000
Following from our previous example, this will only return the states which have total sales greater than 10,000.
ORDER BY (ODOURS)
ORDER BY is a fairly self-explanatory clause, in that it allows you to sort or order how your data is presented . With 'ASC' for ascending and 'DESC' for descending.
Example:
SELECT state
,SUM(sales)
FROM orders
GROUP BY state
ORDER BY SUM(sales) DESC
This query will give us the state with the highest total sales at the top and the state with the lowest at the bottom.