John,
Your query is not correct. There are syntax errors. That being said, it could have been written like this:
Code:
SELECT
License.*,
(Select MAX(LicenseAnnualNumber)
From LicenseAnnualRenewal
Where license.licenseid = licenseannualrenewal.licenseid
) As MaxLicenseNumber
FROM logosdb.dbo.license
This is the classic subquery approach. As I mentioned in another thread earlier this week, the 2 queries are not the same. There are slight diffferences.
The subquery method:
1. Will return exactly one row in the output for each row in the main table (license).
2. Will error if the subquery returns multiple rows. In this case, it isn't possible because we have a max without a group by, so you are guaranteed to get either one row, or no rows (if the child table doesn't have any matches on licenseid).
The derived table method:
1. Uses a Join: Theoretically, joins can cause multiple rows in the output. But in this case, that cannot happen because we have a scalar value (licenseid) joining to the derived table, so again, there will be 0 or 1 row that matches each row in the parent table.
2. Since this uses a join, you may get less rows in the output than there are rows in the parent table. This would only happen if there were no rows in the child table for the join condition (licenseid). This behavior can be changed by using a left join to the derived table, is which case you would get NULL returned. This would be the same for both queries.
I created 2 similar queries, but using tables in my database so that I could test this. My main table has 9,482 rows and the child table has 287,305. The execution plans are the same. The IO statistics are the same. Everything is the same.
Code:
Select Student.*,
(Select Max(CalendarDate)
From Calendar
Where Student.CurrentSchoolId = Calendar.SchoolId
) As LastDayOfSchool
From Student
Select Student.*,
LastDayOfSchool
From Student
Left Join (
Select SchoolId,
Max(CalendarDate) As LastDayOfSchool
From Calendar
Group By SchoolId
) As LastDay
On Student.CurrentSchoolId = LastDay.SchoolId
Now, to answer your question...
There are several reasons why I prefer the derived table method:
1. It's what I am used to based on my experiences with older SQL database engines. I *think* that earlier versions of SQL Server would NOT create the same execution plan, and the derived table method was faster.
2. In my opinion, it is easier to tune the query. Look at the query I posted (from my database). You can see that it uses the schoolid (in the select and group by clauses) and it also uses the CalendarDate column (in the max aggregate). If I think about this single query, it is easy to optimize it for performance by adding a multi-column index on SchoolId and CalendarDate.
3. In my opinion, it is easier to write the query. You see, Usually, when I write a query like this, I start from the inside out. In this case, I wrote the query that was to become the derived table. Once I had it written, i squished the F5 button and made sure that it ran without any errors. I then write the outer query, which was also relatively simple (for me) and then tested again.
When you use the subquery method, you cannot run it by itself because the where clause is forming the join to the outer query. This means I need to write the entire query before I can test it. For a (relatively) simple 2 table query, this isn't so bad, but it's not uncommon for me to have queries that involve a dozen tables, which makes things a lot more complicated.
I'm a very big fan of the KISS principle. In case you've never heard of it ([!]K[/!]eep [!]I[/!]t [!]S[/!]imple, [!]S[/!]tupid). In my opinion, the derived table method is simpler to understand and allows me to build up the query in smaller chunks which also allows me to keep it simpler.
I should also mention that the query could just as easily have been written using a common table expression approach. In fact, it would be just like derived table method, but the syntax would be a little different.
Code:
;with LastDay As
(
Select SchoolId, Max(CalendarDate) As LastDayOfSchool
From Calendar
Group By SchoolId
)
Select Student.*,
LastDay.LastDayOfSchool
From Student
Left Join LastDay
On Student.CurrentSchoolId = LastDay.SchoolId
Notice that the only difference between this query and the derived table query is the syntax. Most notably, the query for the derived table was removed from the outer query and pushed to the top. Many people prefer this method. I don't, but mostly because I got really used to the derived table method. Also note that the execution plan is the same, the IO is the same, and performance is the same.
All things considered, I prefer to stick with the method that I am used to, and one that is easier for me to write the queries. May sound lame, but it works for me.
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
