16 ms·

I never realized this before but many excellent developers struggle with SQL beyond simple SELECT statements. I have a colleague who is by all accounts a deeply

• MaxBarraclough 7y ago
  > I suspect this was what attracted developers to noSQL databases like Mongo in the first place -- it's more attuned to a programmatic mindset. Well, it's more attuned to the dynamically typed mindset, sure. Programmers who understand the value of static type systems should understand the value of relational schemas.
  • andybak 7y ago
    I disagree. There's more to relationships that typing. I'm very pro-dynamic languages and still chafe at static typing but the wonder of the relational model fits nicely with my liking for declarative and functional approaches. (EDIT - and as another data point I dislike SQL's syntax. The semantics are bearable but the syntax just makes my brain melt)
    • MaxBarraclough 7y ago
      They're not identical concepts, but both relational schemas and statically typed programming languages provide assurances about basic structure. Mongo, and Python, offer no such assurances, and leave it to the developer to get it right. Agree that SQL's syntax is rather bad.
      • MaBeuLux88 7y ago
        MongoDB supports Schema Validation since MongoDB 3.2 (Dec. 2015) and JSON Schema since MongoDB 3.6 (Nov. 2017) so MongoDB can enforce types strictly if you want to at the collection level. NoSQL doesn't mean NoSchema or NoTypes. https://docs.mongodb.com/manual/core/schema-validation/#json-schema https://docs.mongodb.com/manual/core/schema-validation/#json...
        • MaxBarraclough 7y ago
          So they bolted it on eventually then. The relational databases went the other way and bolted-on schemaless data [0] [1]. We can still compare the schema-based and schemaless approaches. [0] https://www.postgresql.org/docs/10/datatype-json.html https://www.postgresql.org/docs/10/datatype-json.html [1] https://docs.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/functions/json-qu...
      • andybak 7y ago
        > both relational schemas and statically typed programming languages provide assurances about basic structure. Yes I get that but I was trying to make the point that there's more to the relational model than just assurances. It offers a richly entwined semantic structure and an elegant route to reducing duplication and the risk on inconsistency that comes with it. None of this is in tension with dynamic typing. (Also - dynamic languages aren't just for sloppy thinkers and cowboy coders. There's actual value that is lost in moving to static typing even if you think the trade-off is well worth it)
• fimdomeio 7y ago
  I struggle with lack of experience with SQL by always being told that I should always use an ORM or I would regret it in the future when I would change database technology. I'm in the future now and spend a lot of time debbuging the ORM and the sql statements it produces, when I could split that work in half by not using the orm at all. Would also have a lot more experience with sql so there would probably be less bugs in the first place.
  • btilly 7y ago
    If you're writing a CRUD application, an ORM saves a lot of headaches. If you're doing complex reporting queries, an ORM is strictly worse. And yes, I've seen developers, architects, and authors of ORMs that believed otherwise. They are wrong. As an example, very, very few ORMs can make the distinction between SELECT ... FROM foo LEFT JOIN bar ON foo.id = bar.foo_id AND bar.category_id = 5 LEFT JOIN baz ON bar.id = baz.bar_id ... and SELECT ... FROM foo LEFT JOIN bar ON foo.id = bar.foo_id LEFT JOIN baz ON bar.id = baz.bar_id AND bar.category_id = 5 ... (And if you get into things like analytic queries, just forget about it.)
    • jakearmitage 7y ago
      Curious, what is the difference here?
      • deleted 7y ago
        [deleted]
      • gtsteve 7y ago
        I believe they may have intended to write this query with INNER JOINs instead of LEFT JOINs. If so, the result of the query would be identical but the second one would likely have performance problems given the category_id filter is not being applied at the point where bar is joined. A clever database engine might find an optimisation but I wouldn't count on it. This illustrates the subtle problem that ORMs can introduce - all logical tests pass but issues emerge over time. I found that typically every ORM based problem was resolved by rewriting it using SQL. I used to use ORMs extensively, but my philosophy now is that you can make simple queries by using a query generator (i.e. connection.GetByID("MyTable", 100)) and complex queries have to be written by hand. I would not choose to use an ORM again.
• meddlepal 7y ago
  From my experience it's just unfamiliarity with the SQL language. I've spent thousands of hours writing PHP, Java, Scala, Kotlin, Python and Go and doing fairly complex things with them since I started programming. SQL maybe a few hundred hours? And off and on again rather than constantly so I don't always remember beyond the basics. The concepts make sense to me but the language always feels foreign.
• dirtydroog 7y ago
  I'd put myself in this category. I just never liked databases, they never interested me.
• hknd 7y ago
  I used to write SQL at Google/FB, and I did and still do struggle with it. I think it's because I usually touch SQL like once a month and by the time I need it I already forgot everything :shrug:
• vnorilo 7y ago
  I agree that this is in part why nosql became popular. I'm quite experienced with systems programming but databases were never central to my interests. So when I needed one for my personal stuff, I went with CouchDB, because it's just a very nice RESTful API around a data structure I understand well.
• corey_moncure 7y ago
  I'm an SQL hater in remediation. In a given week I might work with all of the following: SQL, C#, Python, JS (Kendo, Vue, React), XSL, bash, and more. I'm a quick learner and I pick things up fast, always have, I've got a deadline and I don't have the time or capacity to fully internalize the minutiae of all the technologies I have to work with. In other words I depend on the tools to show me the options at my disposal and construct syntactically correct expressions. What I really dislike about SQL is common to most systems I dislike, where the tools aren't discoverable to me. In SQL it boils down to the fundamental syntactical requirement to put the SELECT clause before the FROM clause. So I have to build up my statement in this weird spiral pattern where I change something deeper in before I know what I can SELECT in the first place. The ability to give tables shorthand names with MS-SQL, i.e. `left join [dbo].[sometable] st` ...is very helpful but I gather this isn't common to all dialects. Working with XML in SQL is a nightmare as the tool cannot tell you whether you can do obvious things like pass an sql:variable into nodes() until you actually query the server. (spoiler: you can't.) I vastly prefer the functional programming approach to working with data e.g. C#'s lambda style linq. The tool shows me all the pieces I have to work with, and all I have to do is piece them together the right way.
  • throwaway_pdp09 7y ago
    > In SQL it boils down to the fundamental syntactical requirement to put the SELECT clause before the FROM clause No offence but if you are at the level of struggling with the syntax then you should not be using SQL until you have more experience. There have been plenty of well-founded critiques of the crappy syntax of SQL, but it's not ultimately that hard. There are worse things you'll have to cope with such as the implications of 3-valued logic that comes with nulls. The giving-tables-alias-names feature has been standard probably since the very first standard was released. In some cases such as self-joins, it is necessary. If I can give you a piece of advice, arrange things using the 'with' clause and learn to break things down as simple as possible at each step. Let the optimiser sort things out for you. If performance is poor, look at the query plan. Also understand you don't have to write everything in one huge statement. Spool intermediate results off to temp tables if that helps (not @tables but #tables - @tables have problems with them). As for the XML, I guess that may be better handled outside the DB. IMO XML should never be made part of SQL. Good luck.
• pbowyer 7y ago
  I like SQL when I'm not writing reporting queries. GROUP BYs bite me (With MySQL 8 I end up reaching for the ANY_VALUE() function), and I end up with more subqueries than I feel I should need. When working with time-indexed data I feel I'm forcing the database to do something it doesn't want to. E.g. if I want to answer the query "How many sales are there per day this month?" and I want an entry for every day in the month, even when there were zero sales. Or another query asking "Which days didn't have sales?" and listing the days. I haven't found a way to do this in-database. I end up answering in code based on the data I fetch. Definitely feel it's me not SQL, but not found the answer.
  • sk5t 7y ago
    A more functional mindset can definitely help here. Think of your "sales per day" model in terms of starting with a sequence of days--startingDay up to startingDay + n--as the input to a function that maps to an aggregate of that day's activity. Aggregate functions in SQL are IMHO quite awesome once you develop a comfort level to stop worrying about them per se. I wouldn't like to try to get Excel to tell me--or write the code to do manually--something like "show me the standard deviation in units sold by day of week over the last ten summers."
    • pbowyer 7y ago
      I find thinking about analytic queries like these as map/filter/reduce easier than thinking in terms of the SQL!
      • taffer 7y ago
        SQL and functional programming constructs are actually quite similar: SELECT -> map FROM -> stream JOIN -> flatMap WHERE -> filter GROUP BY -> collect ORDER BY -> sorted https://blog.jooq.org/2015/08/13/common-sql-clauses-and-their-equivalents-in-java-8-streams/ https://blog.jooq.org/2015/08/13/common-sql-clauses-and-thei...
        • karmakaze 7y ago
• gwbas1c 7y ago
  Part of the issue is that a complicated database can handle the same SQL query many different ways based on indexes and other configurations. This kind of "magic" isn't always clear when programmers are mostly used to working with data structures and procedural code. The other problem, IMO, is that programming languages are very poor at bridging the difference between the SQL domain and the language domain. We really need plugins for compilers because ORM libraries often are harder to learn than the database itself.
  • treis 7y ago
    >This kind of "magic" isn't always clear when programmers are mostly used to working with data structures and procedural code. My problem is that it is like some sort of black magic to me. If I write a complex query I have no idea if what is spit back to me is actually what I want. The only way is seeding lots of records and then manually checking that each filter and calculation is doing what I want. In code complex things can be broken down into more simple items. Then I can reason about and test those building blocks into something I understand and am confident that is working as intended.
    • frosted-flakes 7y ago
      Yes, SQL's biggest fault is that it's not very composable. Complex queries end up being long and repetitive, and the order of the parts of a query is totally unintuitive (it should go something like: FROM, GROUP BY, SELECT, ORDER BY rather than SELECT, FROM, GROUP BY, ORDER BY, which makes autocompletion hard).
      • takeda 7y ago
        QUEL[1] was like that, but thanks to Oracle SQL won. [1] https://en.wikipedia.org/wiki/QUEL_query_languages https://en.wikipedia.org/wiki/QUEL_query_languages
        • setr 7y ago
          One thing I never understood is that the SQL language, and its alternatives, share the same theoretical IR -- the relational algebra -- it shouldn't be that difficult to implement for postgres/mysql alternative relational languages like QUEL or Datalog. Or even just a simplified SQL with a sane, consistent syntax. I know PG has a bunch of procedural-language alternatives, but afaik, no relational-language alternatives. At the same time, I'd also expect it to not be that difficult to transpile from say mysql to postgres, yet there's very little in that space, at least in open source (there is however many ORMS that map to either mysql or postgres..) Both of which, amongst the procedural-languages, you'd find a hundred transpilers (JS->C) and VM-languages (Clojure,Scala,etc on jvm) implemented even by bored/curious students. Which makes my suspicion that there's no technical blocker, just a cultural one. That is, QUEL losing to SQL, such that you can't find any implementation, is an absurd concept -- it should be available on postgres (perhaps requiring some special starting keyword) -- but somehow it is not.
• pjc50 7y ago
  This is partly why I love LINQ: it's a more C# flavoured way of expressing queries. And it works on objects as well as databases. Most ORMs are bad for queries (pull over all the objects and look at their properties!), but LINQ will actually turn your code into SQL under the hood with some remarkable machinery.
  • andybak 7y ago
    Not doubting there are bad ORMs but none of the ones I've dived into is quite that bad (SQL Alchemy and Django's ORM). I sometimes wonder if the debate about ORM is mainly driven by some traumatic horror witnessed at some point in every developer's career. If we judged programming languages by the same metric... Oh... Actually we kind often do that too!
  • uhoh-itsmaciek 7y ago
    Which ORMs are bad for queries? Most popular ORMs these days expose most of sql in a language-specific DSL (and some allow you to splice in bits of raw sql in a semi-structured way as an escape hatch). Sure, you still need to learn to "think in sql", to use these effectively, but the ORM has not been the problem for a long time in most languages.
    • ratww 7y ago
      ActiveRecord, Rails ORM was the poster child of ORMs for a while, and is completely abysmal for non-trivial left joins, non-trivial aggregations and some subqueries. Of course using raw SQL is possible but might force you into converting other parts of the code into raw SQL. There is Arel, but it's just a more verbose SQL in quasi-AST form. On the other hand, I never had much problems with LINQ or Hibernate.
  • fernandotakai 7y ago
    >Most ORMs are bad for queries (pull over all the objects and look at their properties!) i would change that to ORMs CAN be bad for queries. most ORMs are super configurable and can be changed only to pull specific stuff. i would argue that most ORMs are a plus to productivity because for most of cases, you just need simple querying (select * from, simple updates, simple deletes). also, if you really need the power of raw sql you an just use that -- and even then, for most advanced ORMs (django's, for example) exposes a LOT of really advanced sql stuff in python, which, for a lot of developers, is a lot more expressive.
• crazygringo 7y ago
  SQL is very much like CSS to me. It's declarative, the primitives seem entirely non-intuitive, it often takes a lot of fiddling to get what you want, the behind-the-scenes execution is mostly a black box, and while it's supposed to work the same on different implementations (of browsers/databases), there are tons of little gotcha quirks. All in all, they're both entirely different skill sets from traditional programming, and also where experience counts for a ton more than just normal logical thinking.
  • taffer 7y ago
    When you look at SQL from a logical/set-based perspective, it is by no means unintuitive. Basically, all you do is join all the tables you need and then filter out everything you don't need and maybe do an aggregation here and there.
    • alexbanks 7y ago
      And with CSS, basically all you do is tell the browser how things look.
      • paulryanrogers 7y ago
        Conceptually yes. The devil is in the details: tables, flexbox, grid, div-soup, inconsistent naming, etc.
        • alexbanks 7y ago
          Sure. Conceptually is fine. But I'm pointing out how ridiculous it is to water down CSS/SQL to "Basically just _____". Nothing is hard by that logic.
    • 411111111111111 7y ago
      SQL can do a lot more though. Triggers, functions, procedures, access control.....
• Ididntdothis 7y ago
  SQL feels to me like bash or regular expressions. You can do amazing things if you do it full time. But if you do it only a few times per month or year you quickly forget all the subtleties and it gets hard to understand even the stuff you wrote half a year ago. I guess in the end things have become so complex that as a dev you can’t be good at everything. I often wish there were dedicated database guys but if you have one usually that person is sysadmin and won’t help much with coding against the database.
  • rurp 7y ago
    This echos my experience. I've written some complicated SQL queries, but I do it rarely enough that I always have to re-learn a lot of it. There have been a number of instances where I have had to look something up seemingly for the first time, found a good Stack Overflow answer, and then chuckled to myself because I had already upvoted that exact answer at some point in the past. SQL queries are a pretty common source of this, along with uncommon git and terminal commands.
    • Ididntdothis 7y ago
      Very true about git. I would add C++ to that lis. Done full time it’s super powerful. But later or with less experience you look at the code and think “WTF?” .
  • Noumenon72 7y ago
    Common table expressions were all I needed to write <a href="https://modern-sql.com/use-case/literate-sql">literate https://modern-sql.com/use-case/literate-sql">literate SQL</a> and make it perfectly readable when I come back. But I was doing it almost full time so I wasn't forgetting much either.
  • tcgv 7y ago
    I tend to disagree, since I find SQL orders of magnitude more intuitive than regular expressions, having no problem picking it up once every couple of months without requiring to fallback on books or cheat sheets for implementing the majority of queries. Sure, every now and then we have to optimize a query or implement a complex data extraction pattern forcing us to study how to do it, but for me this is unusual. The key aspect of SQL that makes it easy for me is readability. Once you know the basics you can understand what a query is doing just by looking at it. Regular expressions notation on the other hand are really though to read, and indeed I keep always forgetting its syntax.
• brightball 7y ago
  I firmly believe that every developer should spend 2-3 weeks early in their career working with nothing but SQL. It will pay huge dividends for the rest of it. IMO a lot of the issue is that developers for many years using Java or PHP, were using SQL to handle everything. The application language was a pass through later between the client and the database. Your goal was to accomplish as much as possible in a single query and then to simply return the results of that query to the interface. That meant formatting numbers or currency in your SQL. Optimizing inserts or updates to be handled in a single query. Grouping, counting, left/inner joins, having statements to filter on aggregate results. More than 1 or 2 queries for the primary area of the screen was both a rare and foreign experience. And then ORMs started to slowly integrate themselves into the flow of various frameworks to automate the repetitive things around CRUD tasks. Then to address scaling & bloat problems we saw an uptick in REST APIs, microservices that further made those ORMs the norm...and then many developers started actively trying to stay within those API constraints to an almost religious degree which led to a nested payload becoming acceptable fueling the whole "NoSQL" situation, along with the idea that it was somehow better to repeat the same data thousands of times over. A whole lot of people pushed back against this and eventually, it mostly ran its course. I've often seen resistance to SQL to be driven by fear of SQL more so than anything else. As soon as people get a basic comfort level with SQL, it become almost automatic.
  • dominotw 7y ago
    > I firmly believe that every developer should spend 2-3 weeks early in their career working with nothing but SQL. It will pay huge dividends for the rest of it. i did tons of sql couple of years ago on a reporting team. Now I do android dev fulltime and don't remember any SQL beyond basics, highly doubt it will all come back to me if i tried.
    • baq 7y ago
      it doesn't matter, you'll google the details. many people aren't aware that sql isn't just a stupid way of filtering tables and can do things server-side that you'd spend hours implementing and testing on the application side with worse performance in the end most of the time.
• txcwpalpha 7y ago
  When I was in undergrad I was part of program that was heavily programming focused, but was actually part of the business school. Several of the classes I took were heavily SQL focused, with at least one class that had every single assignment requiring extensive designing database schemas and writing SQL. Now, years later, I still think that those classes were some of the most valuable to my career as a programmer. What's interesting to me is that apparently the CS program at my university hardly did anything with SQL, and I notice too that most programmers I meet "in the wild" are lacking in SQL skills, as you mentioned. It's led to some interesting interview situations where I really struggle with any questions about algorithms (my college courses didn't cover algorithms at all) while the interviewer will tell me that I have the best SQL skills of anyone they interviewed. Speaking with others that went to other universities, I've heard that it's similar elsewhere for the "business/programming" to include SQL classes but eschew algorithms, while CS programs will ignore SQL but focus heavily on algorithms. It seems to me like both programs could benefit from meeting in the middle a bit.
  • carlps 7y ago
    I have a very similar background. My degrees are in Business Computer Systems and we had SQL hammered into us. We learned how to program with Java and .NET, but all from a very high level. It wasn't until a couple years into my career after school that I started learning DS+A fundamentals on my own. It blew my mind. I agree that there should be more of a mid-point available.
• EvanAnderson 7y ago
  Some of the most fun I've ever had coding has been creating "complex" SQL queries. The syntax is something that can only be overcome with memorization, but it becomes second-nature fairly quickly. Until I started thinking about SQL as manipulating sets it never "clicked". Once it did, though, a whole world of applications filled with mismatched procedural thinking mapped on to SQL was revealed to me, and my own work became much easier.
  • resu_nimda 7y ago
    The first time I accidentally typed "select 8 from users..." and, instead of an error, it returned a column called "8" with a bunch of rows with the value "8", my mind was blown.
  • eropple 7y ago
    Despite our industry's habit of treating mathy whiteboard things as indicative of one's "programming talent", real application of type, set, and graph theory are not tested nearly as much as they end up being in practice, and I think that developers' reticence to get deep into SQL is symptomatic of the weaknesses there.
• kyllo 7y ago
  TFA isn't even about SQL the language at all though, it's about the scalability and reliability characteristics of databases, especially in a distributed environment.
• coliveira 7y ago
  In the minds of many developers, SQL is not a "real programming language". That's why they don't see the value of spending the time learning it. Many also believe that SQL is something that they should better avoid, using an ORM, and let it be handled by libraries written by "experts". All of this contributes to developers not having a firm grasp of SQL.
  • mey 7y ago
    Yet they are unwittingly willing to learn a different DSL, the ORM's DSL.
• slifin 7y ago
  SQL definitely solves a lot of performance issues for typical users who think they can do better but throwing strings over the wire, trying to get as much as possible without crossing cardinalities and ignoring the n query problem makes SQL at scale disastrous Devs always get one of these things wrong and not being able to use my programming language in the query makes some things hard to express Datalog and Datomic are definitely worth learning https://youtu.be/Pz_NvY1kw6I https://youtu.be/Pz_NvY1kw6I
  • nsomaru 7y ago
    could you clarify what throwing strings over the wire and crossing cardinalities means in this context?
• forgingahead 7y ago
  I don't think it's as elaborate as you're saying -- the clearest road into a productive job for many years has been "join a fast growing tech company who uses <cool_framework> to do the magic, so the tech team is always building more business logic". When you've been working with (eg:) ActiveRecord and Ruby on your database data, direct SQL just doesn't come up until you're trying to wrangle more efficiency in your queries or do more complicated joins. It certainly makes sense for all developers to learn more SQL! But it just hasn't been part of the daily work requirement for many. As for the attraction of noSQL databases, I think perhaps it's more of a new-hotness trend rather than a serious technical decision. NoSQL has its place, but like most of this kind of tooling, it's often applied incorrectly because of a lack of thinking through the business logic.
• MathCodeLove 7y ago
  As a programmer who is admittedly attracted to Mongo, a large part of it is simply its ease of integration. I'd happily spend more time familiarizing myself with SQL if it were less of a pain to integrate into my projects. The first time ever using Mongo I had a cloud cluster connected and working in about 10 minutes after signing up. Trying to integrate SQLite took me around 1.5 hours before it was functional. To this day I have yet to set up a real cloud SQL database because the one time I tried it with PostreSQL I just couldnt get it to work.
  • izacus 7y ago
    Is initial integration cost really the most important metric you look at when designing a system that will probably run for years and will have to be maintained and scaled?
• bryanrasmussen 7y ago
  I'm pretty sure I would be good at SQL if I devoted time to it, because I've worked in other declarative DSLs and generally outperformed other programmers using them, but basically every place I've ever worked at that used SQL extensively had already employed SQL experts that just handled the problems on that level so that it didn't seem worthwhile bothering with it.
• echlebek 7y ago
  SQL really is a programming language though. It's just not imperative.
• bpyne 7y ago
  I think you're onto something with the observation about what type of developers take to SQL. We have a spectrum in which people think more like how the processor operates to people who think more in classes of problems and their solutions. We run into problems when someone with a proclivity to one side meets a problem best solved by thinking on the other side of the spectrum. An example is a young dev in my organization who was given requirements for a new app that required a data store. Not being comfortable with SQL or relational databases, he chose a document store. It wasn't a good fit. Very quickly requirements expanded and caused his code to balloon into a mess of nested loops with lots of if-checks. Performance has progressively decayed as well. A simple multi-table join with filtering would have knocked off 2/3 of the code.
• josephorjoe 7y ago
  When I first encountered SQL, my immediate reaction was to start writing my own lightweight ORM (two days later I learned ORMs were a thing that already existed) because I took one look at that syntax and decided it was insane to work with directly. I definitely don't think in SQL. That said, I like working w SQL databases -- just please give me a battle tested ORM...
• homami 7y ago
  I find SQL INSERT statement not intuitive. I can understand why SQL requires me to declare the field names and then the values of a new row that I am inserting; but it would've been a huge time saver if SQL had a key-value dictionary-like syntax: INSERT INTO "my_table" "col1": value1, "col2": value2, ...
  • PZ81JUXJE7uJ 7y ago
    MySQL supports a insert/set statement: INSERT INTO tbl SET a = 1, b = 2;
    • castorp 7y ago
      How does that look like with a multi-row insert?
  • castorp 7y ago
    And when you need to insert more than one row, you repeat the column name over and over again for each row - not a good idea either.
    • rspeele 7y ago
      Single-row inserts are super common in both application code and in interactive usage of SQL, so I think that it is worth it to have a syntax for them that reduces this common error. Especially when a table has many columns of the same type (like booleans). E.g. insert into Permissions(UserId, Create, Read, Update, Delete, Share, ForceUnlock, LaunchNukes) values (12345, 1, 1, 0, 1, 0, 0, 1); When I wrote my (now unmaintained) statically typed SQL dialect for F#, which compiles to underlying SQLite/Postgres/MSSQL, I added a single-row insert with Field=Value because it's nice to have and took no more than 30 minutes to do. It's only a tweak to the parser after all -- you just parse it to the same AST used to represent the `INSERT ... VALUES` clause and all later stages of the compilation do not need to know about it. https://rspeele.gitbooks.io/rezoom-sql/doc/Language/InsertStmt.html https://rspeele.gitbooks.io/rezoom-sql/doc/Language/InsertSt...
• fendy3002 7y ago
  However most of the time we'll only deal with simple queries such as select where and join. More complex example is just group by and simple aggregation such as max min count. People struggling with sql because they usually don't know easier way to query that they resulting to use more complex query. For example `select where id in` and `case when` are both powerful query that many don't know or under utilized. With `select where in` for example, we can do object mapping / join on application level instead db level.
• markus_zhang 7y ago
  Does it mean that my years of job of analyst writing SQL queries might be a valuable treasure if I were to apply for some dev job?
• randomdata 7y ago
  > I suspect this was what attracted developers to noSQL databases like Mongo in the first place -- it's more attuned to a programmatic mindset. I suspect it was mostly because the tree-like structures people were trying to represent are an absolute pain to work with when you have to shove them into two dimensional rows and columns. I doubt SQL itself had much to do with it as ORMs were already all the rage when Mongo emerged. But ORMs only slightly improve on papering over the data structure impedance mismatch. Most of the pain points present with using SQL directly remain in ORMs when it comes to this problem. With Mongo you can just throw the tree at it and it will happily store it and nicely give it back again. Which eventually leads to its own set of problems due to how it handles said tree internally, but that's why it has fallen out of favour and SQL databases are the new hotness again. You'll notice the frontrunners in the NoSQL movement of the time weren't relational databases with a different query language. They all took different approaches to dealing with data itself. Sometimes, humorously, they even maintained SQL as the query language. NoSQL didn't come to mean "No SQL" at all, but rather "Not Relational". As an aside, while it might go a bit outside the spirit of SQL and introduces its own challenges, I remain somewhat amazed that we haven't seen a standard way emerge to query tree-like structures to better reflect the needs of a fairly common use case. You can kind of get there in unconventional ways, like using json_agg in Postgres, but that all seems pretty hacky.
  • yen223 7y ago
    I couldn't agree more. I think people underestimate how much complexity got introduced thanks to the object-relational mismatch. It would be really nice if there were databases that attempted to expose a data interface that is more tree-friendly. That's why I'm keeping an eye out on projects like EdgeDB and DGraph, and even Hasura.
• rspeele 7y ago
  Developers do not want to deal with data in 2D tables all the time. The expressive power SQL offers for manipulating these 2D tables is pretty good, but it can't compare to the ease of working with data not confined to that shape, which is what devs are used to from "real" languages - whether imperative, pure functional, or whatever. In my mind the best example of this is grouping. Just like most languages now have "Map" and "Filter" functions in their standard libraries, many now also have some form of "GroupBy" function for working with lists and objects in memory. Invariably the meaning of this is that it takes: 1. A list of Xs 2. A key function from an X to a Y, where Ys can be tested for equality And spits out: A list of pairs (Y, Xs), each pair containing: 1. A key value Y 2. A list of Xs from the input list matching that key That list-of-(keys and sublists) can then be fed into whatever .Map(), .Filter(), etc. you want to use next. It is perfectly good for simple aggregates like "give me the total salary we're paying each department", but it's also great for stuff like "give me the 3 highest-paid employees within each department". It just makes sense - map over your groups, sorting each sub-list by salary descending and taking the first 3. Nice clean pure functional stuff. By contrast in SQL, the first problem is super easy - group by department, select sum(salary). The second problem trips a lot of people up because suddenly you aren't really using GROUP BY anymore, even though the thing you're doing is still a "grouping" task. I think the main way to do it is to use ROW_NUMBER() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) to get a ranking number for each employee then select only those with ranking <= 3. Why do I think that's worse? 1. If you were to describe to a person what you're trying to accomplish, the first step would be the same for problems 1 and 2 - get the employees for each department. But the queries in SQL are not alike and do not appear to share the same first step. 2. There needed to be dedicated extra language syntax to do this, instead of having one function (not even special syntax) that gives you the essence of grouping - which other functions then aggregate/massage further into whatever you need. This makes it harder to learn and harder to pick the right tool for a given query job. 3. Locality is almost fundamental to programming. I like writing the inner dept.OrderByDescending(e => e.Salary).Take(3) in the real programming language, because I can think locally to "this department's employees" and even down to "this employee's salary". In SQL, you're a mile above the data tying things together with push pins and string by ID, row number, etc. You rarely get to think locally. 4. In the same vein as above, structured data maps better to how I think. When I write data code, the metaphors in my head involve places and containers - drawers, shelves, moving things around. I am organizing the objects into boxes, going into each box, and picking my top 3. I am not marking each object with a number then going over them all as a big set and tossing out the ones with numbers greater than 3. 5. When I get my result from this query, I will probably want to work with it in the nested-list form, even if only to display it with merged cells for the the department (so I don't repeat every department name 3 times). Funny enough, if not using an ORM, I'll end up using my language's GroupBy to massage the flat rows back into this form. Despite all this I want even less to do with NoSQL. I don't want to chuck blobs of JSON into the DB and end up with duplicate data, orphan data, ill-defined schemas, data that's only efficiently queryable one way, etc. I really do want to store my data in a relational form, and a normalized one where possible. I am happy with my tables. I just would love to query them in a LINQ-like language that is capable of representing and manipulating nested data structures -- and returning them to my application as such.
• pjmlp 7y ago
  It depends pretty much on the quality of one's degree. Thankfully we had one semester course on databases, all the respective layers, got to implement B+Tree indexes in C, and a GUI application using Oracle's Forms their 90's Visual Basic variant, but using PL/SQL instead.