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.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.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.)
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...
- 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?
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.
I'm happy too, no doubt... I guess, given how long it already "survived" SQL is quite "successful". Cheers