Let’s know more about Advance SQL Techniques

Introduction to Advance SQL Techniques that mostly used in everyday work, like subquery, windows function, and aggregate functions

Jonathan Shinray Fang | LinkedIn

Hello, I’m Jonathan a mere data enthusiast, I’m learning while writing article that I find interesting in medium, and this is the let’s know more about series.

image source

Introduction

All right, so most developers know what a query is, but did you realize that we can really make it better? Knowing how to enhance query efficiency can distinguish you as an outstanding developer among your others. Similarly, in martial arts or woodcutting, mastery extends beyond mere training and strength. Without advancing beyond basic techniques, one cannot achieve the proficiency required for more complex tasks. So, what are you waiting for? Let’s dive in!

What are Advance SQL Techniques?

Advance SQL Techniques refer to more complex technique that utilize query and manipulating data more efficient. In this article, I’ll explain three most used advance techniques, one by one with example to explore much further.

1. Subqueries

Just imagine a box in a larger box, then you pick it up to another place maybe to the car or somewhere else. Yes, the box represents a query, so a box in a larger box represents a query in a query, that’s what we call a subquery. Upon running, these bad boys will allow you to perform multiple steps in a single query just like you pick the box while there’s many things in it.

In this example I want to use a database called toy_box, a box that have many toys in it, I want to get toy from category that released after 2015.

Database for Subquery Example

This is the query if I didn’t use subquery (I still using JOIN clause because how am I connect this without subquery or JOIN clause)

SELECT t.toy_name, t.category
FROM toys t
JOIN categories c ON t.category = c.category_name
WHERE YEAR(c.creation_date) > 2015;

Not using Subquery Query Result

Instead using JOIN clause where the query can be confusing, I’ll demonstrate another query using subquery.

SELECT toy_name, category
FROM toys
WHERE category IN (
    SELECT category_name
    FROM categories
    WHERE YEAR(creation_date) > 2015
);

Subquery Query Result

See the difference? No? Of course, because the difference is while we are querying, maintaining subquery will be much easier than basic query since we understand what this query does.

2. Aggregation Functions

Did you remember back in school, we used Microsoft Excel to do our analysis? Yeah, aggregation functions act like Excel’s formulas or maybe it’s because the Excel’s formulas are the one mimicking the aggregation functions. Aggregation functions can perform calculations based on set of values and return a single value. Commonly we used COUNT, SUM, AVG, MAX, and MIN.

In this example I want to make a simple report of department salary (like average salary and how many people in that department). We’re gonna use this fictious salary table.

Table for Aggregation Function Example

Instead of calculating it one by one, I’ll just use aggregation function query.

SELECT department, AVG(salary) AS 'salary', COUNT(DISTINCT(employee_name)) AS 'people'
FROM employees
GROUP BY department

Aggregation Functions Query Result

I believe this query technique can’t be compared to any basic query, since this should be compared to manual calculation or checking with query.

3. Window Functions

Did you realize we often see ranking between things, especially in social media, you know in SQL there is a technique that can group some output based on category. That technique called window functions. Like CTE known as WITH Query, window functions sometimes called as OVER Query because it uses OVER every time it used.

For this example, I’ll still using last employee table, I want to show average salary per department but still showing employee’s name.

SELECT 
  employee_name, 
  department, 
  AVG(salary) OVER (PARTITION BY department ORDER BY department) AS 'salary average'
FROM employees;

Window Function Query Result

So, it shows average salary per department and employee name.

And for the record, there also many more advance techniques like Common Table Expressions (CTE), Dynamic SQL, Store Procedure, Triggers, etc.

Conclusion

See the differences, it’s better using these techniques, right? I’ll assume it as a yes. There’s still so much more to learn. So, let’s normalize using advance techniques in our query! Stay tuned for my next article!