_edited_edited.png)
When you are first learning SQL, it can be like trying to find one's way through a maze, what comes after the other, GROUP BY or HAVING? OR what is the difference between WHERE and HAVING, and how do I know when to use what? The syntax seems so simple but ,the order of execution often confuses the newcomer.
But let's turn this confusion into delicious simplicity, such as cooking a meal. Let’s think of SQL as a cook following a recipe. Each query is one dish you are preparing, and the secret of perfection lies in following the steps in the correct order. Just as skipping or rearranging steps in a recipe can lead to a culinary disaster, misplacing SQL clauses can result in errors or unexpected results.
The good news? With this analogy, you'll be sure never to forget how SQL executes. Let's now dive and cook one up!
The SQL Execution Order: Cooking Step-by-Step
Let’s think of the process ,SQL query execution in terms of steps to make something to eat. Here's how it goes :
1.FROM is like gathering ingredients
You first decide on the ingredients you are going to use to make the food. No cooking starts before this! Just like you can't begin cooking without selecting your ingredients, SQL queries can't work without selecting a source table.
2.JOIN just as combining the ingredients
Now, as we may want to join lots of tables, this is where it gets combined - think of mixing together flour and eggs to combine into the batter for making a cake. Joining Tables, Allows you to get data from many tables and compile them into one result set.
3.WHERE is filtering of Ingredients
Before cooking, unused ingredients must be removed. This is like filtering rows to include only the relevant data. The WHERE clause is your filter to make sure you're working with data we need before proceeding.
4.GROUP BY is mixing and separating
Then we group the ingredients. Think of it as wet and dry ingredients which needs to separately mixed or the meat mixture being formed into balls to bake. GROUP BY takes rows and puts them into summarised data, ready for a taste test.
5.HAVING is taste test groups
After we have grouped, we sometimes want to double-check our groups or place criteria on them, such as discarding the meatballs that are too small. HAVING makes sure your groups meet the set criteria—it's like doing a final check on the batches before they go into the oven.
6.SELECT is plating the dish
This is how you determine what to serve. You are presenting your finished dish, revealing just what's important - the columns that you want your visitors to see.
7.DISTINCT is not to use duplicate ingredients
As part of the SELECT process, DISTINCT removes duplicates from your result. You wouldn't want to serve the same cupcake on a plate twice. DISTINCT ensures unique entries in your results.
8.ORDER BY presents the dish nicely
Presentation matters! Sorting your results is like garnishing and presenting your dish beautifully on the plate. ORDER BY allows you to order your results, by making the presentation perfect.
9.LIMIT is to serve the right portions
Finally, serving size-one slice of cake or three cupcakes. LIMIT enables you to control portion size, deciding how many results to display.
A SQL Recipe
Let's take our analogy into a real-world SQL query application. Suppose you work in a bakery and would like to know what top 3 dessert recipes are by total sales. Here is how you could "bake" this query:
Step-by-Step SQL Recipe
1. Ingredients - FROM: Extracting data from the recipes and sales tables.
FROM recipes
JOIN sales ON recipes.recipe_id = sales.recipe_id
2. Filter the Mixture - WHERE: Focuses on dessert recipes.
WHERE recipes.category = 'Dessert'
3. Mix It Up - GROUP BY: Grouping sales to get a total for each recipe by name.
GROUP BY recipes.recipe_id, recipes.name
4. Taste Test - HAVING: Ensures at least 10 units of that recipe were sold.
HAVING SUM(sales.quantity) >= 10
5. Select Dish - SELECT: Selects recipe name and total sales. If you need unique recipe names, then use DISTINCT.
SELECT DISTINCT recipes.name AS recipe_name, SUM(sales.quantity) AS total_sold
6. Arrange Nicely - ORDER BY: Recipes are arranged by total sales in descending order.
ORDER BY total_sold DESC
7. Control Portions - LIMIT: Displays only the top 3 recipes.
LIMIT 3
Final Recipe (SQL Query)
Let’s put all the steps together in the final recipe:
SELECT DISTINCT recipes.name AS recipe_name, SUM(sales.quantity) AS total_sold
FROM recipes
JOIN sales ON recipes.recipe_id = sales.recipe_id
WHERE recipes.category = 'Dessert'
GROUP BY recipes.recipe_id, recipes.name
HAVING SUM(sales.quantity) >= 10
ORDER BY total_sold DESC
LIMIT 3;
Understanding the recipe
While the analogy has simplified the process, it's essential to understand some finer points to master SQL execution:
WHERE vs. HAVING: One of the prevalent questions is when to use WHERE or HAVING. The important difference is that WHERE filters out rows before any aggregation however, HAVING filters groups after aggregation. For example, WHERE can be used for excluding rows with sales below 10 before grouping, whereas HAVING would filter groups, such as recipes whose total sales are below 10 after the aggregation.
The Power of JOINs: The JOIN clause deserves a bit more attention. The joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. The type of JOIN defines how tables will be combined. One thing important about an INNER JOIN is that it returns only the rows with matching data in both tables. The LEFT JOIN returns all rows from the left table, even when no match was found in the right one. The RIGHT JOIN returns all rows from the right table, even in cases where no match was found in the left one. The FULL JOIN returns all rows when there is a match in the left or right tables.Self join is also useful in cases where you need to compare rows within the same table and Unions are used to combine the results of two or more SELECT statements into one.
What's Next After Querying? Once you're comfortable with these basic queries, next would be advanced concepts such as:
Subqueries: It is a query inside another query. These are often used to filter data based on a result from another query. For example, you might use a subquery to identify which recipes have sales above the average for all desserts.
Window Functions: These functions enable calculations to be performed across a set of rows that are related to the current row without collapsing the result set. These are particularly useful for ranking data, such as which recipes sell best by region.
Performance Optimization: Learning how to optimize queries for speed, especially with large datasets, will make you an advanced user in SQL.
Conclusion
At first glance, SQL may appear intimidating. However, once the order of logical execution in each clause is understood, SQL becomes an easier entity to work. It is much like following a recipe. By following the right order, your query will have the desired result every time.
Next time you are writing a SQL query, intuitively, think of the execution order of SQL queries like cooking a meal, you don't want to serve an undercooked cake just because you mixed the dough way earlier than turning on the oven. Stick to the sequence, and voilà! your SQL queries are just like baking a perfect cake!
First, understand how the flow of data comes together to create the result. And just like with cooking, practice makes perfect!
Happy "cooking" with SQL!