Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Learn SQL, dammit

gun.io

Post reply on HN
Print view View original

21–30 of 118 posts

Re: Learn SQL, dammit

#21

Post by archon »

pjungwir wrote: post #10

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.

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.

Re: Learn SQL, dammit

#22

Post by moron4hire »

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.

Re: Learn SQL, dammit

#24

Post by redcircle »

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.

Re: Learn SQL, dammit

#25

Post by moron4hire »

zzzeek wrote: post #15

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 expos…

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.

Re: Learn SQL, dammit

#26

Post by sehrope »

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...

Re: Learn SQL, dammit

#27

Post by rrreese »

untog wrote: post #3

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.

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...

Re: Learn SQL, dammit

#29

Post by itsybitsycoder »

pjungwir wrote: post #13

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 tr…

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...

Re: Learn SQL, dammit

#30

Post by tjr »

zzzeek wrote: post #15

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 expos…

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?
Post reply on HN