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

81–90 of 118 posts

Re: Learn SQL, dammit

#81

Post by nsxwolf »

nissimk wrote: post #77
papsosouid wrote: post #63

Earlier quoted context omitted.

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

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

Great, you have a list of dictionaries of dictionaries. Now the user has updated some of the items inside one of the nested dictionaries.

The user clicks save. Now what? You've got to get that change to the data in the dictionary inserted into the correct place in the relational database.

You're going to write code to do by hand what an ORM wants to do for you.

Re: Learn SQL, dammit

#83

Post by jrochkind1 »

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.

Re: Learn SQL, dammit

#84

Post by jfb »

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.

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.

Re: Learn SQL, dammit

#85

Post by miahi »

obviouslygreen wrote: post #14
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 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…

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

Re: Learn SQL, dammit

#86

Post by silverbax88 »

untog wrote: post #70
silverbax88 wrote: post #43

Earlier quoted context omitted.

I'm still amazed that there are developers who would rather spend more time maintaining/debugging/optimizing ORM code in a mature app that just learning a simple INSERT statement.

who would rather spend more time maintaining/debugging/optimizing ORM code But that's kind of the point- with a decent ORM library there isn't really much work to do. model = MyModel.load(id) Add a new field to your model? It'll handle it. It might even modify the table for you. With raw SQL you'd have to go and edit your UPDATE and INSERT statements each time. Seems like a lot more maintenance/potential debugging to…

Okay, add a messaging trigger on update and insert between two joined permission tables, without creating a mutating trigger.

SQL is about a lot more than CRUD and tables.

Re: Learn SQL, dammit

#87

Post by jrochkind1 »

tjr wrote: post #30
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?

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.

Re: Learn SQL, dammit

#88

Post by tom_b »

sehrope wrote: post #26

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

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

Re: Learn SQL, dammit

#89

Post by krsunny »

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

Re: Learn SQL, dammit

#90

Post by jrochkind1 »

michaelt wrote: post #66
ScottWhigham wrote: post #19

Earlier quoted context omitted.

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

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…

I believe you are correct, except possibly the other way around -- nested queries are just syntactic sugar for joins, group by, and having. :)

I'm actually not sure what I said is true (you can possibly do more with nested queries than you could do with just joins and group by/having?), but I DO think group by/having came before nested queries in rdbms implementations.

Post reply on HN