Step by step: How RDBMS executes SQL statement
Tags: database, sql, programming
Published: 2022-10-10 23:25:21

## Motivation

SQL and RDBMS are most used tools in software industry, and most of performance issues are related of bad storing or bad retrieving of the data, knowing how database stores and retrieves the data can make you avoid a lot of performance issues, we will talk here about how RDBMS executes SQL statement.

## Before SQL execution

DBMS do some several steps before executing the SQL statement here are:

1. Parsing the SQL statement and break it into keywords/tokens.
2. Validating the SQL statement syntax (all keywords valid and in right order).
3. Validating the SQL statement against schema (is tables and columns used in SQL exists).
4. Generating execution plan (regarding table properties and statistics DBMS decides what is best way to execute SQL, using the index or jumping to the table directly and so on).
5. Executing the execution plan.

## How RDBMS execute SQL statement

let’s say below is our data set **Movies** table with **Box-office-rating** table

**and we want to display the movies with its rating in Box-Office**


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/u8o2ipzqenreshpr9reo.png)


So, this will be our query


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kto0keve43c99wkqwtzg.png)

First of all, SQL clauses can be described as a **pipes** each pipe is processing the data with its own logic then transfer the result to the next clause (pipe) for processing.

First pipe type is **FROM** clause which is get the data set which we should work on. We can say that **FROM** clause is equivalent to creating a stream from list of values in Java.

**FROM** clause prepare the data set to work on, and this is the result.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bhmpl5l4yqwdboedo999.png)

Then **FROM** pipe transfer the data to **JOIN** pipe which do **cartesian product** between the main table data and join table data.

So, result till now something like this

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2vuff0erof1mfjg3rcqd.png)

And depending on **JOIN** type (INNER/LEFT/RIGHT) and **ON** clause the result of multiplication formed, as in our example we use **INNER JOIN** with this on **m.id = bo.movie_id** the pipe will filter the data and the result as below.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2zpg8q2u72kigj7vl2ks.png)


In case a lot of joins each join will work as separate pipe and do it’s own logic and transfer the result set to next one.

> Note: you can group joins by parenthesis '()' and force ordering as parenthesis calculated first.

Let’s continue, the next pipe is **WHERE** clause which work for filtering the data based on the condition and move the result to next one, so after executing **WHERE** clause this will be the result.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eh24iejwzh0gus3waos9.png)

The next pipe should be **GROUP BY** and Then **HAVING** clauses but we not have these clauses in our example so will be skipped.

Then next pipe is **SELECT** clause which here grep the columns we needed and calculate aggregate functions and so on, it works same as **map()** method of stream in Java, in our example we use ‘*’ to get all columns.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7izycfbj6d3rb2j298jm.png)

Then, **DISTINCT** clause is the next to remove duplicates based on column values, but we didn’t specify this clause here so it will be skipped.

Then, **ORDER BY** pipe works for ordering the result set as per the column and direction and result set became as below.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dtfrycew5zl8dw3tif6a.png)

The last pipe is **LIMIT** pipe which limits the row count fetching as below.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kotkfe6c131z28k4f503.png)

## SQL joins


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ghzp506yfhgqmp0oysni.png)

## References

- [Processing a SQL statement](https://learn.microsoft.com/en-us/sql/odbc/reference/processing-a-sql-statement?view=sql-server-ver15)

- [SQL statement order of execution](https://sqlbolt.com/lesson/select_queries_order_of_execution)

- [What’s the Difference Between INNER JOIN, the OUTER JOINs, and CROSS JOIN?](https://www.sentryone.com/blog/difference-between-inner-join-outer-joins-cross-join)

IF YOU LIKED THE POST, THEN YOU CAN BUY ME A COFFEE, THANKS IN ADVANCE.

<a href="https://www.buymeacoffee.com/samehmuh95" target="_blank"><img src="https://cdn.buymeacoffee.com/buttons/default-orange.png" alt="Buy Me A Coffee" height="41" width="174"></a>