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

91–100 of 118 posts

Re: Learn SQL, dammit

#91

Post by michaelt »

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…

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?

Re: Learn SQL, dammit

#92

Post by ScottWhigham »

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 don't know all of the syntax from all languages, but I do know that in Sybase and Microsoft SQL Server, the first would generate a syntax error. The HAVING and WHERE clauses cannot reference column aliases. You could rewrite it to be:

...

having sum(line_value) > 100;

Would the performance be the same? That's up to the individual DB. To say, "Yes, they would" implies that all DBMS vendors implement query plans the same way. I think that, fundamentally, they should perform the same way but again: it's not up to you or I; it's up to the people who wrote the query execution engine.

Re: Learn SQL, dammit

#93

Post by jrochkind1 »

jamesmiller5 wrote: post #64

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

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)

Re: Learn SQL, dammit

#94

Post by dragonwriter »

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.

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

Re: Learn SQL, dammit

#95

Post by dragonwriter »

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.

Re: Learn SQL, dammit

#96

Post by dragonwriter »

Tycho wrote: post #51

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.

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.

Re: Learn SQL, dammit

#97

Post by zzzeek »

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.

so add a trigger to your tables. How is an ORM getting in the way of that?

Re: Learn SQL, dammit

#98

Post by pfg »

chris_mahan wrote: post #48
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…

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!

Re: Learn SQL, dammit

#99

Post by 6cxs2hd6 »

zorbo wrote: post #65
archon wrote: post #21

Earlier quoted context omitted.

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.

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.

That's a great point. Part of learning SQL is learning what parts of it _not_ to use. Things like HAVING and ORDER BY are useful for ad hoc queries at a SQL command-line, but are either not useful or a waste when there's another level in the system like a BI tool.

Re: Learn SQL, dammit

#100

Post by tjr »

jrochkind1 wrote: post #87
tjr wrote: post #30

Earlier quoted context omitted.

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.

Fair enough. Replace "wrong" with "generally considered a suboptimal idea".
Post reply on HN