BYOMSPM

I mastered the basics (emphasis on basics) of SQL a few years ago in school, and in the time it’s taken for it to come up in my job I’ve completely forgotten it all. Below is my attempt to piece back together remnants of a former understanding.

Order of Terms

SELECT columns
FROM table
WHERE filter condition
GROUP BY group criteria
HAVING filter condition for groups
ORDER BY order

But also… the Actual Order of Execution

FROM table
WHERE filter condition
GROUP BY group criteria
HAVING filter condition for groups
SELECT columns
ORDER BY order

Main takeaway: SQL pares down rows first (WHERE, GROUP BY, HAVING) and columns second (SELECT), so SELECT happens much later than it’s written. Good to keep that in the back of your head. Though I discovered that things written within the select clause are executed early, so it seems that it’s just the actual column selection that happens later.

Note: order of execution can vary depending on what you’re using.

Useful Add-Ons

• SELECT columns AS nickname – can then use the nickname later in the query
  • Instead of just selecting a column, you can also select a function or combination of columns
  • i.e. SELECT column1 + ‘ ‘ + column2 AS Name
  • i.e. SELECT COUNT(OrderNumber) AS TotalOrders

• WHERE column LIKE ‘G%” – a condition that selects only values that begin with G
  • LIKE ‘%g%’ = contains g with any number of character before or after
  • LIKE ‘_g_’ = contains g with one character before and one character after
  • LIKE ‘[a, b, c]%’ = starts with a, b, or c
  • LIKE ‘^[a, b, c]%’ = starts with anything besides a, b, or c

• WHERE NOT condition – selects anything without that condition
  • i.e. SELECT column FROM table WHERE NOT column = ‘Red’;

• SELECT TOP num columns FROM table
  • This function specifically varies completely between different variations of the SQL language

Other Useful Functions

• MIN()
• MAX()
• COUNT()
• SUM()
• AVG()
• IN()
• BETWEEN num AND num

Best of luck with that!

Thank you to W3Schools SQL Tutorial for making this post possible.