1. 42

  2. 16

    Having interviewed a lot of people, SQL is one of those things that everyone thinks they know well and very few actually do. In interviews now, if I see them claiming expertise, I ask if they can do something more than “SELECT * FROM foo” because so often that’s all it takes to “know SQL” on your resume.

    Good database knowledge can be extremely valuable. I can’t tell you how many times I’ve seen code that makes four or five round-trips to the DB, grabbing data, processing it client-side, and sending it back that could’ve been done in one round-trip with a well-crafted SQL query.

    There’s the other side too: I’ve seen some truly awful database designs that have required multiple round trips because the DB designer didn’t understand foreign keys or constraints or whatever.

    1. 4

      Depends on how you interview me.

      If you sit me down with a pen and paper and tell me to write a complex query…. I might well fail.

      If you sit me down with say sqlitebrowser… and an editor side by side, I will rapidly iterate starting from very simple queries (yes, I will start with select * from foo) to as complex as need be.

      And yes, I might occasionally refer to the syntax for the gnarlier corners.

      But I will get it done pretty rapidly in a single very well crafted query.

      Conversely, I’m a true believer in what CJ Date has been saying for decades…. so if you allow me to redesign the table definitions…. you’d be shocked by how simple my queries will be.

      1. 3

        Imo one of the best arguments against relying on ORMs is performance: in some situations a CTE or window function can get you one or two orders of magnitude improvement over a naive orm solution.

        1. 2

          Nothing prevent you from implementing those edge case in your ORM though. I personally use SqlAlchemy, and I feel like it cover what I need as-is 90% of the time, and the 10% of the time left it gives me the tool to build more complexe queries and even allow me to keep using the “object mapping”. SqlAlchemy supports CTE and Window function!

          For even too complexe query, it might also be possible to wrap them into SQL Function and simply map your ORM to this function.

          1. 2

            Oh, to clarify, I think ORMs are great, I just don’t think it’s great to rely on them. They do 90% of the things for you, but you need to know SQL for the other 10% of cases. Some things aren’t like that, where there’s not such a wildly significant benefit as knowing SQL gives you in this case.

            1. 1

              This is very true. It’s also helpful to know (in rough terms) what the ORM is doing under the hood, something that’s only really possible if you understand the SQL behind the operations.

            2. 1

              Yep, Peewee orm also supports things like CTE, window functions, insert/on conflict, etc. The query builders for both libraries are very flexible, but you pretty much have to know SQL and then translate it into the APIs the query builder exposes. For experienced programmers this is no problem, but someone less fluent with SQL is going to have no idea how to use this advanced functionality, regardless of how it is exposed.

              1. 1

                Definitely! My point was mostly about ORM and “advanced”/performant SQL not being mutually exclusive.

                1. 1

                  I like ORM or query builders not because I don’t know SQL, but rather because I detest the SQL syntax. I wish there were a better relational language. Maybe Tutorial D in front on Postgres’ storage engine.

          2. 2

            What would you consider beyond “SELECT * FROM foo"? I don’t touch SQL on a daily basis, but I could throw together a left, right, and full join if I needed to, and I’m aware of how to use subqueries. What SQL skills would someone have in order for you to consider them competent / good at SQL?

            1. 4

              JOINs, GROUP BY, that sort of thing. If they’re going to be building DBs, understanding indexes, unique constraints, and foreign keys.

              If you’re gonna be my lead database programmer/DB admin/DB architect, I’d want to see understanding of good database design (normal forms, good foreign key relationships, good key choices), CTEs, triggers (in your preferred database), being able to spot when a full table scan might happen, understanding when and how to use precomputed values for future queries, and so on.

              1. 4

                The use of aggregate functions, defining new aggregate functions, the various joins, the 3VL truth table, grouping, subselects, CTEs, that kind of thing.

                1. 3

                  I like asking candidates

                  • find me the employee with the highest salary (entire row, not just the salary) - will usually use sub select with max.
                  • find me the employee with the second highest salary - expected to use window function but can still get away with two subquries.
                  • find me the employee with the second highest salary in every department - expected to use window with partition.

                  If you found a guy that thought about equal values ( rank / dense rank / row number ) you know he did some work. Hire him.

                  Haven’t touched joins yet.

                  1. 2

                    I don’t know window function, but believe that I know join

                    1. 3

                      It’s well worth the time learning window functions.

                      As Maruc Winand (who created use-the-index-luke.com and the newer modern-sql.com) says in his talk on Modern SQL there’s life before windows functions and life after windows functions.

                    2. 1

                      I’d much prefer lateral joins be used in some of these cases.

                    3. 1

                      I’ve seen no mention yet of EXPLAIN queries (Or similar operation in other RDMS than Postgresql?). Never been doing a lot of SQL in the past, but lately I had to work with some complexe queries over large dataset and most of my learning involved playing with EXPLAIN [ANALYZE], understanding the result and tweak the base query to fix the performance issue. Once you understand that, you can work from there to find the best index, where to place subqueries, set large enough working memory, etc.

                  2. 3

                    I learned this the hard way when implemented georadius queries for NewBusinessMonitor. I first tried it in Redis, doing set intersections on hundreds of thousands of records, which worked well until it didn’t. I ended up [badly] reimplemented a bunch of normal relational things.

                    Learning even basic joins (and replacing my georadius Redis stuff with cube/earthdistance in PostgreSQL) solved so many headaches in one go.

                    1. 3

                      It is valuable across different roles and disciplines

                      Obviously not useful everywhere. I’m working in an environment where we have to fit an emergency break, adaptive cruise control, and more features into 1.2MB of RAM. No SQL here. ;)

                      I agree that SQL skills are underrated. I have written some but I would have to look up the syntax for joins. It is helpful to know if your ORM is limiting you or the SQL database below it. In the first case the effort to work around the ORM might be worth it. In the second case it is futile and you have to consider other ways to store the data.

                      1. 2

                        i 100% agree with this across the board. I finally got beyond SELECT * FROM almost two years ago. Learning about the various JOIN statements, sub queries, and transactions has helped me immensely at work.

                        1. 0

                          lack a lot of content, but agree with the general message

                          1. 0

                            Can anyone here provide recs as to SQL books/resources they’ve gotten value out of?

                            I recently cracked open a copy of Stephane Faroult’s The Art of SQL, though it’s too early at this point for me to say anything more substantive about it.