Re:Sqlphobia: The Irrational Fear of SQL
by bfung on Sunday April 04, 2010 @06:54PM
(#1240952)
>"Give me all the blog posts with 8 replies written by users with a karma more than 42."
SELECT P.*
FROM blog.Posts P
INNER JOIN ( --postid and count of replies
SELECT P.PostId, COUNT(UserReplies.PostId) NumReplies
FROM blog.Posts P
INNER JOIN ( --Replies with users karma more than 42
SELECT R.PostId
FROM blog.Replies R
INNER JOIN blog.Users U ON R.UserId = U.UserId
WHERE U.Karma > 42
) UserReplies ON P.PostId = UserReplies.PostId
GROUP BY P.PostId
) PostReplies ON P.PostId = PostReplies.PostId
WHERE PostReplies.NumReplies > 8
After writing the query (3 min, while eating pizza), there's obviously much room for optimization in table design and query design. I doubt the ORM'ed developer would be able to see the optimizations not understanding how to write the SQL (so they can reason about indices, aggregations, etc...).
My take is, most people fall into the trap of programming language X is THE language of this application. If an application has a relational database component to it, it'd be best if there was someone who understood SQL and the connection points to the application.
My take is, most people fall into the trap of programming language X is THE language of this application. If an application has a relational database component to it, it'd be best if there was someone who understood SQL and the connection points to the application.