Rational use of left Outer Join in SQL Server for development!

For example, we want to summarize the consumption items of a person, which correspond to the following two tables: Theme and themedetail.

The theme record is:
Themeid (INT) themename (varchar [10])
1 dining
2 business trips
3. Traveling
4 others

The themedetail record is:
Detailid (INT) themeid (INT) price (money)
1 12.5
2 1 5
3 1 6
4 2 11
5 2 17
6 3 8

Themeid in theme is a one-to-many relationship with themedetail. The themedetail table is understood as follows: the "meal" fee is 12.5 + 5 + 6 = 23.5 yuan, the "Travel" fee is 11 + 17 = 28 yuan, the "Ride" fee is 8 = 8 yuan, and the "other" fee does not exist. The corresponding SQL statement can be expressed as follows:

Select Top 100 percent DBO. theme. themename, isnull (sum (DBO. themedetail. Price), 0)
As totalprice
From DBO. Theme inner join
DBO. themedetail on DBO. theme. themeid = DBO. themedetail. themeid
Group by DBO. theme. themename, DBO. theme. themeid
Order by DBO. theme. themeid

The execution result is as follows:
Themename totalprice
Dining 23.5
Business trip 28
8 passengers

If the consumption record is not stored in this way, you can use the inline method to meet the requirements. However, we need to make statistics on every item in theme, it also includes "other" items, so we should adopt another method to implement it. This is the left outer method. The corresponding SQL statement can be expressed as follows:

Select Top 100 percent DBO. theme. themename, isnull (sum (DBO. themedetail. Price), 0)
As totalprice
From DBO. Theme left Outer Join
DBO. themedetail on DBO. theme. themeid = DBO. themedetail. themeid
Group by DBO. theme. themename, DBO. theme. themeid
Order by DBO. theme. themeid

The execution result is as follows:
Themename totalprice
Dining 23.5
Business trip 28
8 passengers
Other 0

Does this satisfy our requirements!