News for hackers. Stuff that matters.
Re:A beginners guide to thinking in SQL by hadley on Monday July 11, 2016 @06:30PM (#12075324)
I think this would really benefit from some thoughtful indenting to illustrate the structure. For example, instead of:

  SELECT
  members.firstname AS "First Name",
  members.lastname AS "Last Name"
  FROM borrowings
  JOIN books ON borrowings.bookid=books.bookid
  JOIN members ON members.memberid=borrowings.memberid
  WHERE books.author='Dan Brown';
Do:

  SELECT
    members.firstname AS "First Name",
    members.lastname AS "Last Name"
  FROM borrowings
    JOIN books ON borrowings.bookid = books.bookid
    JOIN members ON members.memberid = borrowings.memberid
  WHERE books.author = 'Dan Brown';
This helps reinforce that there is one FROM statement that combines three tables using joins
Re:A beginners guide to thinking in SQL by Semiapies on Monday July 11, 2016 @07:44PM (#12075654)
Some time ago, I settled on a fairly similar idiosyncratic indentation style for SQL:

  select
    members.firstname as "First Name",
    members.lastname as "Last Name"
  from 
    borrowings
    join 
    books on borrowings.bookid = books.bookid
    join 
    members on members.memberid = borrowings.memberid
  where 
    books.author = 'Dan Brown';
Normally, I'm a fairly slavish follower of style standards, but almost every bit of SQL I come across in my job is an unreadable mess, even when whoever wrote it did have the vague idea that consistent formatting was good.
Re:A beginners guide to thinking in SQL by sbuttgereit on Tuesday July 12, 2016 @12:31AM (#12076745)
Sometimes with complex queries, it can be tough to adhere to a simple set of style rules while also making clear where things like logical units start/stop. Don't get me wrong I'm not disagreeing with you in principle; but acknowledging that the declarative nature of the language cause you to break style for the sake of readability in some cases. I'm not going to post an example of such a case (I'd have to go digging and I'm too tired).

On a different note, I don't like a lot of the styles I do see. I like yours, with the exception of the joins. My convention is a bit different:

  SELECT
      members.firstname as "First Name"
     ,members.lastname as "Last Name"
  FROM borrowings
    JOIN books 
        ON borrowings.bookid = books.bookid
    JOIN members 
        ON members.memberid = borrowings.memberid
  WHERE books.author = 'Dan Brown';
Some of that is old habit (like the comma first thing which I know is out of favor with many) which I found useful when working with long column lists. I'd also typically and systematically alias the tables, but that wasn't done in the examples. Outside of tastes, the only real downside I run into is that it's easy to get too far to the right with the indentation style I use. But it's not that often that I get there.
Re:A beginners guide to thinking in SQL by Semiapies on Wednesday July 13, 2016 @01:21PM (#12087932)
I haven't had trouble with complicated queries with this style. I've been doing it for ages, though.

There nothing wrong with comma-first, it's sensible; I just never got into the habit. My joins are so I can instantly glance over the tables involved in a query.

(And, oh yes, I, too, normally alias the crap out of tables if there's more than one in a query.)

Re:A beginners guide to thinking in SQL by tuna-piano on Monday July 11, 2016 @11:03PM (#12076455)
One of my favorite tools is a SQL auto formatter, http://poorsql.com/ . Also available as an add-in for notepad++, Visual Studio and SSMS.
Re:A beginners guide to thinking in SQL by joepvd on Monday July 11, 2016 @07:46PM (#12075661)
Fully agree.

Why is it, that SQL generally is so poorly commented? Almost every time I see an SQL script, I cringe for the poor formatting. It's generally not like the writers of these scripts are poor at formatting other code...

Re:A beginners guide to thinking in SQL by klibertp on Tuesday July 12, 2016 @06:59AM (#12078054)
SQL has a very large and complex syntax, which makes it hard to come up with a set of formatting rules that fit at least most of the statements. I think the only way to get readable SQL is to use a DSL-based SQL builder for your language - this decouples syntax from meaning and makes good formatting much easier. It also fits much better with your main language.
Re:A beginners guide to thinking in SQL by pointernil on Tuesday July 12, 2016 @06:55AM (#12078040)
Maybe a little off topic:

- What's the (hi)story behind SQL putting the SELECT clause first? What are/were the benefits?

- What's the (hi)story behind SQL even differentiating between FROM and JOIN? Isn't it in the end: those are the tables, combine them in this way into one data set?

Any hints? pointers?

Re:A beginners guide to thinking in SQL by plasticsyntax on Wednesday July 13, 2016 @06:55AM (#12085228)
SQL uses a natural language syntax.

Regarding your first question - In English, most people would agree that "grab the beer from the fridge" is more natural than "from the fridge grab the beer". For what it's worth I've always considered this aspect of SQL maddening and I always start with SELECT * and work my way back later.

Regarding your second, this is probably because FROM means something different from JOIN. FROM indicates you are starting a block of JOINED tables. Technically if the syntax wasn't natural it might look like:

SELECT * FROM JOIN FOO ON NOTHING JOIN BAR ON FOO.ID = BAR.FooID

Given how annoyingly verbose the syntax is already I'm happy to go with what we were given.

Re:A beginners guide to thinking in SQL by pointernil on Wednesday July 13, 2016 @07:39AM (#12085373)
Aha! The natural English grammar pointer is a nice one... sure enough I now! remember the "natural language syntax" idea ;)

I'm happy too, no doubt... I guess, given how long it already "survived" SQL is quite "successful". Cheers

Re:A beginners guide to thinking in SQL by paulmd on Wednesday July 13, 2016 @08:12PM (#12090799)
You're right on, this is exactly the styling I use with SQL. Indentation makes all the difference in at-a-glance readability.