5 ms·
Optimizer nuances aside, I agree with this and think it's helpful for anyone writing SQL. I've trained a lot of non-developers (and new-developers) to use SQL,
by pjungwir 7y ago
Optimizer nuances aside, I agree with this and think it's helpful for anyone writing SQL. I've trained a lot of non-developers (and new-developers) to use SQL, and one of my favorite tips is, "Start with FROM."
Even sooner than "start with FROM" is "What does each row of the result mean?" Is it a sale? A month of sales? A customer? If you can answer what you want, then it's usually easy to start there. Especially if your answer corresponds to an existing table, put that in the FROM, and then add whatever joins you need. You can even think about the joins "imperatively".
Thinking imperatively about JOINs is also a helpful way of understanding a mistake I call "too many outer joins", which I wrote about here: https://illuminatedcomputing.com/posts/2015/02/too_many_outer_joins/ https://illuminatedcomputing.com/posts/2015/02/too_many_oute... If you don't know about this mistake, it's easy to get the wrong answer from aggregate functions like AVG or COUNT. (EDIT: I should probably call this "too many joins" instead, since it's not really specific to outer joins. But in practice I find outer joins are more often used in these sort of situations.)
- TehShrike 7y agoI've called it the "GROUPing pitfall" since those extra JOINs only really come back to bite you when you try to aggregate the results: https://wikido.isoftdata.com/index.php?title=The_GROUPing_pitfall https://wikido.isoftdata.com/index.php?title=The_GROUPing_pi...
- pjungwir 7y agoNice writeup! The nasty part is that you don't get an error, you just get wrong answers. You might not even notice for a while. Such bad news!
- enobrev 7y agoThanks to both of you for these articles. I'm very fond of outer joins as in most cases (in my day to day work) they're exactly what I need. While I don't use them often with aggregate queries, I didn't realize that multiple OUTER JOINS will break aggregates. I'll need to review my query logs to make sure I'm not doing this anywhere.
- deleted 7y ago[deleted]
- ozlop111 7y agoWas Emily Moore's child ever born?!?!
- lucb1e 7y agoUnless I have the whole query in my head by the time I've typed "$db->query(", I will indeed start with the FROM by just typing the placeholder SELECT * and then revisiting the asterisk when I'm done with (most of) the query. Somehow that makes it easier, especially with complex queries.
- gigatexal 7y agoI do this too! I thought I was the only one
- mritchie712 7y agoI do this too, so the autocomplete in SeekWell automatically does this, check it out: https://www.loom.com/share/9c7979a163eb4513b3320e6e90c66079 https://www.loom.com/share/9c7979a163eb4513b3320e6e90c66079 If you type just a table name into an empty cell it will autocomplete with a full SELECT statement. e.g. if you typed "pubuser" and selected "public.users" it would autocomplete with: SELECT pu.* FROM public.users AS pu WHERE 1=1 Disclaimer: I built this.
- default-kramer 7y ago> Especially if your answer corresponds to an existing table, put that in the FROM, and then add whatever joins you need. This is absolutely right. In fact, my ideal query language would require the programmer to be explicit if they want to increase the cardinality of the result set. Meaning, if I start a query with "from Employee ..." the query language should not let me accidentally add a join that will cause the result set to contain the same Employee more than once. (It should be possible, but only if I make it clear that it is intentional and not an accident.)
- yread 7y agoFor updates, and deletes on the other hand I try to start with the where clause. Learnt the lesson after running a update query once before i typed the where to cause something like 40k worth of damage
- brigandish 7y agoGood advice. I also break up the statement so that an incomplete highlight and execute will fail, e.g. DELETE FROM Customers WHERE Name='Gude' AND Area='Tama' ; Much harder to leave off half the clause doing this kind of thing.
- fr1tkot 7y agoI've forced the habit upon myself to first compose a SELECT statement and verify that it will give me exactly the dataset that I want to run my operation on. Then I wrap that statement into a CTE and run the DELETE / UPDATE on the CTE. It doesn't save me time, but a lot of nerves.
- CubicsRube 7y agoI'm used to writing the SELECT query and adding the commented-out DELETE just below the SELECT line. SELECT * -- DELETE FROM Sales WHERE Customer = 1 Next, selecting the whole query and executing it. If results are satisfactory - selecting only the DELETE part.
- piaste 7y agoThis is the one I use too. Tiny difference - I write select * -- delete from sales where customer = 1 so it's even harder to accidentally highlight the 'delete' part. Although I've switched to DBeaver for a year now, and it automatically pops up a warning when it detects a DELETE query without a WHERE, which is very nice.
- samus 7y ago
- dspillett 7y ago> If you can answer what you want, then it's usually easy to start there. Yep. As developers/designers we need to understand this as much as we wished our clients did.
- scottc 7y ago> "Start with FROM." Interesting because LINQ starts the same way, or at least that's how I remember it.