Agreed... it feels a bit more natural. I actually like using the extension methods for LINQ in C# in a similar order (not the LINQ syntax itself though).
edit:
CTEs can make it closer to the syntax in question though.
But the syntax is in the wrong order for autocomplete to help you.
If from was first, autocomplete could help you with the names of columns, like RStudio does with dplyr:
data_frame_name %>% select(column_name)
Because you start by piping the data reference into your select function, RStudio can inspect the data and autocomplete the column names in the select statement, completely opposite of what is possible inSQL
With a free order, you'd be able to start SQL queries with `FROM table_name SELECT ...` and have columns autocompleted.
Sorry, I didn't mean that in defense of SELECT being first. I agree with the article that SELECT must come toward the end. And the first thing I tell people I teach SQL to is that they have to read SELECT last.
Query languages that came after it that weren't concerned with compatibility realized this. XQuery has "for" first, which is to a first approximation FROM, and "return" last, which is like a more powerful SELECT. SQL++ and its brethren also had it this way very early on.
However there's so many applications and users that are accustomed to it the original way, that it is not usually possible to fight against the inertia.
I really like jvns's work but I have to say I found this diagram misleading when I first saw it! SQL queries "happen" in whatever order the database optimiser decides.
I'm really glad the accompanying article has a bunch of qualifications of the form "Database engines don’t actually literally run queries in this order" but a lot of the beauty of these diagrams is that they work on their own.
I wish the title of the picture said something more like "SQL queries are interpreted in this order" to make it clear we're talking about semantics and not what actually takes place.
It really is "whatever the optimizer feels like". Pretty sure that's the first law of SQL performance optimization - "The optimizer is always your best friend and worst enemy".
I've worked at a place where running time-sensitive batch file submission was the company's main line of business. It is always fun when the optimizer heuristic suddenly makes a 3-minute query take over 3 hours! And there's basically no way to defend against this while still using SQL, short of moving everything to incremental generation.
Not having query hints isn't the "SQL at fault" - it's very much the optimizer, or you wouldn't need to override it with said hint. I've seen it where one index hit an arbitrary number of rows and then the query flipped to joining on another, non-indexed column, increasing the time to run massively - that sort of 3m to 3 hours magnitude. Had to throw on a hint but that's only because the optimizer decided it knew better.
A slightly more charitable view of this situation is that an optimization which was previously triggering to make the query fast suddenly no longer applied.
I think that might be too optimistic - it went from a table with thousands of records to hundreds of millions of records. If I were to speak technically the oracle optimizer "did a derp".
So... stale statistics? I.e. the optimizer making the query plan assuming that the table has thousands of records (because that's what it had earlier) and now it has hundreds of millions of records? A sudden order of magnitude change in table sizes isn't normal operation, for most DB platforms that requires running some "everything's different now, please recalculate statistics" command to maintain proper functioning.
And that's why I listed a bunch of other options as well - simplifying code is probably the best one, but there will always be gaps in their heuristics.
In my experience, you have one of three scenarios:
You know ahead of time exactly the problem you are going to solve and so probably shouldnt choose SQL from a performance perspective.
Your problem size changes and you get to pick a simple system that you understand well and has predictable performance but might be slower than other options.
Or a complex system that's less well understood, and generally has great performance and productivity up to a tipping point, where the abstraction has leaked too much.
In your case - your query sounds exactly like those heuristics worked up to a point, and then finally failed. Is that the optimizers "fault"? It's mostly a trade off between the time spent making the plan and the time spent getting your data back - and again, sometimes those tradeoffs are wrong too.
Depending on why, static / manual statistics on tables (also normally a no-no) may also help.
(This is assuming whatever applicable regular maintenance (stats, index or table reorgs if needed) is done prior to the time-sensitive batch... and understanding sometimes there's just not enough hours in a day to run batch AND maintenance :| )
She has a whole section called "queries aren’t actually run in this order (optimizations!)"
SQL and other declarative languages can be frustrating to talk about because what you tell it you want and _how_ it does the work isn't supposed to matter. So there are two questions that you might answer with either jvsn's answer (how should you imagine it works to understand how to use SQL?) and your answer (how should you imagine it works to figure out why it's slow?). Someone learning SQL is probably asking the first question, somebody debugging performance is probably asking the second one.
So this is a weird criticism because neither of you is incorrect, it's just that different audiences require different kinds of detail. If she instead wrote the answer to your question then someone else would inevitably come along with a comment nearly identical to yours except it would say "well that's just implementation details but you should instead imagine that...".
> She has a whole section called "queries aren’t actually run in this order (optimizations!)"
Yep! Article is a big improvement; first I saw the image was earlier today when it was tweeted on its own here: https://twitter.com/b0rk/status/1179449535938076673?s=20 and her images are often awesome because they capture nuance in a tiny poster
Reason: I started dealing with Oracle when it was at v8 and I think up to including v9 when I specified a hint it was usually followed. Then later with v10 and v11 it progressively became more difficult and nowadays with v12 I'm having a really hard time (I usually have to use some "tricks" that don't change the logic of the SQL but do change its technical execution, like placing somewhere a useless but tactical "distinct", to confuse it enough so that my hints are more or less followed/used).
Btw., if you want to state something like "using hints is wrong" then in general I agree (with some exceptions for special cases) but currently I'm taking care of a very old app that uses often quite big SQLs (involving up to ~15 tables) and as the maintenance budget is limited and the app will anyway be decommissioned in 1-2 years I cannot start rewriting half of the application to break down and improve the statements => when once per quarter the data distribution/quantity/etc... change and the optimizer thinks that it has a new brilliant idea about how to exec some SQL and then of course the SQL hangs then I usually just try to spend 1-2 hours trying to find some hint(s) that will bring back the old execution plan, but since we upgraded to Oracle12 I often see no change in the exec plan unless I do what I mentioned above.
This is somewhat intentional I think to guide you toward buying Enterprise edition, which since 11G includes plan baselines ( see https://docs.oracle.com/cd/B28359_01/server.111/b28274/optpl... ) - which allows you to lock a SQL queryID to a specific plan.
At the company we're using EE, and I've heard about the plan locking functionality, but I never dared to use it.
Does it survive DB-restarts? Additionally we have a setup of an active/primary cluster that is replicated to a passive/secondary one in our secondary datacenter (which then becomes leading in case of a disaster in the primary datacenter) => I don't think that a locked plan is replicated to the secondary cluster (which, in a case of a disaster would become a 2nd disaster as many SQL all of a sudden would stop working).
> Btw., if you want to state something like "using hints is wrong" then in general I agree (with some exceptions for special cases) but currently I'm taking care of a very old app that uses often quite big SQLs (involving up to ~15 tables) and as the maintenance budget is limited
This is a very common truth, I've been in similar situations where budgets are tight and things are working so don't touch a thing. I think it's actually just a specific example of a very common problem pattern in tech - the way I usually push back on it is "The stuff that is is and I'm not going to waste time fixing it - but any stuff I'm adding a feature to or fixing a bug in, it's not worth the company's time to fix it the wrong way because it'll end up costing more when another bug or feature requires an adjustment near this in a year." All that said, especially within MySQL (and I haven't worked in Oracle so maybe there too) the query planner is a bit dumb, so sometime you really need to help it along.
> especially within MySQL (and I haven't worked in Oracle so maybe there too) the query planner is a bit dumb, so sometime you really need to help it along.
Yeah, I've seen it as well last year when working with MariaDB (but I'd guess that MySQL might be a bit more clever as maybe Oracle might have improved it a bit).
She’s pretty clear that she means the order things happen semantically. Just like in any programming languages we talk of things happening in some order, but under the ”as if” rule the compiler and processor can reorder things however they want.
Unless I'm writing 'select *', I always do the select portion of a SQL query last. Starting with from enables helps with autocomplete on most editors too. I actually with 'select' was at the bottom. The only reason it would be useful at the top would be if it allowed you to use the new/computed column names as aliases later in the query, which doesn't work with most databases.
Which you probably shouldnt, because you are just writing the subquery each time - a temp table in MSSQL is a much better solution than CTEs 99/100 times.
Correct, an inlined view - which again if you are referencing the view multiple times in the query, I would again offer a temp table as a likely performance improvement.
edit: I say this from experience fixing hundreds of CTEs that came down to "I want to simplify the downstream query so I am going to package a heck ton of relations in this cte and combine it with this other one to hide the complexity as I do some other stuff!"
It depends - generally you wouldnt ever drop a temp table because when it goes out of scope it dies.
If you wanted the CTE/temp table to re-evaluate the query for each select, yeah, you'd effectively have to drop/truncate - but my read on your code is that the subquery would turn into a temp table, be re-used across all your selects, then go out of scope, and disappear.
This is why I like Linq in C#. The syntax order corresponds to the logical order. This also makes queries more composable.
And a very practical advantage is that the editor can support syntax completion. SQL syntax cannot support that since you write the projection before the from-clause.
Have you checked out the LINQ expression support in F#? They cover all (I'm pretty sure) LINQ operators within an expression style (AKA not the method-call syntax).
This looks much better than the way it is done in C#.
I absolutely hate the SQL-ish LINQ expressions in C# code. It's really awkward when you make a query and need to materialize it. You either store the Queryable<> in a temporary variable and then convert it to a list or an array or whatever, or you have to glom parentheses around a big LINQ expression.
I prefer the approach of LINQ expressions over LINQ method chains, but in C# very few LINQ operators are exposed to the LINQ expression syntax. So if you start with expressions you often end up with a terrible hybrid of both.
Yeah, the lack of composeability in raw SQL is pure agony for me. The very fact that "UDFs area performance disaster" is considered acceptable for a SQL in 2019 is revoltingly laughable.
it's a constant source of pain for us DBAs too. i've just always heard that code reuse isnt an imperative for sql server, not to the same degree as it is on the application side.
I've found that CTEs help ameliorate this quite a bit, and it's especially exciting that pg will soon be removing the mandatory performance implications of CTEs in pg12 (fencing via CTEs will remain optional via a keyword switch, but in pg<12 CTE fencing is impossible to disable).
CTEs seems like a very nice middle ground for code reuse - enshrining them in views is nice for somethings but keeping them raw and composable is to my preference since application developers aren't losing any visibility of logic occurring within the composed in blocks... this is definitely not a simple problem to solve though and is a constant balancing act so there is some clear room for improvement.
If code reuse isn't imperative, then at least there ought to be an official recommended preprocessor that could do macros. Something comparable to the C-preprocessor.
I want a null-handling-equals macro, for example (god I hate three-value-logic so much).
In general, I love SQL. It's one of my favourite languages (or rather,
language families) to play and tinker with. But the fact that SELECT
comes before FROM has always ground my gears. Thanks to this article, I
now finally have a better explanation than “Dunno, feels weird” :-)
I see your point, but FROM-first SQL would provide better support for
autocomplete. Right now in e.g. psql when you write "SELECT " and press
<Tab>, it can't help you with the columns. But if you could write "FROM
users SELECT " and then press <Tab>, then the DB client could tell that
you probably want a column of the "users" table.
You can use the WITH clause if you prefer that style.
WITH
employee AS (SELECT * FROM EMPLOYEES),
department AS (SELECT * FROM DEPARTMENTS)
SELECT employee.name, department.name
FROM employee
INNER JOIN department ON employee.department_id = department.department_id
How does that help at all, though? When it comes time to write the final select, you still haven't established the meanings of the things you are selecting from until you write the from and join clauses.
Your IDE should be able to complete the columns at that point, so when it comes to typing SELECT employee.name, the IDE knows that name is a column of the EMPLOYEES table.
This works with IntelliJ and Datagrip, I don't know about other IDEs.
It shouldn't, though, because there is no table bound to that name to draw from at that point. You haven't added a from-list that is actually relevant to the current scope yet.
I totally believe that it gives the suggestions as you describe, what I'm saying is that in order for it do that, the completion must be making faulty assumptions about the way that scoping works in SQL.
SELECT as the first keyword also makes sense as an opening indicator of the SQL statement's type. "Start a new task that SELECTs data" (then "what [data] do I want" then "given these rules") instead of INSERT, UPDATE, etc.
From a source code structure perspective "SELECT foo ...;" sort of works like a function header: "function foo() { ... };"
+1. One of the few programming languages that allows you to "work backwards" from your desired result to the data / computation needed for that result.
A long time ago, I might have forgiven such rigidness. But, yeesh, that was 30 years ago. In 2019 if I've already tokenized it for you with FROMs and GROUP BYs, you're a computer, go figure it out. Many years of writing SQL prevents me from but the most rare ordering mistake, but it kinda grinds my gears just a little that it's still an issue.
I wish it looked like the underlying relational algebra expression that it represents, but unfortunately, that's too much for your average joe with no compsci background.
I'm a big SQL fan too. Like the author, I kind of intuitively got the order after years and years of writing sql but if the syntax had evolved into FROM then SELECT I could get how it would make more sense. As it stands most of my queries start out:
1. "SELECT *" because habits
2. Start writing the FROM/JOINs and table aliases
3. Go back and refine the SELECT to tableAlias.ColumnName
4. (OPTIONAL) Aggregates
5. Forget the WHERE clause and burn the optimizer's cached query plans to the ground! (j/k, add WHERE or just throw a TOP 500 into the SELECT)
Title is misleading as far as I can tell. The author was just surprised to realize that the syntax for SQL queries did not mirror the execution order of the various elements of the query.
It is a bit of a "well, duh" thing, but I've had my fair share of those with SQL through the years so I won't hold it against the author.
One of my more recent random discoveries was that I could join a subquery, ie
select x.a, y.b, y.c
from tableX x
join (
select id, sum(b) as b, count(b) as c
from tableY group by id
) as y on y.id = x.id
Again nothing special, just hadn't had the need for it before so didn't even think about the possibility.
That’s one of the insights that Everyone Should Have about RDBs and SQL. Relational databases are about, well, relations, in particular the sort of discrete relations that can be represented as a set of tuples. It doesn’t matter where those sets of tuples come from. From the disk? Great. Computed on the fly? That’s fine too. And by manipulating relations you get… new relations! What RDBs and SQL are actually about is composing relations in the way that, say, Haskell is about composing functions. And indeed the theory behind RDBs is called relational algebra.
I’m pretty sure the author was aware that the execution order was not the same.
What the author was not aware of was what the actual execution order is. This article is about them actually digging in to find out what that order was.
I have long wondered why we can write "ORDER BY 1" (meaning to order by the values in the first column), but it's illegal to say "GROUP BY 1". This article shows why that is.
`group by 1` is actually allowed in several SQL implementations, and there's no reason it can't be -- the same as with column aliases the db engine can just rewrite
would defeat something MySQL was getting wrong in a query that already wasn't ordered by anything. A coworker put it as "waving a dead chicken that doesn't even exist".
Of course they don't start with SELECT, which is also why SQL is a terrible language.
When you type the fields you want to select, there is zero context as to what those fields apply to, meaning that there is zero contextual help that you could get from know the table and group by ahead of typing select.
SQL is the most WTF notation/language I've ever seen... How the hell did someone start from the beauty of relational algebra and end up with the this misordered confusing and verbose abomination that we've all standardized upon?!
...even Mongo's JSON-based query language has more elegance to it. SQL seems designed on purpose to be annoying to write, hard to read, and never look like anything, not the math logic, not any query operations that could ever be executed. I may be superficially similar to English, but English is horrible, why would you model a notation after that?!
Everytime I see someone yelling that Haskell or Lisp syntax is weird and unintuitive I think they need to have their face bashed into a printout of a complex SQL query!
hah. famous last words. SQL is beautiful. I allows non-developers to actually work with data in ways more flexible and extensible that most notations/languages out there.
Who? I've done so many "can you write a query that gives me all the foo bars where so an so" for managers, cs people, or even data analysts, that it makes my skin crawl. Sometime they know how to load that data into Excel and do their magic there, but with SQL, anything that's more complicated than a simple select from a single table, forget about it.
I have designed/given internal SQL classes to business analysts, product managers and similar.
From simple selects using some common functions and basic types, to filtering, to aggregations and grouping and finally joins. That gets them about 80% there for the kind of information they want ad-hoc.
Only with joins did the people really struggle for some time.
In my opinion the distinguishing factor wasn't the language, but the peoples willingness to learn and ability to apply it in practice on a somewhat regular basis.
I suspect you're asking more figuratively, but I saw this PDF from Stonebraker on the developer of databases and query languages that literally answers your question[0].
I've always done data heavy work, but as a recently hired data engineer, I'm really diving deeply into this layer that previously I had taken for granted. In doing so, I find SQL highly expressive and beautiful for data work; much moreso than Python, my other primary language.
Which languages do you feel manipulate data beautifully?
...that would match rel algebra notation nicely, and extend to complex cases more like:
( employees e *{e.id = a.employee_id} addresses a )
| filter{a.city.name = "London"}
| [e.name, a.city.name]
...you can replace operators with words of course. Or you could represent this as a JSON structure, even better imo. But the point would be to have a notation highlighting the idea of a "product", of restricting and specializing that product or specifying how it's made, and of indexing into its fields. The way I see it in my mind is like taking "a (maybe generalized) cartesian product" of different things, then filtering this, then zooming on each dot/point/object and filtering its fields etc. And obviously focusing on piping/chaining stuff.
Maybe the notation I suggest is not a good one, but I'd care more about making the concepts obvious and readable and composable.
I learned this and more earlier from https://blog.jooq.org/2016/03/17/10-easy-steps-to-a-complete...
my goto guide and recommendation for people with practical SQL experience who want to improve their understanding. (I first read it via Hadley Wickham's tidy data writeup.)
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_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.)
Thanks 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.
Unless 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.
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:
> 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.)
For 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
I'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.
This 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.
DBeaver has something even better, albeit sometimes annoying: you can define a connection as "Production" (highlighted red) and "Test" (green). On Production systems, DBeaver will ask you for confirmation for every write statement.
For destructive statements I almost always do a select with the WHERE statement that I will use in the delete first so I can see I am not doing anything stupid.
Yeah i've made a whoops like this before. I usually start a n update with a select statement to make sure my assumptions about the intent of the change is on point.
This saves you only if you delete directories. Better start with `ls`. The one with git force push is spot on though. This is one of the reasons to prefer tools that let you write options at the end of the argument list instead of after the command name.
I wish SQL would die as a query language. This is one of the reasons why, writing a query in the syntactic order is counter to how you should reason about getting and shaping your data.
I came to this realization by accident shortly after using various ORMs and database mapping tools.
Being able to break down and compose a query in your own defined order while leveraging a library to transform it into a valid query is quite nice for that way of thinking.
Well, processing doesn't but the beauty of the language is that it starts with what the user wants as the output and the details of how it is pulled follows later.
"SQL queries don't start [optimizing] with SELECT"
The title is missing a word. Interestingly, part of the industry is moving towards databases that optimize themselves (ex Snowflake & Oracle Autonomous Database), plus projects that do it for you (Apache Calcite).
With a bit of an exception for the era where we were all hard wiring indexes in MySQL using hints... that's what databases should be doing. DB interactions are supposed to be done using a purely declarative language - postgres has stuck by this the strongest and has been able to make the biggest optimizations in (IMO) a large part due to how little of the internals they've exposed. With pg12 they're making the optimization fences in WITH clauses an accepted give on this point and the discussion on whether to do this or not went back and forth quite a bit.
I've actually found myself more and more disappointed that FROM isn't the first clause in the query, it'd be really nice to express those queries as WITH, FROM, GROUP BY, HAVING, WHERE, SELECT, ORDER BY, LIMIT as that's usually the way I consider queries - granting HAVING is sort of a free card here, I don't use it enough to place it accurately, but being near WHERE or GROUP BY makes a lot of sense.
A professor I took a databases course from in college was convinced XML was the next big thing, so he made us also learn the XQuery language [1], which does things in roughly the order you described.
I don't remember the specifics, just that the acronym was something like "flower" and you started with your from clause and ended with what you would return.
Or rather, why have the language limit you on the order of the operations? Why can't you for example do a LIMIT and then a WHERE? This can currently only be accomplished by using subqueries. I've been thinking of developing a different query language that transpiles to SQL that allows you to more-or-less reorder these operations arbitrarily. You can easily do that in any regular programming language so why not SQL?
263 comments
[ 4.5 ms ] story [ 271 ms ] threadedit:
CTEs can make it closer to the syntax in question though.
If from was first, autocomplete could help you with the names of columns, like RStudio does with dplyr:
Because you start by piping the data reference into your select function, RStudio can inspect the data and autocomplete the column names in the select statement, completely opposite of what is possible inSQLWith a free order, you'd be able to start SQL queries with `FROM table_name SELECT ...` and have columns autocompleted.
I'm really glad the accompanying article has a bunch of qualifications of the form "Database engines don’t actually literally run queries in this order" but a lot of the beauty of these diagrams is that they work on their own.
I wish the title of the picture said something more like "SQL queries are interpreted in this order" to make it clear we're talking about semantics and not what actually takes place.
If your code may run from 3m to 3 hours, its probably the SQL at fault.
In my experience, you have one of three scenarios:
You know ahead of time exactly the problem you are going to solve and so probably shouldnt choose SQL from a performance perspective.
Your problem size changes and you get to pick a simple system that you understand well and has predictable performance but might be slower than other options.
Or a complex system that's less well understood, and generally has great performance and productivity up to a tipping point, where the abstraction has leaked too much.
In your case - your query sounds exactly like those heuristics worked up to a point, and then finally failed. Is that the optimizers "fault"? It's mostly a trade off between the time spent making the plan and the time spent getting your data back - and again, sometimes those tradeoffs are wrong too.
(This is assuming whatever applicable regular maintenance (stats, index or table reorgs if needed) is done prior to the time-sensitive batch... and understanding sometimes there's just not enough hours in a day to run batch AND maintenance :| )
SQL and other declarative languages can be frustrating to talk about because what you tell it you want and _how_ it does the work isn't supposed to matter. So there are two questions that you might answer with either jvsn's answer (how should you imagine it works to understand how to use SQL?) and your answer (how should you imagine it works to figure out why it's slow?). Someone learning SQL is probably asking the first question, somebody debugging performance is probably asking the second one.
So this is a weird criticism because neither of you is incorrect, it's just that different audiences require different kinds of detail. If she instead wrote the answer to your question then someone else would inevitably come along with a comment nearly identical to yours except it would say "well that's just implementation details but you should instead imagine that...".
Yep! Article is a big improvement; first I saw the image was earlier today when it was tweeted on its own here: https://twitter.com/b0rk/status/1179449535938076673?s=20 and her images are often awesome because they capture nuance in a tiny poster
is it only my (wrong) subjective feeling, or did the one of Oracle become much more stubborn about following "hints"? https://en.wikipedia.org/wiki/Hint_(SQL)
Reason: I started dealing with Oracle when it was at v8 and I think up to including v9 when I specified a hint it was usually followed. Then later with v10 and v11 it progressively became more difficult and nowadays with v12 I'm having a really hard time (I usually have to use some "tricks" that don't change the logic of the SQL but do change its technical execution, like placing somewhere a useless but tactical "distinct", to confuse it enough so that my hints are more or less followed/used).
Btw., if you want to state something like "using hints is wrong" then in general I agree (with some exceptions for special cases) but currently I'm taking care of a very old app that uses often quite big SQLs (involving up to ~15 tables) and as the maintenance budget is limited and the app will anyway be decommissioned in 1-2 years I cannot start rewriting half of the application to break down and improve the statements => when once per quarter the data distribution/quantity/etc... change and the optimizer thinks that it has a new brilliant idea about how to exec some SQL and then of course the SQL hangs then I usually just try to spend 1-2 hours trying to find some hint(s) that will bring back the old execution plan, but since we upgraded to Oracle12 I often see no change in the exec plan unless I do what I mentioned above.
At the company we're using EE, and I've heard about the plan locking functionality, but I never dared to use it.
Does it survive DB-restarts? Additionally we have a setup of an active/primary cluster that is replicated to a passive/secondary one in our secondary datacenter (which then becomes leading in case of a disaster in the primary datacenter) => I don't think that a locked plan is replicated to the secondary cluster (which, in a case of a disaster would become a 2nd disaster as many SQL all of a sudden would stop working).
But thanks for the hint :)
This is a very common truth, I've been in similar situations where budgets are tight and things are working so don't touch a thing. I think it's actually just a specific example of a very common problem pattern in tech - the way I usually push back on it is "The stuff that is is and I'm not going to waste time fixing it - but any stuff I'm adding a feature to or fixing a bug in, it's not worth the company's time to fix it the wrong way because it'll end up costing more when another bug or feature requires an adjustment near this in a year." All that said, especially within MySQL (and I haven't worked in Oracle so maybe there too) the query planner is a bit dumb, so sometime you really need to help it along.
Yeah, I've seen it as well last year when working with MariaDB (but I'd guess that MySQL might be a bit more clever as maybe Oracle might have improved it a bit).
I'm soooo gonna miss that when we have to add MSSQL support soonish. Having to repeat myself is tedious, especial for non-trivial expressions.
Are CTEs an optimization barrier in MSSQL? I would've thought that the query planner could move the execution plan nodes across the CTEs.
They are not. They are, however, in Postgres.
edit: I say this from experience fixing hundreds of CTEs that came down to "I want to simplify the downstream query so I am going to package a heck ton of relations in this cte and combine it with this other one to hide the complexity as I do some other stuff!"
We do a lot of queries like
Typically a lot more going on than this, but this gets the point across I think. Would we have to drop for each select then?If you wanted the CTE/temp table to re-evaluate the query for each select, yeah, you'd effectively have to drop/truncate - but my read on your code is that the subquery would turn into a temp table, be re-used across all your selects, then go out of scope, and disappear.
And a very practical advantage is that the editor can support syntax completion. SQL syntax cannot support that since you write the projection before the from-clause.
See https://docs.microsoft.com/en-us/dotnet/csharp/programming-g...
I absolutely hate the SQL-ish LINQ expressions in C# code. It's really awkward when you make a query and need to materialize it. You either store the Queryable<> in a temporary variable and then convert it to a list or an array or whatever, or you have to glom parentheses around a big LINQ expression.
CTEs seems like a very nice middle ground for code reuse - enshrining them in views is nice for somethings but keeping them raw and composable is to my preference since application developers aren't losing any visibility of logic occurring within the composed in blocks... this is definitely not a simple problem to solve though and is a constant balancing act so there is some clear room for improvement.
I want a null-handling-equals macro, for example (god I hate three-value-logic so much).
SQL is a declarative language, very intentionally. There is no way to infer operational semantics (order of execution) from a query.
SQL has a terrible grammar, as most everyone agrees, but this article doesn't explain it well.
For some reason that made sense to me.
This works with IntelliJ and Datagrip, I don't know about other IDEs.
From a source code structure perspective "SELECT foo ...;" sort of works like a function header: "function foo() { ... };"
- 1. create a bunch of constructs to work towards that goal -
- 2. work happens -
- 3. finally get my output and have what i want -
OTOH FROM first is better for tooling.
Really, there's no really good reason for a mandatory order of clauses in an SQL statement.
The thing I always imagine is a bunch of people in the 1970ies sitting around designing this stuff, and reading it out loud in a Captain Kirk voice
"Computer, SELECT course WHERE Klingons IS zero!"
1. "SELECT *" because habits
2. Start writing the FROM/JOINs and table aliases
3. Go back and refine the SELECT to tableAlias.ColumnName
4. (OPTIONAL) Aggregates
5. Forget the WHERE clause and burn the optimizer's cached query plans to the ground! (j/k, add WHERE or just throw a TOP 500 into the SELECT)
Great article, thanks.
It is a bit of a "well, duh" thing, but I've had my fair share of those with SQL through the years so I won't hold it against the author.
One of my more recent random discoveries was that I could join a subquery, ie
Again nothing special, just hadn't had the need for it before so didn't even think about the possibility.What the author was not aware of was what the actual execution order is. This article is about them actually digging in to find out what that order was.
When you type the fields you want to select, there is zero context as to what those fields apply to, meaning that there is zero contextual help that you could get from know the table and group by ahead of typing select.
...even Mongo's JSON-based query language has more elegance to it. SQL seems designed on purpose to be annoying to write, hard to read, and never look like anything, not the math logic, not any query operations that could ever be executed. I may be superficially similar to English, but English is horrible, why would you model a notation after that?!
Everytime I see someone yelling that Haskell or Lisp syntax is weird and unintuitive I think they need to have their face bashed into a printout of a complex SQL query!
From simple selects using some common functions and basic types, to filtering, to aggregations and grouping and finally joins. That gets them about 80% there for the kind of information they want ad-hoc.
Only with joins did the people really struggle for some time.
In my opinion the distinguishing factor wasn't the language, but the peoples willingness to learn and ability to apply it in practice on a somewhat regular basis.
I've always done data heavy work, but as a recently hired data engineer, I'm really diving deeply into this layer that previously I had taken for granted. In doing so, I find SQL highly expressive and beautiful for data work; much moreso than Python, my other primary language.
Which languages do you feel manipulate data beautifully?
[0] - https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/pape...
But I'd just want to write code like:
...that would match rel algebra notation nicely, and extend to complex cases more like: ...you can replace operators with words of course. Or you could represent this as a JSON structure, even better imo. But the point would be to have a notation highlighting the idea of a "product", of restricting and specializing that product or specifying how it's made, and of indexing into its fields. The way I see it in my mind is like taking "a (maybe generalized) cartesian product" of different things, then filtering this, then zooming on each dot/point/object and filtering its fields etc. And obviously focusing on piping/chaining stuff.Maybe the notation I suggest is not a good one, but I'd care more about making the concepts obvious and readable and composable.
To dig deeper: https://en.wikipedia.org/wiki/Query_plan https://www.khanacademy.org/computing/computer-programming/s...
so, SQL is not about how you're going to get the results it's about what results you want.
It leaves out SQL window functions though.
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_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.)
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:
Disclaimer: I built this.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.)
It doesn't save me time, but a lot of nerves.
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.
Everything is non-destructive without an explicit COMMIT.
Then if you make an error you can always ROLLBACK. If it looks OK -- COMMIT.
Similarly `git push ... -f` etc.
--safe-updates
to avoid queries running without WHERE clause.
Yep. As developers/designers we need to understand this as much as we wished our clients did.
Interesting because LINQ starts the same way, or at least that's how I remember it.
The reason is for that is Intellisense, you cannot show columns for SELECT unless you already know the table(s) you're selecting the columns for.
Being able to break down and compose a query in your own defined order while leveraging a library to transform it into a valid query is quite nice for that way of thinking.
I generally dislike having multiple ways to achieve the same end result.
The title is missing a word. Interestingly, part of the industry is moving towards databases that optimize themselves (ex Snowflake & Oracle Autonomous Database), plus projects that do it for you (Apache Calcite).
I don't remember the specifics, just that the acronym was something like "flower" and you started with your from clause and ended with what you would return.
[1] https://www.w3schools.com/XML/xquery_flwor.asp