CODEWITHSUNDEEP
sqlsubqueryflattenself-join
ImpossibleInc
ImpossibleInc

Reputation: 1

Can/should a single table subquery be flattened

I was going through my course material for the SQL class that I am taking and it mentioned that in order to make programs run faster and run in one pass using joins verses a subquery. It stated that most subqueries can be flattened if they follow an IN, EXISTS or produce a single value can be flattened and most that follow NOT EXISTS or contain a GROUP BY can not be flattened.

So I was wondering if I could use a join on a single table rather than a subquery or is it the case that since it's already in the same table it already happens in one pass and there is no reason to use a join.

For example I was asked to select all the horse names that were above the average height where all the data was already in the same "Horse" table. This is what I came up with if I was to do a subquery

SELECT RegisteredName, Height
FROM Horse
WHERE Height > (SELECT AVG(Height)
                FROM Horse)
ORDER BY Height

Is this just how you would do it or am I missing how I would do it. There isn't a great value in the table to self join it to. Would I add another one so I can and that make it faster? I got the problem right I'm just wondering if there is a better way to do it

Upvotes: 0

Views: 125

Answers (1)

Saint Rollox Digital
Saint Rollox Digital

Reputation: 106

Your understanding and usage of a subquery to solve the problem of selecting horse names that are above the average height is absolutely correct and efficiently handles the requirement. In SQL, using a subquery can indeed be very effective, especially in scenarios where you are comparing values against an aggregate computed over the same table.

In your specific case, your SQL query:

SELECT RegisteredName, Height
FROM Horse
WHERE Height > 
   (SELECT AVG(Height)
   FROM Horse)
ORDER BY Height

is likely the optimal way to address this requirement given that the query is straightforward, and any alternative approach would not necessarily provide performance benefits. The subquery you used is a scalar subquery (returns a single value), which is generally well-optimised by SQL database systems, especially if the table isn't very large or the query can make use of indexes effectively.

Upvotes: 0

Related Questions