A Guide to Subqueries in MySQL

Subqueries are a part of the life of every DBA. Everyone knows that subqueries are just that – they’re queries within queries, but they’re actually so much more than that. Intrigued? This blog will walk you through everything you need to know about them – have a read!

Subqueries are just what they seem like – they’re queries within queries. In other words, they‘re nested queries – one query is called the inner query, while another query (the one inside the inner query) is called an outer query. Here‘s how subqueries look like in the majority of the cases:

A subquery in DbVisualizer

In other words, a subquery is a SELECT statement within a SELECT statement – in most cases, subqueries are used to select data from another table and they begin with the IN clause as well. Here’s the syntax for most subqueries (here x is the column where we run search queries on):

SELECT a, b FROM table WHERE x IN (SELECT c, d FROM table2 WHERE y = ‘Demo’);

It’s pretty similar to the query shown above – subqueries are useful when we want to search for data in a more complex manner than usually offered by our database management system of choice – queries using subqueries usually:

Determine what column is the most important (WHERE x IN) to search for.
Run another query on that column (SELECT ... WHERE.)
And finally, as results of subqueries, databases are able to build more “exotic” results (results that solve more difficult problems than those derived from ordinary queries.)

Nuances of Subqueries

As with everything database-related, subqueries do have nuances unique to themselves. Some of such nuances include, but are not limited to:

• The ability to use mathematical operators like =, !=, >, <, <=, <=, <>, and <=>
• The ability to use LIKE queries and wildcards within subqueries.
• The ability to use constructs like MIN, MAX, and COUNT(*) within subqueries (do note that if MyISAM isn’t in use, COUNT(*) queries will likely be slow because other database storage engines don’t store the count of rows within their metadata.)
• The ability to use row subqueries like so:

  

  Row subqueries within DbVisualizer

  The query above has to always return one row – if a row-based query returns more than one row, our database management systems will start to error out.

The following query will error out as well:

A problematic subquery

See the problem? We select one row, then select everything from a specific column using a subquery. Such an approach isn’t good and our database management systems won’t like it very much – they will error out as well. Not good!

However, if we want to send a request like “find all rows in a table demo that overlap with the rows in a table demo2”, we can use subqueries as well – for such use cases, they’re a very powerful thing. See example below:

Subqueries within subqueries in DbVisualizer

When to Use Subqueries?

By now you should have gained an understanding about subqueries – these are nested queries within queries that help build different results than expected. But when exactly should we use subqueries? This question remains unanswered – and it’s a very important question indeed.

However, not all complex questions require complex answers – subqueries should be used whenever we need to select data based on a WHERE query that selects data from a different table or a database. The same works in all other cases – should we want to insert, update, or delete data based on a WHERE query, we can make use of subqueries as well.

Also, subqueries can be nested within many different queries like INSERTs, UPDATEs, INSERTs, DELETEs and even [NOT] IN or WHERE EXISTS operators to figure out if a column or a row exists too – they’re not only useful for SELECTs, so keep that in mind!

Going Beyond Subqueries

Subqueries are an important tool in the arsenal of pretty much any DBA you can imagine, however, they’re far from the only tool we can employ to take our database performance, availability, capacity, and security to the next level. Everyone’s able to take care of their database performance at the basic level – going beyond that, however, requires actual skill. Don’t worry though – companies have that skill and can offer it as part of their SaaS solutions. One of such companies is DbVisualizer – built by some of the best database experts in Sweden, DbVisualizer is the SQL client with the highest user satisfaction in the world.

DbVisualizer offers support for virtually any database management system imaginable – from MySQL, Postgres, MongoDB, and Oracle to SQLite and Cassandra. Even better, it comes with a free evaluation period letting you evaluate the tool and decide whether it’s a fit for your company yourself. Grab a free evaluation trial here, then explore all of the features: from a visual query builder to tools letting you explore and visualize your data at a high level, DbVisualizer has it all. No wonder why companies like Twitter, AMD, Apple, eBay, Visa, Volkswagen, and even NASA elect to use the tool. Will your company be one of them?

Summary

In this blog, we’ve gone through the implications, upsides, and downsides of using subqueries in database management systems. We’ve told you what they are, how they work, and how best to exploit their best sides while keeping the bad ones at bay. We hope you’ve enjoyed reading this blog and that you’ll stay around our blog to learn more about databases in the future, and until next time!

FAQs Related to Subqueries

What are Subqueries?

Subqueries are just queries within queries – subqueries or nested queries – they the same thing.

When Should I Use Subqueries?

Consider using subqueries whenever you feel the need to select data and perform multiple operations on it at the same time. In other words, use subqueries whenever the result that you wish to achieve requires you to use a subset of more than one table.

How Do I Use Subqueries?

The usage of subqueries is pretty simple – a query like so will do (here a, b, x, c, d, and y are columns):

SELECT a, b FROM table
WHERE x IN (SELECT c, d FROM table2 WHERE y = ‘Demo’);

The query above is perhaps one of the most frequent ways to make use of subqueries within any DBMS.

I’m Already Using Subqueries – Is There Anything Else I Should Know?

Subqueries should only be one tool within your arsenal – before considering using subqueries, consider exploring partitioning, the types of partitions available within MySQL or other flavors of database management systems if you use those, and explore how your databases interact with one another by modifying the parameters specific to them in postgresql.conf (PostgreSQL), my.cnf (MySQL), ConfigurationFile.ini (Microsoft SQL Server) or other files specific to the database management system of your choice as well.

About the author

Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.