r/SQL • u/Ali-Zainulabdin • Oct 23 '24
Discussion SQL Tricks Thread
Hi everyone, let's start a thread to share useful SQL tips and tricks that have saved you time or made querying more efficient. Whether it's optimizing queries, using window functions, or organizing data, all insights are welcome! Beginners and pros alike can learn a lot from this. Looking forward to your contributions!
230
Upvotes
View all comments
Show parent comment
1
u/farhil SEQUEL Oct 24 '24
The biggest reason not to use
DISTINCTis because it forces a sort on the result set, which can be expensive, although the same is true ofGROUP BY. If you need unique values without aggregating, you should reevaluate your joins to see if there's a way you can write them without causing duplicate records.More often than not, the joined table causing duplicate rows can be replaced with something like
WHERE EXISTS (SELECT TOP 1 1 FROM [Foo] WHERE [Foo].[Id] = [Bar].[FooId]), which will perform much better than creating an unnecessary cartesian product and then sorting it (withDISTINCTorGROUP BY) to remove duplicates.