Learn SQL, dammit

http://gun.io/blog/learn-sql/

by jpgjb • 13 years ago

102 114 13 years ago

gun.io

thelarry2• 13 years ago

How do you optimize your system if you don't understand the queries that the ORM generates?

evan_• 13 years ago

You throw more hardware at it, of course!

taude2• 13 years ago

This is only a valid question if you have a single engineer on the project? Likely, there's at least one or two people on the team that are gurus at optimizing the access to your datastore.

umsm• 13 years ago

Let's not even talk about the overhead required for ORM...

AceJohnny2• 13 years ago

How can you have any pudding if you don't eat yer meat?

untog4• 13 years ago

I once interviewed a guy with a Masters in computing (of some sort, I forget) who didn't know SQL. He'd been developing for years, but lived entirely in .NET land and just used ORMs.

Absolutely crazy.

taude2• 13 years ago

I could actually see how this happens these days, especially when working on a big enough team with dedicated Database engineers. In the old days, I'd definitely be writing my own stored procs and embedded SQL in code, etc. Then I worked for several years on teams with dedicated data gurus. When we'd hit some performance thing that would need tweaked, they'd write a stored procedure for it, and we'd just call it from the middleware. I've seen this type of team composition on both .NET and Java platforms, with and without the use of ORMS.

It all depends on the coverage of your server-side web engineers...some will go deeper into the JavaScript/UI, some go deeper into the data-model.

rrreese• 13 years ago

As a .Net developer who uses ORM's the idea of not understanding the underlying SQL scares me. n+1 select errors would likely be rampant.

Joel Spolsky covered this well in his article "The Law of Leaky Abstractions": http://www.joelonsoftware.com/articles/LeakyAbstractions.htm...

jmmcd• 13 years ago

I have a PhD in computing (of some sort, I forget) and I know about 3 SQL commands. I've been developing for decades, and I never need to interact with databases. Like everyone in my field of research, I keep my data in text files.

dragonwriter4• 13 years ago

> I have a PhD in computing (of some sort, I forget) and I know about 3 SQL commands. I've been developing for decades, and I never need to interact with databases.

If you were a developer that did interact with databases (but not a DBA or specifically a "database developer"), you could probably get by with the four that correspond to CRUD operations directly (SELECT, INSERT, UPDATE, DELETE), so knowing "about 3" isn't all that bad.

untog4• 13 years ago

Sure- if using databases isn't an everyday task for you it's understandable. But this guy used them every day, without knowing what was actually going on when he called .Save()

qu4z-2• 13 years ago

I feel like the academic qualifications (Bachelors, Masters, PhD, etc) and programming shippable applications are two separate hierarchies.

So I'm surprised he didn't know SQL after developing .NET for years, but not that surprised he had a Masters but didn't know SQL.

mongol• 13 years ago

I don't understand how it is possible to develop anything database-related without knowing SQL. Are these people not querying the database directly to figure out what it contains (during development, debugging, testing etc etc)?

plorkyeran• 13 years ago

I've never seen a GUI frontend that managed to remove the need to write custom queries while inspecting the data, but if you don't know SQL I could see someone not even realizing all the things that can be trivially done with a relational database, and just settling for manually cross-referencing tables.

hmsimha• 13 years ago

Well there's the whole world of NoSQL but without understanding the relational model there's no guarantee that your choice of NoSQL is appropriate. From what I understand, many of the prominent advocates of NoSQL even recommend learning relational first.

overshard• 13 years ago

Learn caching, dammit

thelarry2• 13 years ago

Can't you learn both?

mikeevans• 13 years ago

He's referring to the fact that the site is down already.

Google cache: http://webcache.googleusercontent.com/search?q=cache%3Agun.i...

rheide• 13 years ago

No. First, learn the internals of your system. Learn SQL. Learn NoSQL. Understand why your software is slow. Then, once you've optimized the worst case away with decent SQL and/or a good object-oriented architecture. Then and only then should you start with caching. Too many developers these days are just brainless monkeys who dump everything in memcache.

jeffasinger• 13 years ago

A coworker mentioned to me that she came in early every day so she could run a report that took about an hour and a half before the CEO got in.

I looked at the code, nothing looked THAT bad, so I did an EXPLAIN, noticed a missing index, added it. I ran the report in 4 minutes.

Clearly, whoever wrote that report didn't know nearly enough about SQL.

DrJokepu2• 13 years ago

Throwing caching at poorly written database code is exactly like when parents tell their children to tidy their room and then they shove everything under the bed. You can only do that for so long, at some point you need to clean that room up.

pjungwir5• 13 years ago

For a while I used to ask interview candidates to explain the difference between WHERE and HAVING, to see if they'd ever done anything beyond the basics. I'm still not sure if that's too hard, but people who could answer it did tend to do much better in the rest of the interview as well.

ScottWhigham2• 13 years ago

It's sad but this difference, although completely fundamental to SQL, is seen as "advanced" by most devs that I know. They would have no clue as to how to answer...

bgilroy26• 13 years ago

Order of execution is a great second stop on the road to SQL, after learning what all of the words mean.

michaelt2• 13 years ago

Consider these two queries (untested so they might have typos):

  select order_number,sum(line_value) as order_value from order_line
  group by order_number
  having order_value > 100;

And:

  select order_number,order_value from 
    ( select order_number,sum(line_value) as order_value from order_line
      group by order_number )
  where order_value > 100;

I'd expect them to have the same explain plan and runtime characteristics. Isn't HAVING just syntactic sugar for the fact queries can be arbitrarily nested? Syntactic sugar isn't fundamental IMHO.

archon• 13 years ago

This prompted me to go look it up, since I didn't know. HAVING is WHERE for aggregate functions (SUM, etc).

Funny thing is, I've used HAVING a lot in the past, but couldn't have explained the difference succinctly without cheating and looking it up.

willvarfar• 13 years ago

Conceptually WHERE is filtering the table rows that get considered and HAVING is filtering the result rows - functions and all - that get returned. That's how my mental model of it all works.

zorbo• 13 years ago

I deal with pretty complex queries daily, and I completely forgot what HAVING did exactly. I guess it's because I long ago realized that if I had to use HAVING in a query, I'm doing something wrong. That's almost always a Reporting/BusIntel tool's job, not mine.

jfb2• 13 years ago

I like asking for a 3VL truth table for the basic logical operators. This is a good way to get people talking about something that is conceptually fairly straightforward but that they might not have ever really puzzled out before.

xradionut• 13 years ago

Learn accounting, too!

chris_mahan5• 13 years ago

Don't forget econ and statistics.

pjungwir5• 13 years ago

A class of query I love that scares off a lot of developers is a correlated sub-query, where the subquery references a value from the outer query. For example, finding all employees with at least one assignment:

    SELECT *
    FROM   employees e
    WHERE  EXISTS (SELECT 1
                   FROM   assignments a
                   WHERE  a.employee_id = e.id)

For a while in Oracle this was a lot faster than IN/NOT IN. I'm not sure if that's still the case, or if it's true for other systems. I believe I read that in Postgres the query planner does the same thing whether you use EXISTS/NOT EXISTS or IN/NOT IN.

EDIT: This kind of query is great with Rails scopes, because you can write something like this:

    class Employee
      scope :with_assignments, where(<<-EOQ)
        EXISTS (SELECT 1
                FROM   assignments a
                WHERE  a.employee_id = employees.id)
      EOQ
    end

and that is easily composeable with other scopes/conditions/etc since it doesn't force you to use any joins. Yay for mixing SQL with your ORM!

obviouslygreen• 13 years ago

I recently discovered that MySQL before 5.6 (5.5 being the latest available on RDS, of course) does not honor indexes if they should be invoked for a subquery, e.g.

select whatever from wherever where user_id in (select id from users where somethingorother like '%lol%');

Got an index on user_id? Too bad. Ignored.

If you precompute the values, though?

select whatever from wherever where user_id in (1, 2, 3);

Sweet, I love indexes! I'll definitely use them.

miahi2• 13 years ago

Loks like it cannot evaluate the subquery result (number of items).

michaelt2• 13 years ago

Let's say your inner query returns n rows and wherever contains m rows. And your index is a B-tree, so you can hit it once in O(log m).

If you use a nested loop join, where you hit the index once for each inner query result, that's O(n*log m). If on the other hand you do a hash join, skipping the index but doing a full table scan of wherever, the complexity is O(m+n). So which is the faster choice depends on how many rows the inner query returns.

If you want to plan up front, before you know what m is, how would you decide which join to use?

itsybitsycoder• 13 years ago

I believe I read that in Postgres the query planner does the same thing whether you use EXISTS/NOT EXISTS or IN/NOT IN.

I don't think this is true, unless it's a very recent change. Here's a post from 2009 comparing NOT IN/NOT EXISTS/LEFT JOIN WHERE IS NULL for Postgres:

http://explainextended.com/2009/09/16/not-in-vs-not-exists-v...

pjungwir5• 13 years ago

In the NOT case, NOT IN and NOT EXISTS may even return different results! I think I knew that once and had forgotten. Good to be reminded!

yxhuvud• 13 years ago

Why would you ever want to use such a horrible statement instead of using an inner join?

scope :with_assignments, joins(:assignments)

(or :assignment, depending on how your association is defined)

pjungwir5• 13 years ago

Well, adding joins to the outermost query can limit your options re how you use the scope. For instance, `joins` sets a read-only flag on the AR result, so you can't say Employee.with_assignments.destroy_all, for example, to, um, fire all your busy people. :-)

DrJokepu2• 13 years ago

OP's query is not the same as an inner join; it will only return each employee only once while your query will return each employee for every assignment it is associated with.

nimish• 13 years ago

This is a textbook semijoin, but you can see what the postgres planner does with explain and explain analyze tacked in front of a query.

chris_mahan5• 13 years ago

You can use CROSS APPLY on tsql, and it allows you to use an order by in the inner query, for example, if you wanted to know the id of the employee's latest assignment. SELECT e.* , assignment_id = b.id FROM employees e CROSS APPLY (SELECT top 1 a.id FROM assignments a WHERE a.employee_id = e.id ORDER BY a.assignment_date DESC ) b

In tsql, you are not allowed to have order by in a subquery (if memory serves me right)

Edit: Oh crap, I forgot this isn't SO, and the formatting went out the window.

pfg• 13 years ago

You can use ORDER BY in connection with TOP n, for example if you want to use the result of a subquery as a column:

  SELECT
    user_name,
    (SELECT TOP 1 last_action FROM actions WHERE actions.user_id=users.user_id ORDER BY action_timestamp)
  FROM
    users

(TOP is T-SQLs version of LIMIT)

Also, thanks for making me aware of CROSS APPLY, haven't seen that before!

msluyter• 13 years ago

I think IN is equally fast in Oracle, or at least it was when I was working with 10.2.

In Postgres, it appears that this does the same thing as IN.

pjungwir5• 13 years ago

I haven't used Oracle since 8, and the only reference I can find now from those days are a couple AskTom articles [0] [1]. They are quite worth reading, although what they say re performance is not quite what I (mis?)remember.

[0] http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QU...

[1] http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUEST...

daigoba663• 13 years ago

Depending on the query and the optimizer, the resulting plan for IN/NOT IN might be the same as EXISTS/NOT EXISTS. E.g. a left semi join. I know this is true for MSSQL Server, I can't speak for Oracle.

zzzeek3• 13 years ago

The main point that one should know SQL as much as possible before using ORMs, I agree with fully. The point that applications should be written by quick-prototyping with an ORM, then replacing the ORM entirely with raw SQL, I could not disagree with more. Since he is using my own ORM (SQLAlchemy) as his example, I'd like to point out (as many of you know I always do) that SQLAlchemy's entire approach is one of exposing the relational model at all times. There is virtually no need to drop out of using the Query and/or select() constructs into raw SQL strings, as these constructs can represent SQL fully and more or less directly, not really any different in concept than when the relational database itself parses an incoming SQL string into a tokenized parse tree internally. The results returned are tuples. If your tuples happen to line up with the attributes in your object model, then you can tell it to get objects back. There is no sharp red line between "I'm using an ORM!" vs. "I'm using SQL!". With a mature tool like SQLAlchemy you're using both, and the tool is there to automate your work with SQL, not to replace it.

So of course, learn SQL as fully as possible. But I recommend using an ORM that allows you to make full use of your SQL knowledge at all times.

moron4hire4• 13 years ago

This is very important. Any time you build a bike with training wheels, you need to build in a way to take the training wheels off. Otherwise, at some point the solution is to not make faster training wheels, but discard the bike entirely.

derleth2• 13 years ago

Is C training wheels on assembly?

tjr3• 13 years ago

If you've already learned SQL and are comfortable using it directly, do you think there is any reason that it'd be wrong to continue doing so?

zzzeek3• 13 years ago

it's really not as much about the querying (though there is a lot of time-saving automation to be had there) as it is about integrating the data in your object model with the tuples being shuttled to/from the database. Like, at what point do you get sick of writing redundant "INSERT INTO <table>" over and over again? Are there really people who still don't see the time-wasting, code-cluttering repetition in that?

PleaseBeSerious• 13 years ago

If you already have a layer of abstraction to your SQL statements, then it can be arguable. But if your are littering SQL statements everywhere, making changes to the database can be more tedious.

Also if you want to support more than one type database, I would use an ORM.

malkia• 13 years ago

Other people on your team, might not be on the same SQL level as you. E.g. you can create very fast update/bulk/etc. operations directly in your used SQL engine, but then provide some ORM for people not familiar so that they can get access (speed might not be critical to them, just access to the db).

An example - in the game industry we often have technical artists - pretty cool bunch of folks with awesome talents - art (maya, motionbuilder, etc.) and coding (python, mel, C/C++) - these folks are never afraid to step into the dark woods, and would take SQL and learn it as if it was nothing dangerous, but they surely won't mind a good ORM. From what I've learned, they see most of these things as tools.

jrochkind15• 13 years ago

Any reason it would be 'wrong'? There are few things in software engineering that are universally 'right' or 'wrong', it all depends on context.

There are plenty of people who already know SQL and are comfortable using it, but still prefer to use an ORM though. It's not like ORMs are only used or liked by people who don't know SQL.

camus• 13 years ago

> then replacing the ORM entirely with raw SQL,

anyway it is usually never the case. Why would you build a complex app only to tear it appart with going back to raw SQL ?

i'm not talking about SQLAlchemy but often the main problem with ORM is performance. Especially when a orm has its own query language on top of the SQL language ( Like Hibernate or Doctrine ).

A good ORM imho is what you described , something very light but still usefull enough so one doesnt have to do data transformation from rows to objects. How does SQLAchemy fetch related objects ? is it is lazy ? or does it fetch everything when doing a join query for instance.

Anyway thank you for your great work with SQLAlchemy.

aidos• 13 years ago

Indeed. I do worry about the level of SQL knowledge that developers have in general these days. Without understanding it - and the relational model (which I'm a big fan of) - it's impossible to optimise the way you use the ORM.

OT but thanks for SQLAlchemy. It's the model ORM in my opinion. It's the only one I've ever used that feels like it respects SQL. When I started working with Python fulltime I spent days reading through the SQLAlchemy source - I learnt a lot from that.

jfb2• 13 years ago

I have no interest in the 'O' or 'M' in an ORM, but I am very interested in a language better than SQL for interacting with SQL-based pseudo-relational datastores. I've found SQLAlchemy to be a pretty good tool in this respect.

f4stjack• 13 years ago

This was also discussed in Rob Conery's speech on Norwegian Developer's Conference: http://ndc2011.macsimum.no/mp4/Day2%20Thursday/Track1%201140...

moron4hire4• 13 years ago

I see no reason why one programmer can't learn just about everything related to their application. I expect the programmers who work for me to be experts in SQL, CSS, and everything in between.

benjamincburns2• 13 years ago

Everything?

https://plus.google.com/112218872649456413744/posts/dfydM2Cn...

moron4hire4• 13 years ago

nobody likes a pedant.

redcircle• 13 years ago

He left out an important reason to know and use SQL directly: you should always be aware how you are using your indices. This goes beyond making an index for a column in the table: you also need to know whether the query will map well to a btree index. Mobile, with its low resources, makes index optimization even more important; apple's Core Data can easily kill your app.

sehrope• 13 years ago

If you really want to master non-trivial SQL learn to think about filtering, joining, and combining data via set theory. Once you can logically break down the set you're trying to get to the rest then just becomes learning/looking-up syntax.

The other big piece of advice is the tried and true incremental approach. The more complicated something is, the more likely I am to use the SQL client the way one uses a REPL and incrementally write the query:

    1. Write basic SELECT
    2. Add another clause (WHERE filter, GROUP BY, etc...)
    3. Execute (syntax/sanity test)
    4. Finish or Goto step 2

Just like everything else in programming it's amazing how much simpler things are when you just piece them together one step at a time.

[1] https://en.wikipedia.org/wiki/Set_theory

[2] http://seanmehan.globat.com/blog/2011/12/20/set-theory-and-s...

tom_b• 13 years ago

I believe it's somewhat impossible to overestimate how REPL-like data munging with SQL actually is. Particularly as the complexity of your data exploration increases.

Celko calls this "thinking in sets."

lucb1e• 13 years ago

So I know SQL... but what's that ORM he assumed I'm familiar with?

antoko• 13 years ago

Object-relational mapping.

http://en.wikipedia.org/wiki/Object-relational_mapping

phillc73• 13 years ago

I agree. I am not a professional programmer. I'm more a technologist with a passing interest in programming. My desktop is Linux and I can basically hack in Bash, Perl and R to achieve certain simple outcomes. One of the things I use R for is to query a MySQL database for statistical analysis. To do this, I write long hand SQL queries. I didn't know there was another way. What is this ORM of which people speak?

baddox• 13 years ago

> SQL is faster (because an ORM is an abstraction layer on top of SQL)

You can't make this stuff up.

Demiurge• 13 years ago

The article has the good premise, of course you should learn SQL, but it goes over the top and makes some false presumptions for the sake of the argument:

'Think about it, though: it’s absurd that you would even need to learn any SQL at all! The very nature of an ORM is to bypass SQL.'

I think the nature of ORM is to map object oriented code to relation data. So not to bypass, but to pass between the two conveniently. Conveniences that ORMs do automatically that otherwise you do manually are type checking, sql sanitization, and merging logic (methods) with the data in the same class. Knowing SQL does not make the above tasks any easier, so does not, in any way, prompt dropping ORMs.

Also, there is the wrong use of 'begs the question' in the same paragraph :)

derleth2• 13 years ago

> Also, there is the wrong use of 'begs the question' in the same paragraph :)

That was a bad translation from day one, and people are now using the phrase in a way that makes more sense.

addflip• 13 years ago

Learn to write SQL views. It'll make you feel all warm and giggly inside.

miahi2• 13 years ago

Then learn to write materialized views, and feel the power of the index.

habitue• 13 years ago

Then learn the pain as everyone uses an expensive view in all sorts of queries because it's easier, and they dont understand the performance characteristics of joins well enough to understand why they shouldn't do that.

Trufa• 13 years ago

Google cache/mirror: http://goo.gl/3tclG

bayesianhorse• 13 years ago

Most of my experience is with the Django ORM. The point of the django ORM is not to replace SQL knowledge, even though that is feasible. The main point is reusability.

I had to work with SQL through PHP for a while and I found myself "composing" SQL queries in a myriad of ways. I tried to not repeat myself, but it felt like the Django ORM would have gone a lot further in cleaning up the query-building.

In conjunction with Django forms and Django Admin, maybe even the template language, the ORM makes query construction reusable.

One of the kickers is the ability to unify object construction from table columns. It's easy to convert a string or number to some Python field. It's more elaborate with Decimal, Json, or whatever you want to cook up.

Tycho• 13 years ago

The nice thing about SQL is that it doesn't take very long to learn how to use it and then it's incredibly useful any time you have a database to interact with. I think all novice programmers should take a crack at it.

dragonwriter4• 13 years ago

More generally, I think that learning about the relational model (which, in practice, probably means learning to use SQL, even though SQL is in many ways far from ideal) of data is fundamentally mind-opening in how you understand and deal with data and makes you a better programmer in general, even when you aren't interacting with an external relational database.

nsxwolf3• 13 years ago

Everyone uses an ORM. You use a well known, documented, and supported ORM, or you're writing your own wether you realize it or not.

Don't believe me?

1. Do you have objects? 2. Do you have relational data?

There's the O and the R. How do you get them together? That's where the M comes in. You use a library that knows how to do the M, or you do your own M with a bunch of getters and setters, for loops and case statements.

Eventually, any little change to the database becomes a regression nightmare.

Once you find yourself saying "I know, I'll build a code generator to create these DAOs", that's when you should finally realize you should have used a real ORM. Sadly, many people still won't get it at this point and will go ahead with the code generator.

papsosouid3• 13 years ago

>Everyone uses an ORM

Bullshit. You are making absurd generalizations based on your personal view of how the rest of the world operates.

>1. Do you have objects?

No, I do not. That makes it pretty obvious that I do not use an ORM doesn't it? "Everyone" includes more than just people using OO languages.

nsxwolf3• 13 years ago

Do you use data structures? Do you use variables? Do you use anything that in any way stores the values from your database so your application can work with them?

Then you are doing XRM, where X = Objects, structs, vars...

nissimk• 13 years ago

I don't know why you're being downvoted. Even when you're using an oo language, there's not always a reason to force your query results into an "Object." Frequently the only object you need is a 2 dimensional data structure which could be a list of dictionaries, or a DataTable or something like that. You don't always need a special named structure for the results of every query. ORM's always seem to be designed for people who have a one to one mapping between data structures in their program and tables in their DB. I find that this very rarely makes sense, particularly when you're talking about analytical applications.

jamesmiller5• 13 years ago

For those interested in sharpening their SQL skills I have found these two books to be a great resource.

1. Joe Celko's Trees and Hierarchies in SQL for Smarties 2. Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

jrochkind15• 13 years ago

Joe Celko's more general "SQL For Smarties" is how I learned SQL, it is a great book (or, at least, was like 3 editions ago)

daigoba663• 13 years ago

An ORM isn't necessarily complex. It just needs to map a tuple to an object.

Where things get complex is when your framework starts introducing other concepts such as query generators, unit-of-work, caching, lazy-loading, etc.

One of the most critical features is query generation, which I think is the point of this article. Simple queries are pretty easy to abstract, such as loading rows by primary key or querying based off a simple index. Other queries, especially aggregate queries, get tricky fast. I argue that often it is much harder and more work to construct an appropriate query via your frameworks query generator.

Fortunately many good frameworks allow you to essentially write the exact SQL to be executed and the rest of the framework (mapping, caching, unit-of-work) "just works" with the results.

tomku• 13 years ago

What are some good resources for methodically learning SQL? Like many of the other devs I know, I learned a hodge podge of SQL while working on other projects, but I've never had any formal or comprehensive training on it. Ideally I'm looking for a book or two, and I don't mind if they start simple as long as they're comprehensive and recent enough to be relevant to modern RDBMSs.

jrochkind15• 13 years ago

I am not an ORM hater. I like ORMs a lot.

But the point that one needs to know SQL even if one is using an ORM is just obvious to me. It boggles and scares me that anyone thinks they can be a competent web developer without knowing SQL.

I was going to say "...if they use an rdbms, maybe they just use some NoSQL and can get away without it." But you know what, nope, not even that caveat -- if you don't know SQL and rdbms, you aren't going to be competent to know if some nosql is right the choice, or which one, either.

krsunny2• 13 years ago

I dont understand how its possible for someone to be the "best python programmer you know" yet that person doesn't know enough SQL to display data from a table...?

dragonwriter4• 13 years ago

> I dont understand how its possible for someone to be the "best python programmer you know" yet that person doesn't know enough SQL to display data from a table...?

Because knowing SQL doesn't make you a better Python programmer. It might make you a better application developer, but SQL knowledge is not a subset of Python knowledge.

krsunny2• 13 years ago

Agreed, however It still seems truly odd to me.

Learn SQL, dammit

Related Stories