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

71–80 of 118 posts

Re: Learn SQL, dammit

#71

Post by malkia »

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?

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.

Re: Learn SQL, dammit

#72

Post by pjungwir »

itsybitsycoder wrote: post #29
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...

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!

Re: Learn SQL, dammit

#73

Post by daigoba66 »

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.

Re: Learn SQL, dammit

#74

Post by tomku »

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.

Re: Learn SQL, dammit

#75

Post by nsxwolf »

papsosouid wrote: post #63
nsxwolf wrote: post #60

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

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

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

Re: Learn SQL, dammit

#76

Post by daigoba66 »

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

Re: Learn SQL, dammit

#77

Post by nissimk »

papsosouid wrote: post #63
nsxwolf wrote: post #60

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

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

Re: Learn SQL, dammit

#78

Post by papsosouid »

nsxwolf wrote: post #75
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.

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

More wild assumptions. The M in ORM stands for mapping. I do not map anything, the data comes back in tuples, and is used exactly as-is. Just because it gets stored in a variable doesn't mean there is any form of mapping going on.

Re: Learn SQL, dammit

#79

Post by jfb »

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…

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.

Re: Learn SQL, dammit

#80

Post by [deleted] »

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…

[deleted]
Post reply on HN