19 ms·

SQL queries don't start with SELECT

• bena 7y ago
  I really wish SQL syntax was in this order.
  • tracker1 7y ago
    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.
  • knodi123 7y ago
    Why do SQL clauses need to be ordered, anyway? Is there anything that would be impossible to parse if the order was not defined?
    • cm2187 7y ago
      To be logical to beginners. So that autocomplete can help you (which it cannot if it doesn't know what tables you are querying).
      • wodenokoto 7y ago
        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.
        • cm2187 7y ago
          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.
          • wodenokoto 7y ago
            Then I misunderstood you post.
  • parshimers 7y ago
    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.
• Gormisdomai 7y ago
  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.
  • SketchySeaBeast 7y ago
    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".
    • fennecfoxen 7y ago
      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.
      • hobs 7y ago
        Query Hints. Simplifying code. Compiled stored procedures. Plan guides/fixed plans. If your code may run from 3m to 3 hours, its probably the SQL at fault.
        • SketchySeaBeast 7y ago
          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.
• xeonoex 7y ago
  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.
  • deleted 7y ago
    [deleted]
  • magicalhippo 7y ago
    > 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 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.
    • jfim 7y ago
      As far as I know, it supports the WITH keyword, so you can reuse those expressions.
      • magicalhippo 7y ago
        Ah, well still a bit more tedious as far as I can see, but better than nothing. Thanks!
      • hobs 7y ago
        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.
        • jfim 7y ago
          It's not any different than selecting from a view. Are CTEs an optimization barrier in MSSQL? I would've thought that the query planner could move the execution plan nodes across the CTEs.
• goto11 7y ago
  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.
  • nick_ 7y ago
    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).
    • d--b 7y ago
      The "from" syntax also exists in C# See https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/query-syntax-and-method-syntax-in-linq https://docs.microsoft.com/en-us/dotnet/csharp/programming-g...
    • thrower123 7y ago
      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.
      • goto11 7y ago
        The SQL-ish syntax in C# is pretty neat if you have joins or lets or multiple from's. But for simpler cases I also prefer the method-call syntax.
      • nick_ 7y ago
        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.
• gowld 7y ago
  Misleading, and the author even explains that, despite not dull understanding it: "queries aren’t actually run in this order (optimizations!)" 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.
• ainar-g 7y ago
  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” :-)
  • duxup 7y ago
    I always liked select first as it was "what do I want" then "given these rules". For some reason that made sense to me.
    • ainar-g 7y ago
      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.
      • jfim 7y ago
        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
        • clhodapp 7y ago
          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.
          • jfim 7y ago
            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.
• jbverschoor 7y ago
  Funny. Iirc, the ejb1 query language was like “from xxx”. Not entirely sure though
  • jbverschoor 7y ago
    Oh wait I think it was hql from hibernate
• bodyloss 7y ago
  A minor thing, but adding the non-image version is a really nice touch! Great article, thanks.
• magicalhippo 7y ago
  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.
  • thescribbblr 7y ago
    I wasn't knowing this before. Thanks for sharing!
  • DasIch 7y ago
    You can also have a subquery in from as in select * from (select 1) as temp;
  • goto11 7y ago
    Everywhere you can have a table expression, you can replace it with a subquery.
    • Sharlin 7y ago
      Indeed one can think of a table expression as the trivial subquery SELECT * FROM table …well, modulo the fact that in a concrete sense that substitution would lead to infinite recursion!
  • Sharlin 7y ago
    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.
• parliament32 7y ago
  Interesting stuff. One of those things you don't really think about, but when it's explained, it seems intuitive.
• csours 7y ago
  Of course not, they start with WITH! Preferably 8 withs that reference each other.
• CWuestefeld 7y ago
  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.
  • jvns 7y ago
    `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 select name, count(*) from table group by 1 as select name, count(*) from table group by name before executing the query
    • erik_seaberg 7y ago
      I will never forget Percona telling us ORDER BY NULL 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".
• d--b 7y ago
  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.
• nnq 7y ago
  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!
  • mirceal 7y ago
    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.
    • dsego 7y ago
      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.
      • northwest65 7y ago
        IME, third line support staff for a start.
      • smoe 7y ago
        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.
• mirceal 7y ago
  so this depends on the database engine and while in general it may follow a pattern there are no guarantees. To dig deeper: https://en.wikipedia.org/wiki/Query_plan https://en.wikipedia.org/wiki/Query_plan https://www.khanacademy.org/computing/computer-programming/sql/relational-queries-in-sql/a/more-efficient-sql-with-query-planning-and-optimization 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.
• lugg 7y ago
  I thought this was obvious? (Serious, not snark) You start explaining what you need and work backwards, on the other end, they work in reverse, because that's how it's stored. Conceptually, it's like piping in bash, "find | grep | cut". Start with finding the files(tables), grep for the row data (where), cut the fields you want returned (select).
• em500 7y ago
  I learned this and more earlier from https://blog.jooq.org/2016/03/17/10-easy-steps-to-a-complete-understanding-of-sql/ 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.) It leaves out SQL window functions though.
• 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 ago
    I'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 ago
      Nice 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 ago
        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.
• clatan 7y ago
  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.
• belltaco 7y ago
  >LINQ (a querying syntax in C# and VB.NET) uses the order FROM ... WHERE ... SELECT 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.
  • noveltyaccount 7y ago
    Came here to say this. It makes a heck of a lot of sense when you think about it!
• Rerarom 7y ago
  My database professor in university always used to say "the FROM, not the SELECT, is the most important".
• nickjj 7y ago
  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.
• riantogo 7y ago
  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.
• starpilot 7y ago
  Fuck this opacity. When will a database add seamless translation of Pandas to SQL? It is a thousand times the better syntax.
  • avip 7y ago
    Like spark has? personally I found it just confusing. Why would I need a "no sql" syntax, when sql has such a perfect syntax for what it does? I generally dislike having multiple ways to achieve the same end result.
• iblaine 7y ago
  "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).
  • munk-a 7y ago
    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.
  • kragen 7y ago
    She wasn't saying that's not where optimizing starts; she's saying that's not where the dataflow starts, conceptually.
• munk-a 7y ago
  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.
  • war1025 7y ago
    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. [1] https://www.w3schools.com/XML/xquery_flwor.asp https://www.w3schools.com/XML/xquery_flwor.asp
    • iamthepieman 7y ago
      FLOWR - from, let, order by, where, return.
      • gigatexal 7y ago
        Or flows just replace the r with the s for select
  • kccqzy 7y ago
    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?
  • wolco 7y ago
    An oem is perfect for that use-case and most allow chaining of the above in any order.
• Scarbutt 7y ago
  You can even think about the joins "imperatively". JOINs are imperative.
  • atroche 7y ago
    How would you describe the joining of two tables more declaratively than how SQL does it?
    • Scarbutt 7y ago
      more declaratively I'm just saying they are not declarative since you have to explicitly describe how each table is joined to the next, the operation goes beyond the query itself.
      • yellowapple 7y ago
        I think we have different definitions of "declarative", since I consider SQL's join syntax to be pretty darn declarative. You're declaring the relationship between two tables. You're not telling the database how to spool through each table and match up values; you're just telling it which values need to match up by which constraints.
    • baddox 7y ago
      Perhaps a “fake table” (e.g. a view) that is the result of two tables being joined but presents itself as a normal table with the expected columns? That would be a very declarative way of querying joined tables, but yeah, you’d still have to create the views or have some means of declaring the appropriate relationships between tables. ORMs sort of let you do this, like in Django where you declare that a User has many Permissions and the ORM knows how to generate that JOIN query for you.
      • benjiweber 7y ago
        Something like SQL's NATURAL JOIN? Essentially a relational "AND" https://en.wikipedia.org/wiki/Join_(SQL)#Natural_join https://en.wikipedia.org/wiki/Join_(SQL)#Natural_join
• anon1m0us 7y ago
  > (I really want to find a more accurate way of phrasing this than “sql queries happen/run in this order” but I haven’t figured it out yet) Maybe "Order of Operations"? Similar to order of operations in math where you do (), then * and /, then + and -, ... https://en.wikipedia.org/wiki/Order_of_operations https://en.wikipedia.org/wiki/Order_of_operations
• Pxtl 7y ago
  This is one of the things MS Entity Framework gets right - it enforces the "start with FROM". It actually ends with SELECT, which makes so much more sense. Seriously, between navigational properties, group-with-children, EF to me hammers home how bad SQL is. EF is a square peg in a round hole but it does a great job at makign some trivial improvements over SQL, while still remaining true to the principles of relational databases. Of course, the framework is a hairy mess for other reasons (lazy loading, untranslateable methods, ugly generated queries) but the alternative to SQL it presents is lovely.
  • munk-a 7y ago
    I do prefer this structure, but it differing for this one product is more of a cost than I'd like to pay for it. SQL is wonderful because so much of it is platform independent - nearly everything can be expressed in a common SQL manner and, in practice, I tend to avoid dialect specific SQL whenever possible - this just ups the comprehension immensely. There are a bunch of tech that's tried to reinvent the wheel with SQL like Redis and Splunk... both of those systems are just a pain to interact with - their variants have strengths, sure, but they lose the ease of declaration and universality of SQL. I'd rather see a SQL variant emerge (if it were to) as a logically compatible declarative language that various DB vendors can add support for.
    • nightski 7y ago
      The thing is that LINQ's purpose is not to reinvent SQL. While EF has an adapter which converts LINQ expressions to SQL, you can use LINQ against objects and other sources for which it is incredibly useful. In fact, it's better suited to those tasks as it doesn't map 1:1 with SQL. But as someone who uses EF and LINQ on a daily basis, it is possible to learn how the mapping is performed and get the results you want consistently even in some pretty advanced scenarios.
      • scarface74 7y ago
        Side note, I fell in love with the Mongo LINQ driver. There is no impedance mismatch. While I never used LINQ to avoid learning sql, I would create my complex Mongo queries in LINQ first.
• faizshah 7y ago
  In my experience the execution order isn’t obvious to most sql users. You’ll find a lot of people using sql who don't understand why something from SELECT can be used in ORDER BY but not WHERE or HAVING for example. It takes a slightly more advanced SQL understanding to be able to explain these kinds of errors in thinking. Thats why a good ORM can often be the best choice for users.
  • chias 7y ago
    Can't you though? SELECT 2+2 as num FROM <table> HAVING num > 3; this will return as many 4s as there are rows in the table SELECT 2+2 as num FROM <table> HAVING num > 4; this will return 0 rows. How is this not using something from SELECT in a HAVING clause?
    • faizshah 7y ago
      Like with many SQL questions this depends on what implementation you're using. In MySQL you can use HAVING, ORDER BY, and GROUP BY with column aliases but not WHERE: https://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error https://stackoverflow.com/questions/942571/using-column-alia... In PostgreSQL you can not use the column alias within WHERE or HAVING but you can use it in ORDER BY and GROUP BY: https://dba.stackexchange.com/questions/225874/using-column-alias-in-a-where-clause-doesnt-work https://dba.stackexchange.com/questions/225874/using-column-... Similarly in Microsoft SQL server you cannot use it in WHERE, HAVING, or GROUP BY but you can use it in ORDER BY. In SQLite you can use column aliases within WHERE, HAVING, ORDER BY and GROUP BY: https://stackoverflow.com/questions/10923107/using-a-column-alias-in-sqlite3-where-clause https://stackoverflow.com/questions/10923107/using-a-column-... In Google Bigquery you can use them in GROUP BY and ORDER BY but in Hive you can only use them in ORDER BY. As far as I know the standard only defines that you cannot use column aliases in the WHERE clause. I'm sure someone else can chime in with what the standard says about column aliases in ORDER BY, GROUP BY and HAVING.
    • emmelaich 7y ago
      HAVING applies to groups. Sqlite3 doesn't allow HAVING without GROUP BY. If a sql dialect doesn't require the GROUP BY then I think the entire select result is one group. So you'd only get either 1 or 0 rows ever. Or so I think :-)
• phs318u 7y ago
  Maybe I’m just showing my age here, but has use of SQL really fallen so far out of favour that very few developers know it well? I’m honestly surprised.
  • EastSmith 7y ago
    I've wrote CRUD apps for 5 years very long time ago. Then I was spending weeks debugging business logic in stored procedures and views, fine-tuning big query execution plans, you name it. At my current work nobody talks about SQL, literally every discussion starts with "We will create this new Rails model". Indexes are not created deliberately - we are adding them only when the need is obvious, because some functionality is plain slow. No one talks about SQL, plain SQL queries in code are considered really bad practice - in cases where the query generated by Rails is complex and slow and a plain SQL query is PR-ed, the code reviews discussions are pretty long. I am not saying this is good or bad, in fact all looks OK, but I was really really surprised at the beginning.
    • laumars 7y ago
      I once worked with a guy who would generate SQL queries via a series of multiple regex transformations. Even now that's still up there as one of the most terrifying things I've seen a developer do.
      • phs318u 7y ago
        Why terrifying? Sounds like he saw that the structure of much of what he was doing was boilerplate and created a bespoke code generator to save himself time. As long as the resulting queries are readable and effective I don't see a problem. Unless I'm missing something, understanding his code generator is not (necessarily) a pre-requisite for understanding the resulting code.
        • deleted 7y ago
          [deleted]
        • 7y ago
• ineedasername 7y ago
  drop schema if exists * Doesn't start with select; you never have to write sql again; you're probably fired
  • hirako2000 7y ago
    I did this, on a very very large set of DBs.The boss moved me to the backup team.
    • OJFord 7y ago
      Hopefully not before restore!
    • ineedasername 7y ago
      I didn't drop a schema, but I did, um, accidentally get over aggressive with a merge-purge duplicate process. By which I mean that I merged every single record, all of them, into a single record. I was very fortunate, we had backups and restoring went smoothly, and my boss very calmly asked me one thing: "did you learn from this?" I assured him that I had.
• deleted 7y ago
  [deleted]
• perl4ever 7y ago
  My SQL queries generally start with WITH. Or they did when I was using Oracle.
  • scomp 7y ago
    To be honest I don't think the article highlights very well where this applies. I am a SQL Developer with SQL Server right now and in the past using Teradata I vaguely remember an aliased column could be referenced in the where clause if you didn't use the AS keyword.
• MiSeRyDeee 7y ago
  I'm a bit of surprised by how shallow people's understanding of database is. Especially for someone have literally wrote thousands of sql queries. The query engine implementation is/should be covered by some extent in any of the database courses, thus I assume anyone with a CS degree shouldn't be surprised that a queries doesn't execute from SELECT(how's that even possible). Perhaps nowadays the major users of SQL don't really have a CS background.
  • LegitShady 7y ago
    There are a lot of semi "power users" with little CS background that write simple SQL queries for their work. I think the main difference is in the level of curiosity/caring the person exhibits when they get a result you don't expect. When they do something that they think should work and it doesn't, do they carefully figure out the system behind it and why? The users that do will write better queries faster, and the others will will just keep getting frustrated and foist the work onto others.
    • MiSeRyDeee 7y ago
      I got slightly shocked while I do appreciate his curiosity of the db engine and made it to an informative article. To me, data analytics jobs will be more exposed to non-CS people so SQL will be much more written by non experts. The autonomous database might be the right product towards this future.
      • LegitShady 7y ago
        Well I can tell you that I am a non-CS but other engineering background person who learned SQL and python to get his job done more efficiently. That's why I say its fundamentally about a person's curiosity and ability to self teach. I too think its great people are learning in a way that is easy to absorb. I had to figure that out from an oracle manual and it wasn't great.
  • WhompingWindows 7y ago
    I know a lot of healthcare analysts who don't have a proper CS background but MUST use SQL to get data out of govt databases. There's a LOT of inefficient querying going on, and I was definitely guilty of that as a beginner.
• the_watcher 7y ago
  The author has been posting some pretty awesome SQL content on Twitter recently. I'm a data scientist who uses SQL pretty frequently, and I'm learning lots of stuff - from random tidbits like this that clarify something I think I intuitively knew to things that immediately became useful to me. https://twitter.com/b0rk https://twitter.com/b0rk
• chx 7y ago
  The posts, tutorials and zines of Julia Evans are amazing. She has a knack for explaining things. This one is also quite great, isn't it? It doesn't talk about relational algebras, sets, cartesian products etc but explains things in a very easy to understand fashion while pointing out common problems and pitfalls. I wish she did a monad one, you bet it wouldn't be "it's just a monoid in the category of endofunctors, what's the problem".
  • danellis 7y ago
    There are already many, many monad tutorials. If you want something visual, perhaps this one? http://adit.io/posts/2013-04-17-functors,_applicatives,_and_monads_in_pictures.html http://adit.io/posts/2013-04-17-functors,_applicatives,_and_...
    • inopinatus 7y ago
      Maybe showing my age, but I'm incapable of reading the phrase "If you want something visual" without hearing it in Tim Curry's fruity Sloane Square tones.
    • ant6n 7y ago
      This looks like a bunch of jibberish to me, mostly pretending it's easy by making it look like a comic.
      • chx 7y ago
        I didn't want to be the one to say that... this is a terrible tutorial.
      • tempguy9999 7y ago
        It's pretty straightforward, but it does require knowing haskell style functions (which I struggled with for a while). The chatty tone is irritating but perhaps it does make it less intimidating. I read it before and think it's pretty good.
• nicwolff 7y ago
  More depth and detail in this post a couple of year ago on Lukas Eder's excellent SQL blog https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/ https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-tr...
• pvorb 7y ago
  > Can I filter based on the results of a window function? (no! window functions happen in SELECT, which happens after both WHERE and GROUP BY) Yes, you can by wrapping the entire SELECT in another SELECT (or use WITH) select * from (select ...) as x where ... This works because the result of a SELECT is a (virtual) table on its own. It was an eye opener when I saw it for the first time.
  • Macha 7y ago
    It's surprising to me that someone can run into window functions earlier than subqueries. I guess my background with MySQL which only recently supported window functions affected my perspective there.
    • firasd 7y ago
      Her question that starts the post is about doing it in a single query (no subqueries.) I kinda had this misunderstanding on Twitter too when responding to an earlier diagram of hers https://twitter.com/firasd/status/1172874054002307073 https://twitter.com/firasd/status/1172874054002307073
    • pvorb 7y ago
      I've had use cases for subqueries long before I knew about window functions. This was just the example from the article where subqueries provide a solution.
• manigandham 7y ago
  The reason why is historical. SQL (structured query language) originally came from SEQUEL (structured english query language) and was designed to emulate a normal English sentence as a query. 'SELECT columns FROM table' makes sense when you're asking it literally. However SQL and relational DB's have evolved since then and the English grammar is now awkward. It would be nice to see newer dialects accept the inverse order to help with tooling and structure.
  • kragen 7y ago
    I'm a native English speaker and both "from the top shelf select a book" and "select a book from the top shelf" are perfectly valid syntax, even if the second one is more common.
• chias 7y ago
  I'm confused, this doesn't seem right. This article, and several of the comments here, suggest that you cannot reference things calculated during SELECT in the HAVING field. But you can. As a quick demo, pick your favorite table and run: SELECT 2+2 as num FROM <table> HAVING num > 3; this will return as many 4s as there are rows in the table SELECT 2+2 as num FROM <table> HAVING num > 4; this will return 0 rows.
  • emehrkay 7y ago
    I came here to ask the same question. I use having to filter select a lot. I use BigQuery, but it should hold up
  • okonomiyaki3000 7y ago
    I was thinking the same thing. HAVING must be after SELECT. It doesn't really make sense otherwise.
    • faizshah 7y ago
      Long story short, relying on HAVING to run after SELECT is not a standard SQL behavior it is dependent on the implementation and what the optimizer thinks is best (more discussion below: https://news.ycombinator.com/item?id=21153870 https://news.ycombinator.com/item?id=21153870). The diagram in the OP is a pretty good general/semantic understanding of SQL execution order for writing queries.
  • thawaway1837 7y ago
    Isn’t this covered by the author in her example where she shows how an alias can still work because the DB engine can rewrite the alias as: SELECT 2 + 2 FROM <table> HAVING 2 + 2 > 3; SELECT 2 + 2 FROM <table> HAVING 2 + 2 > 4;
• deleted 7y ago
  [deleted]
• nick_urban 7y ago
  Elixir's ORM, Ecto, appears to be a lot like LINQ. You write pseudo-SQL queries starting with from. E.g. from u in User, where: u.id = 1, select: u.name
• jkhjhljhkj 7y ago
  just
• jkhjhljhkj 7y ago
  I just wanna see how this work
• baddox 7y ago
  I pretty much always start typing SQL queries with “SELECT *” because I know I’ll come back and figure that part out later. It’s similar to how I write Python list comprehensions. I almost always type “x for x in” from muscle memory, then get the collection part at the end correct, then go back and fix the beginning. My brain just won’t think in the other direction.
  • talaketu 7y ago
    Oh yeah I agree - the forward reference to attributes before the source is stated does feel backwards when writing the expression. Compare scala's for-comprehension that feels more natural: `for (x <- xs) yield x + 1;` Perhaps there's an argument that the shape of the result is defined up front, so it might be easier to comprehend the result if not the implementation.
  • petepete 7y ago
    Me too. "select *", a couple of new lines, "limit 30;” now fill in the middle.
  • mritchie712 7y ago
    I 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 It will even take a stab at aliasing the table for you. Disclaimer: I built this.
• kerng 7y ago
  If you use SQL server you can inspect the query plan, very useful.
• crorella 7y ago
  Qualify ?
• etrain 7y ago
  Relational database queries are supposed to be _declarative_. As a user, you're not supposed to think about the mechanics of execution because the database system is supposed to be able to decide how to execute your query using whatever magic it wants, as long as it satisfies the contract that it gives you the right answer. It's absolutely useful as a debugging tool to build up some semantic understanding of what the query means, and I encourage every database user to learn to use EXPLAIN, but relying on a mental execution model is borderline dangerous.
  • curryhoward 7y ago
    The author makes it crystal clear that she is not advocating this as a mental model for the execution of the query: Database engines don’t actually literally run queries in this order because they implement a bunch of optimizations to make queries run faster – we’ll get to that a little later in the post. So: - you can use this diagram when you just want to understand which queries are valid and how to reason about what results of a given query will be - you shouldn’t use this diagram to reason about query performance or anything involving indexes, that’s a much more complicated thing with a lot more variables She claims this is a useful tool to understand the denotational semantics of the query and which kinds of queries are allowed vs. not allowed—and she's absolutely right.
  • filleduchaos 7y ago
    A point that the article makes in multiple places, but I suppose it absolutely bore you repeating it once more.
• Tomis02 7y ago
  For bonus confusion points, DISTINCT happens after SELECT and before ORDER BY (and DISTINCT affects the usage rules for ORDER BY). Personally keep a printed copy of the actual logical order of operations, it comes in handy.
• theemathas 7y ago
  In my undergrad class, I was thought that the order is "FWGHOS" (pronounced, "fwog hose"), i.e., FROM, WHERE, GROUP BY, HAVING, ORDER BY, SELECT. (LIMIT was omitted from this.) This acronym has "ORDER BY" happen before "SELECT", but this article have them the other way around. Does this difference ever matter?
  • daxfohl 7y ago
    You don't have to include the orderby field in the result set so I'd say O is before S.
• rambojazz 7y ago
  Is this specific to Microsoft SQL server or is it the same for every database?
  • niceworkbuddy 7y ago
    Every database. It's because of logical order of execution, as explained in article. Eg. you cannot use `having` before `where` even if syntax of SQL is allowing you to do so.
• sebazzz 7y ago
  I'm always surprised how apparently not-deterministic SQL Server appears to be when it comes to performance. We use for our apps Kendo grids at work which allows you to create any report by dynamically choosing the filters and columns you want. On the server side a query is composed from the parameters the user chooses, including only the columns that are necessary. The data source for the query is a inline table valued function (TVF). At one point we had trouble with performance after going live, and it could not be explained by changes to the TVF. Even when we reverted the released TVF changes the issues kept happening. A day or so later I was with a database expert one day long optimising the query (may I plug the excellent free Sentry One tool here?). Eventually we had optimised the query. However, when we put the query back into the TVF performance was down again. The slightest things that changed affected the performance.
  • n4r9 7y ago
    I've had similar experiences. We're you able to generate the actual query plan in each case and see if it had been done differently?
  • avar 7y ago
    This is something that affects every RDBMS that exposes SQL. The SQL language describes what and not how, how specifically to retrieve your data is left to the implementation. The most common causes for this in MySQL (and I expect SQL Server too) is that often your query can be resolved on multiple indexes, and picking which one is a matter of a heuristic. On MySQL changing table statistics can change which index is used, which can drastically affect query performance. So a common poweruser optimization strategy (which should be used very carefully) is FORCE INDEX, also known as "I know better which index to use". More often than not I've found uses of FORCE INDEX that were more harmful than not. But an ever more insidious version of this is queries that retrieve cached data being faster. This is something you can generally only observe in production because what's in cache is a matter of other traffic on the server. So you can easily get cases where something looks much worse under EXPLAIN, but is faster. E.g. it can be faster to execute a "worse" query with no indexes than one with, if the one with no indexes happens to need data that's already in memory v.s. sitting on disk.
• annguyenfoss 7y ago
  Developers these days truly don’t know SQL. This blog post getting 500+ points on HN is a proof.
  • scarface74 7y ago
    One blog post getting 500+ points is about as much “proof” that developers don’t know sql as a comment that cites that as evidence is proof that HN users don’t understand statistics....
  • dragonwriter 7y ago
    > Developers these days truly don’t know SQL Has that ever not been true?
  • aladine 7y ago
    I think they don't know SQL enough. So even a simple fact blowed their mind. I'm also wowed by this article.
• tannhaeuser 7y ago
  No, no, that's wrong
• spicyramen 7y ago
  This totally makes sense, as from a cognitive perspective the table you will extract information is the first place you take a look
• mtts 7y ago
  I teach a first year introduction to sql course and this is exactly how we tell our students to construct their queries.
• Epopeehief54 7y ago
  That explains linq syntax.
• throwaway_pdp09 7y ago
  Actually I've got bad news. The where clause should be run before the select but for reasons of, I guess cheating at the benchmarks, oracle doesn't do that and MSSQL followed that. It is particulary nasty as it mostly works, so this (CTE to not clutter your schema) with data as ( select * from ( values(1), (2), (0), (4) ) as abc(d) ) select 1 / d from data -- where d <> 0 gives divide by zero. If you uncomment the where it then seems to work correctly. Well it doesn't. You cannot rely on this for MSSQL2005 or later. When the expressin grows more complex it blows up - I am saying this from losing days of work by finding out the hard way. I'll repeat myself, the expressions in the select and that in the where can be evaluated in parallel. Thank you Microsoft. Microsoft's solution to this is to expect the programmer to deal with it by skipping the evaluation of the expression with data as ( select * from ( values(1), (2), (0), (4) ) as abc(d) ) select case when d <> 0 then 1 / d else null end from data where d <> 0; The case prevents any possible division by zero. I've had this behaviour confirmed by Microsoft, and the supposed fix was their solution. Not coincidentally I'm looking at learning postgres. For this and other reasons I don't want to work with MSSQL any more.
• HeavyStorm 7y ago
  I usually begin my queries by typing "SELECT * FROM", and then go back to the * after I've done everything else. There's a bonus in doing that - you get auto completion from the editors on many cases if you've filled the from first.
  • edgarvaldes 7y ago
    This is exactly what I do, and for the same reasons.
  • mritchie712 7y ago
    I 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.
• AtlasBarfed 7y ago
  Yes, this is particularly annoying for autocompleting column names when writing SELECT (and DELETE) with INSERT and UPDATE the user provides a table as context for autocompletion of the column list. There's been a billion revisions of ANSI SQL and they've never provided alternate ordering options. Who cares about users?