The basic of the advanced SQL queries: Tuning
Tags: sql, database, community, performance
Published: 2022-12-30 00:03:00

In this article I wanna show you things about SQL that I believe will make a difference in your queries.

First of all, how does SQL work? This point can be obvious for some one, but I think that could be mentioned here. Keep in mind, a query SQL works in order: **FROM, WHERE, SELECT.**

Look the table ***teachers***


| id_teacher | id_school | situation |
| --- | --- | --- |
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 1 |
| 4 | 1 | 0 |

For example, in a query: 

```sql
SELECT teachers.* 
FROM teachers 
WHERE teachers.id_school = 1
```

The database will search all teachers, then search which one has a column ***id_school*** equals ***1***, so after this put in columns from your select, in this case, all columns of table ***teachers***.

Output:

| id_teacher | id_school | situation |
| --- | --- | --- |
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 4 | 1 | 0 |

Well, this is pretty simple, you know? Let's make this more interesting… Think about, your employer tells you to make a query that returns all teachers who are actively working in some school.

Let's consider the table above to the ***teachers*** and the following table to the ***school***:

| id_school | situation |
| --- | --- | --- |
| 1 | 1 |
| 2 | 1 |
| 3 | 0 |

The first thing that comes to mind is something like this: 

```sql
SELECT teachers.* 
FROM teachers 
JOIN school ON (school.id_school = teachers.id_school) 
WHERE teachers.situation = 1
```

Output:

| id_teacher | id_school | situation |
| --- | --- | --- |
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 1 |

Well this works, but how do I make this better? We know that SQL queries work on clause FROM first, after this, it goes to JOIN clause. With this in mind, what is easier to the database, search all of the teachers and then look who have a school linked or search all of the schools and then look who are the teachers?

The logic tells us that the table **school** is less than the table **teachers**, so we can conclude that it is easier for the database to look for the school table. Like this: 

```sql
 SELECT teachers.* 
 FROM school 
 JOIN teachers ON (teachers.id_school = school.id_school)
 WHERE teachers.situation = 1
```

Output:

| id_teacher | id_school | situation |
| --- | --- | --- |
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 1 |

The same result, but with better performance. This is one of the most important lessons that I learned about SQL.

Okay, we make a little deep into tuning SQL queries, come one continue talking about. The second point that I want to talk about are subqueries. In most cases you **DON'T** need this!

Subqueries are a vampires of SQL query, they are common used when you want put values from other table per line, like when you want to put in line the sum values of items from a order with you **SELECT**.

Consider the follow tables to the ***order*** and ***order_itens***:

Order:

| id | costumer |
| --- | --- |
| 1 | 1 |
| 2 | 1 |

Order Itens:

| id | id_order | value |
| --- | --- | --- |
| 1 | 1 | 93.3 |
| 2 | 1 | 12.99 |
| 3 | 2 | 10.19 |
| 4 | 2 | 12.5 |
| 5 | 2 | 79.99 |
| 6 | 3 | 99.13 |

For example:

```sql
SELECT order.*, 
      (SELECT SUM(order_itens.vl_item) 
       FROM order_itens 
       WHERE order_itens.id_order = order.id) AS vl_total_order 
FROM order
```

Output:

                                       
| id | costumer | vl_total_order |
| --- | --- | --- |
| 1 | 1 | 106.29 |
| 2 | 1 | 102.68 |

This query returns what we want, all of the orders and the value of all items from the order. Okay, but how do I make this better?

The first thing that I suggest you do is detach this query, if this is possible, is the better option in my opinion. But if not, in SQL you have another option to do this exemple, we can make the query above with the **WITH** function.

From now i don't will talk more about this function, but maybe I can make another document just to talk about this. In fact if you don't know how to make a WITH correct, they can be more hindrance than a help, so be careful.

Ok, but sometimes we can run away from subqueries, for example you have to check if the line result exist on another table. Something like: 

```sql
SELECT order_itens.* 
FROM order_itens 
WHERE order_itens.id_order IN (SELECT order.id 
                               FROM order)
```

Output:
                  
| id | id_order | value |
| --- | --- | --- |
| 1 | 1 | 93.3 |
| 2 | 1 | 12.99 |
| 3 | 2 | 10.19 |
| 4 | 2 | 12.5 |
| 5 | 2 | 79.99 |

Of course, the better way that we can do this, is with JOIN. But if you table don't have a PK and sequences in well defined, or something else, maybe with this will get more performance: 

```sql
SELECT order_itens.* 
FROM order_itens 
WHERE EXISTS (SELECT 1 
              FROM order 
              WHERE order.id = order_itens.id)
```

Think about this, in this example we only need to know if line exists in another table, so just take the first one you find and done! This it may seem a small thing, but when we talk about **tuning**, we have a lot of small things that make a difference together.

---

That’s it! Thank you for reading, I will hopefully than this tips help you in your journey as a developer SQL. See you later!