Back to articles list June 11, 2020 - 5 minutes read What’s an SQL Inline Query? Kamil Bladoszewski Kamil works as a Java developer and is a machine learning hobbyist. He loves clean code and turning interesting data into effective charts. His goal of giving people access to knowledge is fulfilled by being a member of the ML in PL Association, an organization that supports the machine learning community in Poland. In his free time, he likes playing computer games. Tags: sql learn sql subquery 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. We won’t spend more time on subqueries now. If you’re not familiar with them, I recommend you read the SQL Subqueries article on the LearnSQL.com blog for a basic overview of the topic. 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 idtitleyeardirector_id 1The Lord of the Rings: The Return of the King200314 2Mad Max: Fury Road201569 3Avengers: Endgame201971 rating idratingmovie_iduser_id 171332 261469 3101767 4101111 58184 622417 772441 8102118 95274 1022630 1110399 1243189 1373887 1483521 1543144 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; max_avg_rating 8.2 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. Tags: sql learn sql subquery You may also like Beginner’s Guide to the SQL Subquery Did you know that you can use a SQL query inside another SQL query? Find out the ABCs of subqueries and improve your SQL skills! Read more What Is a Nested Query in SQL? Have you ever faced a problem where it would be great to put a SELECT inside another SELECT statement? Learn how to use a nested query in SQL. Read more Subquery vs. CTE: A SQL Primer SQL subqueries and CTEs seem similar, but even pros may not know all their quirks. Learn their similarities, differences, and best use cases. Read more Subquery vs. JOIN What are the differences between a subquery and a JOIN in SQL? How do you choose one over the other? See examples of when to use which. Read more SQL Subqueries The article describes what a subquery is and what these useful statements look like. We will cover basic examples with the IN, EXISTS, ANY, and ALL operators, look at subqueries in FROM and WHERE clauses, and explore the difference between correlated and nested subqueries. Read more What Are the Different Types of SQL Subqueries? What are SQL subqueries? How many types are there, and what can they do? This article will guide you through SQL subquery basics. Read more Converting Subqueries to Joins No more badly performed queries! Find out how you can convert SQL subqueries to joins and improve your query efficiency. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.