Start Learning for Free

Join over 1,000,000 other Data Science learners and start one of our interactive tutorials today!

Topic manipulation small

SQL Tutorial: How To Write Better Queries

July 20th, 2017 in Data Manipulation

Structured Query Language (SQL) is an indispensable skill in the data science industry and generally speaking, learning this skill is fairly easy. However, most forget that SQL isn’t just about writing queries, which is just the first step down the road. Ensuring that queries are performant or that they fit the context that you’re working in is a whole other thing.

That’s why this SQL tutorial will provide you with a small peek at some steps that you can go through to evaluate your query:

Comments

abaskm
You have a spelling error:

```
The ANY and ALL Operators
Also the ALL and ALL operators are some that you should be careful with because, by including these into your queries, the index won’t be used. Alternatives that will come in handy here are aggregation functions like MIN or MAX.
'''
The ANY and ALL Operators
Also the ALL and ALL --> Also the ANY and ALL
08/03/17 3:35 AM |
karlijn
Hi abaskm! Thanks for pointing that out; I have updated the post accordingly :)
08/07/17 6:12 PM |
laurenheren
What would you propose using instead of DISTINCT? I just used it for a query recently.
07/30/17 8:00 PM |
karlijn
Hi Lauren; It's definitely not wrong to use DISTINCT. What I meant was that you should be careful when using it, don't forget to consider the database that you're querying and double check whether you really need this DISTINCT operation to take place to get the results that you want to accomplish. Hope this helps!
08/07/17 6:14 PM |
laurenheren
For the following tip, how would you pre-calculate the values if you intend for the query to do the calculation for you?

Tip: in cases where you make use of the proposed alternatives, you should be aware of the fact that all aggregation functions like SUM, AVG, MIN, MAX over many rows can result in a long-running query. In such cases, you can try to either minimize the amount of rows to handle or pre-calculate these values.
07/27/17 7:25 PM |
karlijn
Hi Lauren! I best refer you to this SO thread: https://stackoverflow.com/questions/11360738/when-to-store-precalculated-values-vs-calculating-them-when-retrieving-them :)
08/07/17 6:20 PM |
laurenheren
How do you test the effect of data type conversions before you run a query?
07/27/17 6:04 PM |
karlijn
The quick-and-dirty way that I can think of on top of my head is to set up (or make use of) some test database (development/staging/...) to test out your strategy in a small setting first before bringing it to production. You should probably always do this but I think it's probably the best way to test this out for explicit conversions.
08/07/17 6:43 PM |
nhmarcellino
Thanks very much for this tutorial.It is really informative.Keep it up
Marcellin
07/26/17 8:00 AM |
karlijn
Thanks for the kind words, Marcellin!
08/07/17 6:10 PM |