0

Below is the question:

Display the last name, first name and monthly discount for all customers whose monthly discount is lower than the monthly discount of customer number 103 (Customers table).

I have solved the following question with 2 different approach. One is with SELF JOIN and the other is with Sub-Query.

Both the approaches are demonstrated below:

Approach 1 - SELF JOIN

SELECT C1.Last_Name,C1.First_Name,C1.monthly_discount
FROM customers C1 JOIN customers C2
ON C2.Customer_Id = 103
AND C1.monthly_discount < C2.monthly_discount

Approach 2 - Sub-Query

SELECT Last_Name,First_Name,monthly_discount
FROM customers
WHERE monthly_discount <
(SELECT monthly_discount FROM customers 
 WHERE Customer_Id = 103)

Although, I am getting same result by using both the approaches but I am not sure whether it is better to choose SELF JOIN or Sub-Query.

pro_data
  • 151
  • 6
  • use this post https://stackoverflow.com/questions/2577174/join-vs-sub-query – RF1991 Mar 20 '22 at 15:02
  • 3
    Since SQL Server uses statistical optimization, there is normally no real need to turn subqueries into JOINS for performance reasons. Given that, it's generally better style to use the approach that is a more straight-forward translation of the logical requirements, which in tis case is the subquery version. – RBarryYoung Mar 20 '22 at 15:50
  • 2
    I don't necessarily agree with RBarryYoung's comment. While *most* times you'll end up with a performant execution plan using a subquery, there are times a self-join yields a better execution plan. To determine which one's "*better*" you should compare the [execution plans](https://docs.microsoft.com/en-us/sql/relational-databases/performance/execution-plans?view=sql-server-ver15) and [runtime statistics](https://www.mssqltips.com/sqlservertip/1255/getting-io-and-time-statistics-for-sql-server-queries/) of both, and generally pick the more performant one. We couldn't advise without that info. – J.D. Mar 20 '22 at 16:58
  • For SQL performance questions, we need to see the execution plans. Please share them via https://brentozar.com/pastetheplan – Charlieface Mar 20 '22 at 18:02
  • 1
    I agree with RBarryYoung and Bart Hofland. In my opinion, too, the "better" query is the one that is more readable (the subquery version). In general: if I want to select data from a table where a condition is to be met, I select from that table and put the condition in the where clause (`WHERE monthly_discount < (...)`. Only if I ran into performance issues, would I consider re-writing the query. Usually, though, performance issues are not due the query when it is properly written, but due to a lack of appropriate indexes or something else. – Thorsten Kettner Mar 20 '22 at 21:08

1 Answers1

2

IMHO it depends on what you consider to be "better"...

In almost all cases, I personally consider a query that is more understandable by human/developer readers to be "better".

Only when performance is crucial, and only when differences can be objectively measured (investigating query plans and using profilers or other measuring tools) I will apply technical optimizations for specific queries. And in such a case, I will keep the original (readable) query in comments for documentation purposes.

Bart Hofland
  • 3,700
  • 1
  • 13
  • 22