At some point after learning the basics of SQL, you’ll encounter some more complex SQL queries.
These could be at job interviews, project work at university, or while at work.
Complex queries can be hard to understand. They are more
than just a Select query with a few columns and a WHERE clause.
Understanding complex queries is one of the most common questions I get,
so I created this video for you.
In this video, you’ll see an example of a complex query and watch the steps I take to understand it.
You can use this to understand the kind of steps
you might take to understand your own complex queries.
Welcome to the Database Star YouTube channel - the place for developers
looking to improve their database and SQL skills.
Here’s the query we’re going to aim to understand today.
It’s based on a question on the Hacker Rank website.
I’ve also opened this in MySQL Workbench, which is an editor for the MySQL database.
You can follow along with this in any SQL editor and database if you prefer.
Also, for the purposes of this video, I’ve created the tables and some sample data.
I’ll add links to these in the description below.
What do you notice when looking at this query?
It’s quite wide. It takes up almost the full width of the screen on my window.
It’s all written in lower case, which is OK,
because the editor will highlight the tables and keywords in other colours.
There are also a couple of different types of joins.
And for these two lines, we are joining to a SELECT keyword. I’m
not sure what that’s about, but we will find out.
So, where do we start?
The first thing I’ll do is clean up the formatting.
This will make it easier to see the size of the query and how much is in each clause of the query.
By this, I mean we can see how many columns are being selected,
how many tables are being selected from, what the subqueries look like, and so on.
Many SQL editors include a feature to format your SQL code.
In MySQL Workbench, you can do that by clicking on this Beautify button here.
When I click this, the query is dramatically updated. We can see it’s more spread out,
things are on new lines, and we can get a better idea of what’s in the query.
Let’s say that our editor does not have this format
feature. Or we are viewing this in a text file.
We can manually format our query instead. It doesn’t have to be a lot of effort.
You can just add some new lines here and there to make it easier to understand.
I’ll do this now by adding each selected column to a new line.
I can see after the Left Join, there is a bracket and a select.
This is a subquery, so I’ll add these on separate lines and indent them.
You can use whatever convention you prefer. The aim is to make it easier for you to understand.
After a few updates to the format, this is what it could look like.
It doesn’t need to be perfect, but it’s a lot easier than the original query to understand.
What can we do next?
I like to start with understanding the tables that are in the query.
The place to look for these is the From clause.
In the From clause here, we can see that data is selected from a table called contests.
The letter “c” after it is called a table alias, and it means any columns from this
table can have the letter “c” as a prefix to make it easier to refer to.
We also select from the colleges table, and the challenges table,
each of which have a different alias.
We have another left join here, but we’ll come back to that in a moment.
To help me understand the structure of the query, I like to draw things out.
I’ve demonstrated this process in my courses in the Database Star Academy,
which you can see a link for in the description below, and I’ll cover it here briefly too.
So, the first table is called contests. Let’s draw a box on the screen and label it “contests”. I’m
using a tool called Lucid Chart here, but you can use whatever tool you like, or even pen and paper.
I’ll add in the table alias of “c” so it’s easier to match to the query.
Next, we’ll draw a box for the next table mentioned, which was colleges.
The third table was challenges, so let’s add that.
Now, how are these tables related?
Back in the query, we can see after the Join keyword and the table, the columns used in
the join. This shows one column in each table which is used in the join. So, between contests
and colleges, there is a column in each table called contest ID. Let’s add that to the diagram.
We’ll add a label under each of these tables called contest ID to represent the columns.
Then draw a line between them. This can help us with visualising how the data is related.
What is the next join? We can see the challenges table is joined on
the college ID which exists in both the challenges table and the college table.
So, let’s add those two columns to the diagram.
We’ll add the labels here, and draw a line between them.
After that join, we see a left join. Then there is a bracket instead of
a table name. What does this mean? I thought we could only join to tables?
This part of the query is called a subquery. Inside the brackets is a Select query. We can
see the Select clause, some columns, another From clause, and a group by, then the closing bracket.
A subquery can be written here, which will return a set of rows and columns,
and is treated just like a table or a view in the main query.
So, the query is actually joining to the results of a subquery.
In case you’re wondering, this kind of subquery is called an “inline view”,
as it operates like a view but is not created as a view in the database.
So, how can we represent that on our diagram?
I would create a box for the tables inside the subquery, and surround that with another box.
The only table inside the subquery is called submission stats.
Let’s do that now.
We can create a new box here called submission stats. This is the table inside the subquery.
To show that it is a subquery, we can surround this with another box,
and give this a label of “ss”, which is the table alias given in our query.
How is it joined to the main query?
In our query, we can see that the ss challenge ID
is joined to the cl challenge ID. So, let’s add that to our diagram.
Add the challenge ID inside the SS box.
Then, draw a line from that to the challenge ID in the challenges table.
What’s next in our query?
We have another subquery. This time, it selects from the view stats table.
It has an alias of “vs”, and also joins to the challenge ID.
We can add that to the diagram in a similar way.
We create a box for the underlying table of view stats. We then add a surrounding box
with an alias of “vs”. Then, we add the column of challenge ID, and join it to the challenge table.
Back in our query, what else do we have?
We’ve seen the tables, now let’s write down which columns are being used.
In the Select clause, we can see the contest ID is being shown, and this comes from the table
with an alias of c, which is the contests table. Let’s add that to our diagram.
The contest ID column is already here. There are some columns that are shown
in the SELECT and others that are just used for joins or filters.
I’ll put a little star next to the column name to indicate it’s being shown.
What else do we have? We’ve got hacker ID and name, both from the c table.
We can add two items for those under the contest table here.
We’ll add a star next to them to show they are being displayed.
Next we have a few SUM functions. We show the sum
of two columns from the ss view and two columns from the vs view.
If we look further down the query, we can see these actually come from another
SUM function inside each subquery. We can see the total_submissions column is being summed,
and given an alias of ts, and is then summed in the main query.
Let’s add this to the diagram.
Under the box for submission stats, inside the box for ss, we can add a few columns here.
We can add the challenge ID, then sum of total submissions with an alias of ts.
Then we add the sum of total accepted submissions that had an alias of tas.
Let’s add a star next to both the ts and tas columns,
as they are being shown in the main query.
In the second subquery, we can see something similar is happening.
We have the challenge ID, the sum of total views, and the sum of total unique views.
We can add those columns to the diagram in the same way as the other subquery.
For the final part of our diagram, let’s look at any filters.
We can see at the bottom there is no WHERE clause, but there is a HAVING clause.
This means there is some filtering being done on columns after a grouping has been applied.
We can see the having clause mentions four criteria:
each of these SUM values is not equal to zero.
Back on our diagram, we can add this information somewhere.
You can write it next to the columns, or in a box somewhere else on the diagram.
For this example, I’ll put it in a box on the diagram, to mention that all of these
columns must not be equal to zero. This adds a bit more usefulness to the diagram.
Here’s our completed diagram. We can see that the columns shown in the results are from the
contests table. There is nothing shown from the colleges or challenges table,
and it seems they are just being used for joins. The challenges table is then joined
to the two subqueries, and the SUM from those subqueries is used in the main query.
So, with this diagram, we’ve been able to understand our query a little more.
Next, let’s look into the data in these tables.
Once I can see what tables and columns are used in the query,
I like to look into each table to see what data is inside.
We’ll do this now, by running a simple Select query on each of the tables.
Let’s start with contests. We can run a select * from contests query.
I’ve chosen to use star here instead of specifying all columns or a few columns,
because we’ll be able to see all columns and it’s not a query we want to optimise yet.
We can run the query and see the data here.
We’ve got some sample data which I’ve loaded in for this video. We can get an idea of what the
contest ID looks like and any other columns we want to see, such as the hacker ID and the name.
Let’s look at the next table. We can run select * from colleges, and see the results.
We don’t really need to see much in this table, as we’re not showing any of the
columns in our final query and we’re only using it to join the challenges to the contests table.
Next we’ll run a select query on the challenges table. We can see the results here
We can see the challenger ID column which is the only one that is used in the query,
but we can see other columns too, which helps us understand more about each row.
What about the subqueries?
We can query the submission_stats table with a similar select query.
We run the query, and see the results here. We can see the challenge ID, the total submissions,
and total accepted submissions. These columns are used in our subquery.
We can run a similar query on the view stats table.
In the results, we can see the challenge ID, total views, and total unique views.
So, what do we do next to understand our query? We know a bit about the tables that are being used.
Let’s look at those subqueries.
In this query, we can copy the select statement from the subquery, and run it by itself.
You can do this for most, but not all subqueries.
In this case you can because it does not refer to any columns in the main query.
We can run this select query, and see what it shows.
In the results for the first subquery, we can see a challenge ID column,
the ts column which is a result of the sum of total submissions, and the tas column which
is a result of the total accepted submissions. This result is what is joined to the main query.
We can see there is one row for each challenge ID, and the result of two SUM functions.
We can do the same thing for the second subquery.
We can copy it and paste it further down the editor, and run just that query.
In the results, we can see the challenge ID, the sum of total views in the column tv,
and the sum of total unique views in the column tuv. This is also joined to the main query.
Once we run the subqueries, we know what they are about. In SQL queries,
there is a way to write a comment on the query,
to capture a note and for the database to ignore what we write so it doesn’t break the query.
This comment feature is helpful if we want to improve our understanding of a query.
A good place to capture a comment is on the subqueries. We can write a short
description of what the subquery does, so when we return to it in the future, we don’t forget.
There are a few ways to add a comment, depending on your database.
A method that is accepted in all database is opening a comment with slash then star.
We can type in a comment, then close it with star and slash.
We’ll add a comment here, at the start of the first subquery.
We’ll say that this subquery gets the total submissions per challenge.
It’s short and simple but can save us some time in the future.
We can do a similar comment for the second subquery.
We could write that this subquery gets the total views per challenge.
We’ve got an understanding of each of the subqueries now,
and can see they are joined to the main query on the challenge ID.
Do you know why it would use a left join for these? A left join will show
all rows in the table on the left, and any matching rows in the table on the right,
and if there are no matches it will show NULL.
So, this means the data from the challenges table is shown,
and if there are records from the subquery it will show those, but if there are no records
from the subquery it will show a NULL value for that challenge ID. It prevents challenge
IDs that have no records in the subquery from being excluded from the main query.
It looks like we still want to see challenges even if they have no submission stats, or view stats.
The next thing we can do is run the main query in parts.
We’ve selected the data from contests, so let’s run the entire query.
But, for now, we’ll exclude the HAVING and ORDER BY clause just to see what data is shown.
Comment those out, so we can keep them but not
include them when the query is run, and run the query.
We can see there is some data about the contest, then the results of the SUM functions.
We can see a range of different values here, and some of those have a value of 0.
What happens if we run our full query, including the Having and Order By clauses?
We do this now, and see the results. We can see that some rows have been
excluded because they did not meet the having clause criteria.
We can also see the results are ordered by the contest ID.
We could add a comment near the having clauses that describes what we are doing. We could say
we are excluding all rows where any of the sums are zero. You could say this is obvious given
the having clause criteria we have, so you may not want to add the comment, but it’s up to you.
The last thing we can do to understand our query
is to add a comment at the top of the query to give it a brief description.
Now that we have looked at the tables, the columns, the subqueries,
and the filtering criteria, we should know enough about the query to describe it.
So let’s write a description. This will help us realise if we know enough about it,
and also help us or anyone else who opens the query in the future.
We can write a description like this, as a comment.
Our description here says that the query shows contest details and the sum of views
and submissions for each challenge, where all of the summed values are not equal to 0.
This is a decent description and helps anyone who
opens this file understand more about what the query is doing.
In this video, we started with a messy-looking query and no understanding of what it does,
to a formatted query, with a quick diagram we created,
and comments on the query, all of which help our understanding.
I hope you enjoyed this video. Complex queries are one of the topics I get asked about the most,
so I wanted to create a video like this to explain
how I would try to understand a query to help you with an approach to use.
Let me know what you think in the comments below and
if you want me to create more videos like this.
If you want to learn more about database design and SQL, visit my website at databasestar.com.
If you liked this video, consider subscribing to my channel.
Thanks for watching.