4 ms·
I think this would really benefit from some thoughtful indenting to illustrate the structure. For example, instead of: SELECT members.firstname AS "First N
by hadley 10y ago
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
- Semiapies 10y agoSome 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.
- sbuttgereit 10y agoSometimes 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.
- Semiapies 10y agoI 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.)
- joepvd 10y agoFully 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...
- klibertp 10y agoSQL 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.
- joepvd 10y agoFully 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...
- tuna-piano 10y agoOne of my favorite tools is a SQL auto formatter, http://poorsql.com/ http://poorsql.com/ . Also available as an add-in for notepad++, Visual Studio and SSMS.
- pointernil 10y agoMaybe 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?
- plasticsyntax 10y agoSQL 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.
- pointernil 10y agoAha! 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
- paulmd 10y agoYou're right on, this is exactly the styling I use with SQL. Indentation makes all the difference in at-a-glance readability.