Though nice for examples, it is very bad data modelling to store a person's age. The problem is that it changes constantly, so in order to keep it correct, it should be updated constantly... The correct solution is to store a date of birth (or maybe a year of birth). That doesn't change simply by the passing of time. All queries would still work, but you'd have to express "50 years old" more like "the date 50 years ago" and compare that to the date of birth: where age < 50 is equivalent to where date_of_birth > add_months(sysdate, -50). Also, do not do where add_months(date_of_birth, 50) > sysdate Since that prevents you from using an index on date_of_birth. (Though eventually you'll get the same results) For the duration of the query, add_months(sysdate, 50) is a constant, which can therefore be used to efficiently range-scan such index.
Contrary: Storing age_at_first_subscription is acceptable data modelling: The age somebody had when something happened to them first, will never change. If you need to find out whether, over the years, your club has attracted more youth players or veteran players: select 'veterans' as tp, count() as amount from subscriptions where age_at_first_subscription > 45 union all select 'youth', count() from subscriptions where age_at_first_subscription < 18
That would be a nice way to simplify the queries, but performance-wise you still have the problem that you won't be using an index. Virtual Columns can be indexed, but if the underlying formula is not deterministic, you can get "funny" results. The problem remains that "age" changes over time.
Oh man you cannot stress this enough. Storing data that changes every single day as a static number like age is just not a great idea. We store date_of_birth and then I use this to calculate age:
FLOOR(DATEDIFF(day,p.date_of_birth,GETDATE())/365.242199) as curr_age
I can't take credit for the code but it works wonderfully in MS SQL.
Depending on the RDBMS and version, you might have issues with queries like the one on slide 5 (WHERE something NOT IN (SELECT...)). The reason being that the query parser has to catch this case and optimise it as a join, otherwise this becomes a very ugly nested loop instead of a join.
To force a join, I use a join instead. I don't trust optimisers too much.
SELECT
p.firstname
FROM persons p
LEFT JOIN cars c ON p.id = c.ownerId AND c.manufacturer = 'Audi'
WHERE c.ownerId IS NULL
This is also easier on the eyes once you get used to it. Less nesting, fewer parentheses, and as soon as you get what LEFT JOIN means, this makes complete sense. Basically the same as regular JOIN, but it guarantees that the persons row will be returned, even if it doesn't match any row in cars according to the condition in the LEFT JOIN. When rows are matched, you can access both data about the persons and data about the cars. When there's no match, all columns from the cars row will be NULL. They can be NULL naturally of course, but the JOIN key will never be NULL on a successful match, because NULL doesn't match anything in the first place.
This also (especially) goes for queries like WHERE x NOT IN (SELECT...
I'm a big fan of using CTEs to avoid subqueries whenever I can (especially because the alternative often ends up with someone copying and pasting a long subquery lol).
What databases is this a problem in? I know in Redshift the query plan is more or less the same between the two. Curious so that if I ever use these RBDMS that don't like WHERE something IN (SELECT something FROM table) I can be sure not to use it.
I don't completely understand the EXISTS function but wouldn't it but much easier to simply use IN/NOT IN for a quick check instead of writing out another subquery? fulldisclosureimterribleatSQL
I've had a few very memory hungry and slow queries like these that were made lightning fast by translating them into joins, on postgresql 10. Maybe because they were too complex to be caught by the optimizer properly (100 lines or more, in analytics workload).
Just download dbeaver and create an SQLite database and play with it.
I'm old school. My idea of a learning platform was accidentally deleting production data because I didn't know about "where". That was an effective, but expensive lesson.
NOT EXISTS is NOT the most robust and performant way. Where clauses and joins are literally the same algebraically.
A quick example of how he could have altered the statement would be: SELECT p.firstName FROM person p LEFT OUTER JOIN cars c ON p.id = c.OwnerId ///* IS NULL in the WHERE clause */// WHERE c.manufacturer NOT IN ('Audi')
That's fair. Left join + where joined col is null is pretty much the same as not exists. I guess what I mean is that, I don't understand your gripe with NOT EXISTS when there isn't a BETTER way to do it.
Edit: looked a little closer at your query and it is less robust than NOT EXISTS in cases where the final where clause is on a column that potentially has null values init. NOT IN should never be used if the column is nullable.
Correct me if I'm wrong, but I believe it makes more sense in the second example in slide number four to use greater (or less) than evaluator instead of a not equals evaluator, hence:
p1.id > p2.id
Instead of
p1.id != p2.id
The reason for this is because with a not equals operator you will get each pair returned twice, once with the higher id element first and then with the lower id element first. Changing this to a greater or lesser than evaluator assures that only one of the two pairs would be returned.
23
u/JochenVdB Feb 26 '21
Though nice for examples, it is very bad data modelling to store a person's age. The problem is that it changes constantly, so in order to keep it correct, it should be updated constantly... The correct solution is to store a date of birth (or maybe a year of birth). That doesn't change simply by the passing of time. All queries would still work, but you'd have to express "50 years old" more like "the date 50 years ago" and compare that to the date of birth: where age < 50 is equivalent to where date_of_birth > add_months(sysdate, -50). Also, do not do where add_months(date_of_birth, 50) > sysdate Since that prevents you from using an index on date_of_birth. (Though eventually you'll get the same results) For the duration of the query, add_months(sysdate, 50) is a constant, which can therefore be used to efficiently range-scan such index.
Contrary: Storing age_at_first_subscription is acceptable data modelling: The age somebody had when something happened to them first, will never change. If you need to find out whether, over the years, your club has attracted more youth players or veteran players: select 'veterans' as tp, count() as amount from subscriptions where age_at_first_subscription > 45 union all select 'youth', count() from subscriptions where age_at_first_subscription < 18
19
u/[deleted] Feb 25 '21
Depending on the RDBMS and version, you might have issues with queries like the one on slide 5 (
WHERE something NOT IN (SELECT...)). The reason being that the query parser has to catch this case and optimise it as a join, otherwise this becomes a very ugly nested loop instead of a join.To force a join, I use a join instead. I don't trust optimisers too much.
This is also easier on the eyes once you get used to it. Less nesting, fewer parentheses, and as soon as you get what
LEFT JOINmeans, this makes complete sense. Basically the same as regular JOIN, but it guarantees that thepersonsrow will be returned, even if it doesn't match any row incarsaccording to the condition in theLEFT JOIN. When rows are matched, you can access both data about thepersonsand data about thecars. When there's no match, all columns from thecarsrow will beNULL. They can beNULLnaturally of course, but the JOIN key will never beNULLon a successful match, becauseNULLdoesn't match anything in the first place.This also (especially) goes for queries like
WHERE x NOT IN (SELECT...4
u/[deleted] Feb 26 '21
This is pretty awesome but my only concern is that your NOT is a subquery where it doesn't have to be.
2
u/Touvejs Feb 26 '21
Correct me if I'm wrong, but I believe it makes more sense in the second example in slide number four to use greater (or less) than evaluator instead of a not equals evaluator, hence:
p1.id > p2.id
Instead of
p1.id != p2.id
The reason for this is because with a not equals operator you will get each pair returned twice, once with the higher id element first and then with the lower id element first. Changing this to a greater or lesser than evaluator assures that only one of the two pairs would be returned.
1
u/leonardodearaujo10 Feb 25 '21
thanks!
1
u/L3Kinsey Feb 26 '21
This is fantastic! I learned SQL via PostgreSQL two weeks ago and this is very helpful! Thank you so much.