How Is an SQL Query Executed —The Inside Story

Imagine being able to ask a computer to find all the products in your online store that are on sale, add a new product to the inventory, update the price of an existing item, or even remove a product that’s no longer available—all with a few simple words.

This is exactly what SQL queries allow you to do.

In this blog, we’ll take a look at what an SQL query is and how it works, breaking down the process step-by-step so you can understand the complete execution lifecycle.

Before we get into the query discussion, let us first understand the background of SQL standards.

SQL Standards

SQL standards are developed by global organizations such as the International Organization for Standardization (ISO) and the American National Standards Institute (ANSI).

These standards are created with the help of industry professionals and database providers to ensure that SQL code can work across different database systems.

This means that the same SQL commands can be used on various databases without needing significant changes, making SQL a highly portable and versatile language.

History of SQL Standards

The development of SQL standards can be divided into several stages:

• SQL-86, SQL-89, and SQL-92: These early versions established the foundation of SQL, introducing fundamental keywords and concepts.

• SQL:1999: Marked a significant milestone, bringing in features like object-relational database management.

• Later Standards: Later standards have continued to enhance SQL, introducing new features and incorporating advanced technologies to tackle emerging data management challenges. Each new SQL version builds on its previous standards, contributing to the ongoing evolution and refinement of the language.

What Is an SQL Query

SQL stands for Structured Query Language. It is a standard language used to communicate with databases.

A SQL query is a command you give to a database to ask for specific information or to tell it to do something.

Think of it as a request or instruction you send to the database to get data, add data, change data, or delete data.

In simple terms, an SQL query is just a way to ask a database to do something for you, whether it's showing you some data, adding new data, changing existing data, or deleting data.

Basic SQL Commands

Let’s discuss the different types of SQL queries below:

• SELECT Queries:
  • These queries retrieve data from a database.
  • Example: SELECT * FROM users;
    • Gets all the data from the users table.
• INSERT Queries:
  • These queries add new data to a table.
  • Example: INSERT INTO users (name, age) VALUES ('John', 25);
    • Adds a new user named John who is 25 years old.
• UPDATE Queries:
  • These queries change existing data in a table.
  • Example: UPDATE users SET age = 26 WHERE name = 'John';
    • Changes John's age to 26.
• DELETE Queries:
  • These queries remove data from a table.
  • Example: DELETE FROM users WHERE name = 'John';
    • Removes the user named John.
• CREATE TABLE Queries:
  • These queries create new tables in the database.
  • Example: CREATE TABLE users (id INT AUTO_INCREMENT, name VARCHAR(100), age INT, PRIMARY KEY(id));
    • creates a new table called users.
• DROP TABLE Queries:
  • These queries delete tables from the database.
  • Example: DROP TABLE users;
    • Deletes the users table.

Learn everything about the NoSQL databases and its applications.

Example of SQL Syntax

SQL allows users to perform complex queries with simple commands. The SELECT statement, one of the most common SQL commands, is used to retrieve data from a database. Let's look at how it works.

Basic SQL Query Structure

Here is an example of a basic SQL query:

SELECT name, age FROM users WHERE age > 30;

• SELECT: Specifies the columns to retrieve.
• FROM: Indicates the table from which to retrieve the data.
• WHERE: Adds conditions to filter the results.

1. Joining Tables

SQL can also join data from multiple tables.

The JOIN clause is used to combine rows from two or more tables based on a related column between them.

For example:

SELECT users.name, orders.amount FROM users JOIN orders ON users.id = orders.user_id WHERE orders.amount > 50;

• JOIN: Combines rows from the users and orders tables where there is a matching user_id.
• ON: Specifies the condition for the join.
• WHERE: Filters the results to only include orders with an amount greater than 50.

2. Grouping and Sorting Data

SQL also allows for grouping and sorting data.

The GROUP BY clause is used to group rows that have the same values in specified columns, often used with aggregate functions like COUNT, SUM, AVG, etc.

The ORDER BY clause is used to sort the results.

SELECT department, COUNT() FROM employees GROUP BY department ORDER BY COUNT() DESC;

• GROUP BY: Groups employees by their department.
• COUNT(*): Counts the number of employees in each department.
• ORDER BY: Sorts the results by the count in descending order.

These examples show how SQL syntax can be used to retrieve, combine, filter, group, and sort data, making it a powerful tool for managing and querying databases.

Step-by-Step Process of SQL Query Execution

Every time we run a query, there's a lot happening behind the scenes. A series of steps take our commands and turn them into actions that the database management system (DBMS) can perform.

Understanding these steps is key to making the most out of our database interactions.

For developers, it's important to know how an SQL statement works from start to end.

This process involves several stages: the SQL parser, which checks the query; the query optimizer, which finds the best way to execute it; the execution engine, which carries out the query; and the storage engine, which manages how data is stored and retrieved.

1. You Write a Query: You start by writing a SQL command, like SELECT * FROM users WHERE age > 30;. This command asks the database to get all users who are older than 30.

2. Parsing: The database system first checks your query for any syntax errors. This is like proofreading to make sure everything is written correctly.

3. Planning: Next, the database system figures out the best way to get the data you asked for. This step is called query optimization. The database looks at different ways it could run your query and chooses the most efficient one.

4. Execution: Now, the database system starts to carry out your query. Here’s what happens:

   • Fetching Data: The database looks in its storage for the data that matches your query. It might use indexes (special data structures that speed up data retrieval) to find the data faster.

   • Filtering: If you have conditions in your query, like WHERE age > 30, the database filters out the data that doesn’t match these conditions.

   • Joining Tables: If your query involves multiple tables, the database joins them together based on the specified conditions.

5. Returning Results: Once the database has fetched and processed the data, it sends the results back to you. This might be a list of users who are older than 30, as you asked for in your query.

Detailed Example

Let’s use an example to make it clearer.

Imagine you have a table called users with columns for name and age.

You run this query:

SELECT name FROM users WHERE age > 30;

The step-wise process will look like:

1. You Write the Query: SELECT name FROM users WHERE age > 30;.

2. Parsing: The database checks that you wrote the query correctly.

3. Planning: The database thinks about the best way to find the names of users older than 30.

4. Execution:

   • Fetching Data: The database looks in the users table.

   • Filtering: It filters out users who are 30 or younger.

   • Selecting Columns: It picks the name column from the remaining rows.

5. Returning Results: The database sends you a list of names of users who are older than 30.

Understanding the Two Main Parts of the Database System

In order to understand the SQL query execution clearly, it is important to cover the database side as well.

A database system is divided into two major components: the Server Layer and the Storage Engine Layer.

These two layers work together to process and manage your data efficiently. Understanding the roles of each layer helps in grasping how databases function and how they execute your SQL queries.

1. Server Layer:

   • Connectors: These are like bridges that let different applications talk to the database. They help your application send commands to and receive data from the database.

   • Query Cache: This is a temporary storage that holds the results of recent queries. If the same query is run again, the database can quickly return the result from the cache instead of running the query again, saving time and resources.

   • Parser and Optimizer: The parser checks your SQL query for errors and translates it into a format the database can understand. The optimizer then looks at different ways to execute the query and chooses the most efficient one.

   • Execution Engine: This is where the query is actually carried out. The execution engine follows the plan created by the optimizer to fetch or modify the data.

2. Storage Engine Layer:

   • Data Storage: This is where your data is physically stored. It could be on hard drives, SSDs, or other storage media.

   • Storage Engines: These are specific methods for storing and retrieving data. MySQL, for example, supports different storage engines like InnoDB and MyISAM. Each engine has its own strengths and is suited to different types of tasks. For instance, InnoDB is great for transactions and maintaining data integrity, while MyISAM is faster for read-heavy operations.

This knowledge can improve your ability to write efficient queries and optimize database performance.

Learn about the useful data replication strategies.

Why Use SQL?

SQL is used because it allows you to manage and interact with data in a very organized and efficient way. Here’s why SQL is important:

• Easy to Learn and Use: The commands in SQL are written in simple English, making them easier to understand and use.

• Powerful and Flexible: SQL can handle large amounts of data and complex queries.

• Standardized: SQL follows a standardized format, so the same commands can be used across different database systems like MySQL, PostgreSQL, and Oracle.

• Efficient Data Management: SQL allows for efficient data storage, retrieval, and manipulation, making it essential for applications that rely on large databases. Understand the horizontal scaling and the challenges of scaling the SQL databases.

Bottomline

Understanding SQL and how queries are executed can significantly improve your ability to work with databases.

From writing basic commands to understanding how these commands are handled internally, this blog gives you a clearer picture of how it all works. With this knowledge, you can write more efficient queries, manage your data better, and troubleshoot issues more effectively.