Which is better when using SELF JOIN vs Sub-Query in SQL Server?-CodePudding

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.

CodePudding user response：

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.