What’s an SQL Inline Query?

Have you ever heard the term “inline query”? How are they different from subqueries and derived tables? Have you looked this up in numerous places and still don’t know the answer? Let’s embark on a journey and find out together!

The first time I heard the term “inline query”, I was a little bit surprised. It doesn’t sound like one of SQL’s nuances and I thought I knew all the basic concepts. Unfortunately, googling the term wasn’t very helpful – there were only a few results and all the answers were vague. It took me quite some time to do the proper research and learn about inline queries in SQL. That’s why I’ve decided to write this article and gather all I’ve learned in one place.

Subquery vs. Inline Views

Have you heard about a subquery? It’s a query within a query – at least in the terminology I was taught. Next, there’s the phrase inline view. An inline view is a query in the FROM clause of another query. How I learnt, this makes it the same as a subquery. In Oracle, though, inline views are placed in the FROM clause only, while subqueries are in the SELECT or WHERE clauses. (See subquery in Oracle FAQs and inline view in Oracle FAQs.) In other words, Oracle treats these as two separate concepts.

So, if you’ve learnt about subqueries outside of Oracle, you’ll be able to understand inline views. However, you should remember that Oracle users may misunderstand you if you use the terms subqueries and inline views interchangeably.

To practice writing SQL queries, including subsqueries, I recommend our interactive SQL Practice Set course. It contains over 80 hands-on exercises to help you refresh your SQL skills.

So, What’s an Inline Query?

An inline query is a query in the FROM clause. You select data from it as you would a table.

Let’s take a look at an example of an inline query. We’ll work with some movie data from a site where people rate movies they’ve watched:

movie – Contains information on movies:

• id – A unique ID for each movie.
• title – The title of the movie.
• year – When the movie was released.
• director_id – The ID of the director of that movie.

rating – Stores user ratings for movies:

• id – A unique ID for each rating.
• rating – A number from 1 (meaning “it’s so bad that showing this movie should be illegal”) to 10 (meaning “I could watch this movie for the rest of my life, 24/7”).
• movie_id – the ID of the rated movie.
• user_id – the ID of the user who rated the movie.

movie

rating

The query will show the highest average movie rating. Take a look:

SELECT
  MAX(avg_rating) AS max_avg_rating
FROM (
  SELECT
    AVG(rating) AS avg_rating
  FROM rating
  GROUP BY movie_id
) AS avg_movie_ratings;

In the FROM clause, we’ve used the inline view (called avg_movie_ratings and shown in bold) to compute the average rating for each movie. Then, from the averages, we selected the biggest value. The inline view we’ve used works like a temporary table.

Inline Views Outside of Oracle

As I’ve mentioned before, inline view is rather an Oracle term. I’ve also stumbled across it while reading about MySQL; however, some people referred to it as an inline view and some as a subquery. In other database engines, inline views have different names. For example, in MS SQL Server, a subquery in a FROM clause is called a derived table.

You’ve probably noticed that I’m using the term subquery here. This is because subquery (or sometimes subselect) is the name used in PostgreSQL – the database engine I use most. This name is also used by the majority of MySQL users.

No matter what you’re calling inline views, remember – it’s only the name of a concept. It doesn’t really matter how you’re naming it. The important thing is to know how to properly use the concept in your queries.

Inline Views vs. Subqueries

Let’s go back to Oracle’s terminology for a moment. What’s the difference between inline views and subselects (See subquery in Oracle FAQs) if in some engines they have the same name?

Even though they look similar, they work differently. We’ll focus on the two biggest differences.

The first difference is that inline views can contain multiple columns, while subqueries (in the Oracle meaning) should return only one. The reason is simple – an inline view works like a table and tables can contain more than one column. Subqueries, on the other hand, generally work as a single value.

The second one is correlation. In non-inline views, you can use a value for the outer (main) query. That way, your subquery can return a different value for different rows. This concept is very powerful and can save you time when you write complicated queries. Read more about it in Correlated Subquery in SQL: A Beginner’s Guide on the LearnSQL.com blog.

If you feel like you need more info on the topic and the different types of subselects (including inline views), check out the article What Are the Different Types of SQL Subqueries?.

How Much Do Names Matter? Not a Lot!

As I’ve already mentioned, it doesn’t matter whether you call the concept inline view, derived table, subquery, or subselect. Everything comes down to your knowledge and usage of this powerful SQL technique. Of course, when communicating with others, you should do your best to avoid miscommunication. That’s why it’s worth knowing all the different names this concept goes under. However, if your team agrees, inline view could be called from-part thingamajig – as long as everyone knows what you’re talking about!

If you’ve never heard of the concept, you may want to check out the SQL Basics course at LearnSQL.com. If you’ve already learnt about it but don’t feel very confident in your skills, you may want to solve a few exercises from the SQL Practice Set. We just hope that you won’t mind us calling the concept subquery.