This is a general question: Does anyone have a tip as to how i can know when i should use distinct in my queries ? I am struggling at understanding when to use it exactly. I tend to use it when I don't need it and not when I do.
thank you all very much.
Basically, there is little reason to use select distinct -- although it is sometime convenient short-hand.
If it can be avoided, avoid it! SQL incurs overhead for removing duplicates, even if there are no duplicates. So, select distinct is slower than select.
Often select distinct is more appropriately written using group by -- because often you want some column to be aggregated (such as the maximum date/time).
That said, it can be convenient shorthand, so it should not be avoided altogether, just used rarely.
There is no general rule as to when to use DISTINCT, it is based on your requirement i.e. when you have two same values in one column but you only require one value so you will use distinct.
Suppose you have a list of banks and branches in a city. But you need to know how many unique banks are operating in the city then you will write
select distinct bank_name from city;
I use distinct when I want to ensure rows are not duplicated in a query that could have duplicate records for the field combination I am selecting. Generally, this would be when selecting a set of columns that do not include a primary/unique key and are not guaranteed to be unique when the selected fields are taken together.
For example, if I was selecting customers that had purchased this year to send a letter to, and customers can have more than one order in a single year and I want to ensure that I send only one letter per person and address, I would use Distinct to ensure that I get one occurrence of each unique customer name / address combination.
--could return multiple records for repeat customers if Distinct was not present
Select Distinct BillingName, BillingAddress
from Orders
where OrderDate > '2019-08-01'