SQL queries… You’ve heard of them — who hasn’t? It’s no exaggeration to say that you’ve probably recently executed a couple of them, too! SQL queries help you create, modify (alter), or delete data, and they’re a paramount part of your database strategy. One of the things that you will come across when working with them is that they’re rather complex and have a lot of things associated with them. One of those things is subqueries. Let’s explore queries and subqueries in SQL!
Queries in SQL Explained
To fully understand how queries help your database achieve its goals, think of them as tasks. Queries are tasks that you’ve given to your database and to complete those tasks, your database has to sift through them, and, eventually, execute them.
Queries are composed of multiple subtasks — for a task (your query) to complete, your database has to work on and fully execute subtasks given to it by your database. Those subtasks may differ from database to database, but in general, they’re all part of the same “query map” — to observe that query map, we have to profile our queries and see what they’re doing internally.
After we walk ourselves through ways to execute SQL queries using the SQL client built by DbVisualizer, we’ll find that each database implements profiling differently. For example, MariaDB approaches profiling using a three-step process:
First, we need to turn on the query profiler (by default, it is off) and then run the necessary SQL queries for our use case.
For this instance, we use a bunch of dummy data consisting of codes, country codes, fake usernames, genders, IP addresses, and other information:
After that’s done, we can observe the results using SHOW PROFILES (the duration is shown as “6.609E-4” because the number is too small for DbVisualizer to display — the query was executed internally):
Once we identify our query in the list, we can issue a query like SHOW PROFILE FOR QUERY [id] to see what our database was doing internally when executing it. That query will return a bunch of steps that were necessary for the database to complete before our query has reached the finish line:
DbVisualizer is only displaying 12 initial steps — to see the rest of them (there are over 20), we’ll have to scroll down, so we’ll outline them for you here instead:
No matter what kind of query is executed within your database, your database has to go through a multitude of steps outlined above. These are the subtasks that your task (query) consists of — and to finish (complete) a query, your database has to complete those subtasks. Once they’re finished, your database will return results.
Why Are Queries Slow?
TL;DR: queries are slow because the tasks concerning them are completed slowly — to optimize their performance, make those tasks execute faster or remove them altogether so they’re not executed at all.
When completing any kind of queries, your database has to complete a multitude of subtasks it’s concerned with and this is where we get into another question plaguing developers and DBAs for ages — “My SQL queries are slow. Why?” Well, that’s because the subtasks concerned with them are slow. You probably don’t notice your queries slowing down when they scan through 20 records, only when they scan through some more, right? There’s a logical explanation for that happening and that’s because your database has to work with more data than before!
If you ask for data that’s not necessary (e.g. use SELECT * instead of SELECT column), your queries will be slower because they need to scan through more data. The same goes when your database has to work with more data than before — if you execute 500,000 INSERT queries in a SQL file, each of them will have to complete before your database gives the “OK” sign for you to proceed.
There’s much more to be said about query performance so if you’re interested in that sort of thing we recommend you read “Hacking MySQL: Breaking, Optimizing, and Securing MySQL for Your Use Case”, and now, let’s look into subqueries.
How to Work with SQL Subqueries?
SQL subqueries are just what they sound like — they’re SQL queries with SQL queries inside of them. Subqueries are nested inside of ( and ) characters because they don’t define a column — they often select data from another table or database instead.
A basic example of subqueries would be as follows:
1
SELECT country, username, (SELECT car_brand FROM car_data WHERE car_data.id = demo_data.id) AS car_brand
2
FROM demo_data;
Look closely — we select a country and a username and then nest another query inside of our present query where we not only display the country the user is from but also select the car brand driven by the person. That’s the power of subqueries in SQL!
So, subqueries are pretty easy to grasp — subqueries in SQL refer to queries within our queries and they’re usually nested within ( and ) characters because they don’t define a column, but rather define a separate SQL query that gets executed by our database.
So, queries and subqueries in SQL have tasks to accomplish within themselves — that’s why queries executing subqueries may also sometimes be slow. To optimize query performance, consider using partitioning, indexing, and other practices outlined in the DbVisualizer blog.
Summary
SQL queries and subqueries are interesting creatures — all SQL queries are tasks composed of subtasks your database needs to complete, and as such, go through various phases when they’re executing. SQL subqueries are the same as SQL queries, it’s just that they’re nested inside of them.
We hope that this blog has deepened your understanding about queries and subqueries in SQL, and until next time!
FAQ
What are SQL queries?
SQL queries are queries — tasks — outlined by your application and forwarded to your database. They’re executed after your database goes through all of the steps to execute them.
What are SQL subqueries?
SQL subqueries are queries that are nested within other SQL queries. They do impact query execution time as a whole.
What is the difference between queries and subqueries in SQL?
SQL queries are pieces of tasks that you entrust your database to execute. Subqueries are SQL queries wrapped in other SQL queries.
Why are SQL queries slow?
Your SQL queries are slow because the internal tasks they have to complete finish slowly. To make them execute faster, optimize the performance of those tasks or remove them altogether.
Where can I learn more about queries and subqueries in SQL?
To learn more about queries and subqueries in SQL amongst other things, attend conferences and gatherings, read blogs written by reputable vendors, and also read books on the subject. We’d recommend Hacking MySQL by Lukas Vileikis — for those wanting some quick and applicable advice on the MySQL front, it should be the book to go.
