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

101–110 of 118 posts

Re: Learn SQL, dammit

#101

Post by redidas »

jrochkind1 wrote: post #90
michaelt wrote: post #66

Earlier quoted context omitted.

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.

I can help out with this! (Finally a hacker news discussion in my line of expertise!)

While you could think of nested subqueries as syntactic sugar, you might also want to think of them as a view you specify on the fly, or a "derived table" of information.

Each RDBMS optimizes a bit differently, but depending on your system subqueries may have query plan implications as well. Sometimes they'll make your query faster, other times slower. It all depends on the RDBMS, table indexes, and the operations you are doing inside the nested query.

Personally, I'd recommend using HAVING instead of using WHERE with a nested SUM. The query optimizer may create the same execution plan in the end, but the HAVING is a bit more explicit in what you are doing.

For those familiar with SQL, HAVING indicates you are filtering your query on an aggregate value, where as a WHERE indicates you are filtering records out of consideration before they are aggregated (as someone else as pointed out in another comment).

Re: Learn SQL, dammit

#102

Post by habitue »

addflip wrote: post #35

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

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.

Re: Learn SQL, dammit

#103

Post by untog »

silverbax88 wrote: post #86
untog wrote: post #70

Earlier quoted context omitted.

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.

SQL is rarely about a lot more than CRUD and tables. I would think that over 95% of the SQL code ever written has been CRUD operations.

ORMs exist to simplify CRUD operations. If you are trying to do something that is a not a CRUD operation then you'd be mad to try and use an ORM to do it.

Re: Learn SQL, dammit

#104

Post by untog »

jmmcd wrote: post #52
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.

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.

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()

Re: Learn SQL, dammit

#105

Post by krsunny »

dragonwriter wrote: post #95
krsunny wrote: post #89

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

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

Agreed, however It still seems truly odd to me.

Re: Learn SQL, dammit

#106

Post by coldtea »

derleth wrote: post #57
moron4hire wrote: post #25

Earlier quoted context omitted.

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.

Is C training wheels on assembly?

No. But an ORM is training wheels on SQL.

Re: Learn SQL, dammit

#107

Post by chris_mahan »

dragonwriter wrote: post #94
jmmcd wrote: post #52

Earlier quoted context omitted.

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.

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

Technically, an UPDATE is a DELETE and INSERT. so if you only knew SELECT, DELETE, and INSERT, you could do crud.

Re: Learn SQL, dammit

#108

Post by jrochkind1 »

tjr wrote: post #100
jrochkind1 wrote: post #87

Earlier quoted context omitted.

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.

Fair enough. Replace "wrong" with "generally considered a suboptimal idea".

Still, 'it depends'. Yeah, there are all sorts of reasons, depending on context (and plenty of people to argue all those reasons).

Personally, I agree with OP that nobody should ever use an ORM as an excuse to not know SQL. You need to know SQL anyway.

So, once we agree on that, your question "Is there any reason to use an ORM if I know SQL" (that's basically what you mean when you phrase it in the inverse "Is there any reason it would be suboptimal to NOT use an ORM", right?) -- basically just boils down to "Is there ever any reason to use an ORM?".

A topic which is basically the equivalent editor/OS war of db-based web development on HN or reddit. Meaning it's an argument that can and does go on forever, and you can find in-depth treatments of in many other threads.

Re: Learn SQL, dammit

#109

Post by chris_mahan »

pfg wrote: post #98
chris_mahan wrote: post #48

Earlier quoted context omitted.

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

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!

Sure. Note also that CROSS APPLY allows you to retrieve multiple columns, unlike an in-SELECT subquery (unless you want to have that query written multiple times)

Re: Learn SQL, dammit

#110

Post by prodigal_erik »

chris_mahan wrote: post #107
dragonwriter wrote: post #94

Earlier quoted context omitted.

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

Technically, an UPDATE is a DELETE and INSERT. so if you only knew SELECT, DELETE, and INSERT, you could do crud.

In practice, SQL access control is pretty simple-minded compared to relational algebra, and a server may not notice that your DELETE and INSERT are equivalent to an UPDATE which only modifies columns you're authorized to.
Post reply on HN