PDA

View Full Version : Indenting SQL... what works for you?


Banana
2009-12-18, 14:56
A bit of silly question since it's really a question of style & preferences. However my problem is that I've been unhappy with my indecision of how to handle the formatting. In any other programming languages, it's easy enough to 'get in the groove' and follow along. With SQL, it just doesn't work.

Few problem spots to illustrate:

Subqueries or flow construct in SELECT list

SELECT colA,
colB,
(SELECT colA
FROM tableB
WHERE x) colC
FROM tableA
...

SELECT colA,
colB,
(
SELECT colA
FROM tableB
WHERE x
) colC
FROM tableA...

SELECT colA,
colB,
(SELECT colA FROM tableB WHERE x) colC
FROM tableA
...


None of those feel quite appealing to me as they break up the nice little flow... I also have similar problem whenever I use CASE WHEN...


Using derived tables

...
FROM (
SELECT
....
) A
JOIN (
SELECT
....
) B
ON A.x = B.x
JOIN (
SELECT
...
) C
ON A.x = C.x

I really like this with exception for the dangling ON clause aftwerad, yet I usually see it written as:

FROM (
SELECT
....
) A
JOIN (
SELECT
...
) B
ON A.x = B.x
JOIN (
SELECT
...
) C
ON B.x = C.x

Can't say I like it, especially when the joins may not be necessarily hierarchical.

Those are two most glaring issues that I always end up feeling bad because I wasn't consistent everytime I write SQL code involving such constructs...

So, what do you do? Do you just wing the mother?

chucker
2009-12-18, 15:07
For subqueries, the first, or the third if the subquery is simple enough. Too lazy to type up for other situations.

Brad
2009-12-18, 16:35
For select subqueries, the second or third, depending on how big the subquery is.

For join subqueries, something kinda like the first:
SELECT
col1,
col2,
col3
FROM
table1
JOIN table2 USING col4
JOIN table2 USING col5
JOIN (
SELECT col6, col7 FROM table 3
) USING col6
JOIN (
SELECT
col7,
col8,
CASE
WHEN col10 = 'foo' THEN 'BAR'
ELSE 'BAZ'
END AS col19
FROM table 4
) USING col7
WHERE
...


However, and this is a big "however", I always try to steer clear of subqueries. If you find yourself doing tons of subqueries, it's likely you're taking the wrong approach to the problem or have a poor database design, and your resulting mega-query will probably execute relatively slowly compared to a cleaner design.

Banana
2009-12-18, 18:03
Brad, is alias truly optional for derived tables in PostgreSQL? If so, that must be the only RDBMS I know to not require alias for the derived table. If alias were required, would you just put it between the closing ) and USING/ON clause?

As for subqueries, I wouldn't say I do "tons of subqueries" and agree with the general principle that subqueries shouldn't be the first thing to reach for, and when I do, I always feel a bit dirty due to resulting ugly SQL, hence the whole point of this thread. ;)

FWIW, out of various flavors of subqueries, the 'derived tables' is most commonly used (by me), subqueries in SELECT list distant second, and subqueries in WHERE (of which 96% of the time will be some kind of correlated subqueries --blech!) even further third. But even so, the 'derived table' is pretty few and far in between- usually to pre-process calculated fields which then subsequently get used in outer query being a good example.

Of course, it also doesn't help when I have to put up with great design from Oracle guys.... :|

Brad
2009-12-20, 15:31
Brad, is alias truly optional for derived tables in PostgreSQL? If so, that must be the only RDBMS I know to not require alias for the derived table. If alias were required, would you just put it between the closing ) and USING/ON clause?
Ah, sorry about that. I just threw together that as a bogus example without paying too close attention. Yeah, I think Postgres does require aliases on the derived tables like that.

Of course, it also doesn't help when I have to put up with great design from Oracle guys.... :|
Yeah, I haven't had the "fortune" of using it myself, but I hear no shortage of great things about the way Oracle works from a couple of coworkers who used it in a previous life. Is it true that Oracle just slaps on a bunch of extra generic text columns to all of its tables to facilitate "customization" of the schema?

Banana
2009-12-20, 16:24
Ah, sorry about that.

No problem. Would have been a nice feature.. If only to cut on gratuitous amount of aliasing. :p

Yeah, I haven't had the "fortune" of using it myself, but I hear no shortage of great things about the way Oracle works from a couple of coworkers who used it in a previous life. Is it true that Oracle just slaps on a bunch of extra generic text columns to all of its tables to facilitate "customization" of the schema?

Hmmm. Can't say I have seen that kind of design except for Microsoft Sharepoint (which is powered by SQL Server and does just as what you described - of course they say to not touch it...) . The design I am stuck with is usually along this lines:

1) 50-100+columns being the average numbers of column in a table

2) columns that may look like this:
TOT_FCST_QTY
FCST_QTY_1
FCST_QTY_2
FCST_QTY_3
FCST_QTY_4
...
FCST_QTY_12

then being told to not use the TOT_FORECAST_QTY because I can't be sure it's right! Storing calculation is bad enough, but to make it unreliable?!? :wtf:

3) Several different databases tracking different things even though they are facts about same thing. For example, I need to retrieve data from one database that details the inventory supply, then other database that details factory production. Of course, I can't join them as is because they don't share anything in common. For example, one database may have codes such as 120-120352 while the other database stores in two separate columns, 120 and 120352. In fact there's a "Rosetta stone" to aid in the translation. But why even have the fucking one in first place?!?!? Data dictionary, man!! :wtf: :wtf:

4) It's common for them to have several columns that technically describes an "event". For example:

QTY_PRODUCED
QTY_SHIPPED
QTY_RECEIVED
QTY_RETURNED

which then requires several UPDATE queries to keep the values up to the date as we change. Of course none of those stuff happen in a single transaction (e.g. there could be several shipments spread across weeks for instance) so there's lot of churning going on sifting the values whenever much as *anything* coughs or swallows! :wtf: :wtf: :wtf:

I don't know about you, but I usually feel that UPDATE queries is generally most risky and error-prone operations in comparison to inserting new records detailing new "events" or "transactions". It's one thing to use UPDATE to fix some input mistakes but entirely differently to do a bulk UPDATEs (which in turns triggers even more UPDATEs) for an single event that has many side effects.

Now, I'll grant them that this may not be a OLTP database but more of data warehousing. Except... it's treated as a friggin' DSS (which is ta-da! OLTP!) :mad:

I've long ago decided that the only thing Oracle had going for them was their marketing department. I have no faith in the Oracle consultants' ability to create anything resembling normalized database.


Oh, did I rant? I'm so sorry... :o

chucker
2009-12-20, 16:34
It's cool. You make some of the worst database designs I've seen look brilliant. Submit your stuff to thedailywtf.com. :D

hflomberg
2009-12-20, 22:32
Consistency and Shop standards!

worked that way in COBOL and IMS!