Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Writing more legible SQL

craigkerstiens.com

Post reply on HN
Print view View original

91–100 of 168 posts

Re: Writing more legible SQL

#91

Post by blowski »

__jal wrote: post #86
combatentropy wrote: post #10

Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…

Yep. Same here. I find screaming one's reserved words far more distracting than the highlighting helps. Another SQL habit that seems to far more annoying than helpful is putting commas at the start of next line. Great, they line up. Did you know that publishers have a term for vertical patterns in blocks of text? They're called 'rivers', and one generally attempts to avoid them, because they're visually distracting.

One developer's 'river' is another's 'grid'. Being able to scan down quickly makes it easier to compare lines against each other. That's not something you do when reading a novel or a newspaper article, but it's something you do all the time when reading code.

Re: Writing more legible SQL

#92

Post by blowski »

bigato wrote: post #80

> CTEs >First, yes they can be an optimisation boundary. But they can also make your query much more read-able and prevent you from doing the wrong thing because you couldn’t reason about a query. In my experience, CTEs may be simpler for the writer to reason about, but they make the queries complicated for the reader to understand. If you use a CTE because you find the problem too complicated, chances are that you a…

Sometimes I understand the problem perfectly well, but a single beautiful query is not performant enough. Anything that can help to write and maintain big ugly queries without a cost in performance is welcome.

Re: Writing more legible SQL

#93

Post by 83457 »

1wd wrote: post #73
83457 wrote: post #32

Earlier quoted context omitted.

I think of joins as operators and from as the block similar to and/or in the where clause. He is being inconsistent with line breaks for select, from and where blocks. Personally I want to be able to visually pick out the blocks of the query and the easiest way to do that is with indention imo.

If "joins" are operators like "and", and you put "and" at the end of the line, shouldn't you put "join" at the end too? from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else

well I don't put and/or at the end of lines so there is that... but I have seen people but joins at the end of lines

Re: Writing more legible SQL

#94

Post by __jal »

garysieling wrote: post #70
tragomaskhalos wrote: post #62

Earlier quoted context omitted.

I have plenty of gripes about SQL but am not aware of any real alternatives ... which I do find bizarre - in the realm of programming languages in general we live in a time of Cambrian explosion in terms of the number and type of languages being used and developed, and yet SQL sits aloof and unassailable on its pedestal. There are a number of clear paths not taken: 1/ A new, better language that compiles down to SQL;…

I've wondered about this too. I think ORMs serve effectively the same purpose as Javascript transpilers (which gives you 1 & 2) You can also write stored procedures in Postgres in other languages.

ORMs attempt to bridge object and relational models. (With results that vary from "works fairly well" to "screw it, give me a DB handle and I'll just write SQL".) I see what you're saying, but they operate at a different place than generators/translators.

Stored procedures are just that - they get a name and persistence. PLSQL has procedural extensions, but it is still SQL.

Fabian Pascal (who has made a career out of SQL criticism) proposed a replacement system called Raquel that would have a "real" set-theory-based query language. I don't think it went anywhere.

My best guess as to why there aren't many alternatives: SQL is both mature and Good Enough(tm), but importantly, also unsexy and complicated. Database engine development is more similar to kernel development than, say, web application development, and there are simply far fewer engineers qualified to do it. Building some framework in Javascript currently gets a ton of love, and in comparison is far easier than developing a SQL replacement that is roughly as performant as SQL and either less awkward linguistically or functionally superior.

People do take up those sorts of projects, but they're pretty lonely unless some combination of talent, luck, and timing turns into rock star status (Linus wasn't the only one writing unix clones at the time).

Re: Writing more legible SQL

#96

Post by __jal »

blowski wrote: post #91
__jal wrote: post #86

Earlier quoted context omitted.

Yep. Same here. I find screaming one's reserved words far more distracting than the highlighting helps. Another SQL habit that seems to far more annoying than helpful is putting commas at the start of next line. Great, they line up. Did you know that publishers have a term for vertical patterns in blocks of text? They're called 'rivers', and one generally attempts to avoid them, because they're visually distracting.

One developer's 'river' is another's 'grid'. Being able to scan down quickly makes it easier to compare lines against each other. That's not something you do when reading a novel or a newspaper article, but it's something you do all the time when reading code.

Maybe I'm a weirdo. I find it massively distracting. About the only time column-alignment is helpful is when scanning for typos, but that's handled pretty capably in other ways by most editors more sophisticated than Text Edit.

Re: Writing more legible SQL

#97

Post by douche »

stadeschuldt wrote: post #90

Good post. I use one of Jetbrains IDEs and I am big fan of their auto-formatting. You select the dialect of your SQL (or connect it to the data source) and the IDE almost formats it ideally. Sometimes I enforce certain line breaks but for the most part it does a really good job.

DataGrip is pretty excellent from them. The only thing I'm not really happy with is it's support for diagramming table structure, but I haven't found anything that really does a good job of that.

Re: Writing more legible SQL

#98

Post by falcolas »

One thing missing that I can't recommend more highly - a comment in the SQL clause indicating where in the code it is being called from. These comments are invaluable when debugging or doing performance tuning of queries, especially when you have a large codebase.

i.e.

    SELECT *
    FROM students
    WHERE 1
    /* somemodule.somemethod */
As for query formatting, as with everything, consistency in style is more important than a dogmatic style guide. My time as a DB admin has given me some specific style guidelines, typically stolen from the formatting provided by the DB itself. More specifically, I match my style to what you see when you run a command like `SHOW CREATE TABLE students;`. I tend to reserve my indentation for things like subqueries.

Re: Writing more legible SQL

#99

Post by cptskippy »

combatentropy wrote: post #10

Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…

If I came across your SQL, I'd probably hunt you down and give you a hug. There's so much horridly formatted code and it's unusual to see a developer care.

I prefer to uppercase command syntax to make stand apart visually from the parameters of the query. I don't agree with your conjunctions at the end of the line, I actually prefer commas at the beginning of the next line though I don't do that so as to conform to convention. I also use the AS keyword to explicitly denote aliases.

If I had it my way, all SQL would look like this:

    SELECT t1.col1
          ,t2.col2
          ,t3.col3
    FROM table1 AS t1
    JOIN table2 AS t2 
        ON t1.col2 = t2.col1
    JOIN table3 AS t3
        ON t1.col3 = t3.col1
        AND t3.col2 = something_else
    WHERE t1.col1 > 0
        AND t2.col2  t1.col4
    ORDER BY col2
    LIMIT 100

Re: Writing more legible SQL

#100

Post by neves »

combatentropy wrote: post #10

Here is how I write SQL: select t1.col1, t2.col2, t3.col3 from table1 t1 join table2 t2 on t1.col2 = t2.col1 join table3 t3 on t1.col3 = t3.col1 and t3.col2 = something_else where t1.col1 > 0 and t2.col2 t1.col4 order by col2 limit 100 So: 1. SQL capitalization is not sacred. I lowercase everything. 2. I just indent subclauses, with four spaces, like I indent other languages. I don't go out of my way to line up thing…

You didn't mentioned the most important thing to make readable SQL and that is demonstrated in your clause: use the "join" keyword instead of doing the join in the "where" clause.

I can always auto-reformat a complex SQL in a IDE, but if it isn't using join clauses, it will stink.

Post reply on HN