Share via

Readable SQL

  • Article

I read this article on SQL Code Layout and Beautification and can only agree with the author that other people's SQL often is hard to understand because I'm so used to how I write my SQL statements. One of the links is to an on-line tool formatting your SQL according to a number of rules. I was happy to find that my way of writing SQL was supported:

SELECT a,
b,
c AS d
FROM x,
y,
z AS w
WHERE a = 2
AND b IN (3,4,7)

And another example:

  SELECT obj.run,
obj.camcol,
STR(obj.field,3) AS field,
STR(obj.rowc,6,1) AS rowc,
STR(obj.colc,6,1) AS colc,
STR(dbo.FOBJ(obj.objid),4) AS id,
STR(obj.psfmag_g - 0 * obj.extinction_g,6,3) AS g,
STR(obj.psfmag_r - 0 * obj.extinction_r,6,3) AS r,
STR(obj.psfmag_i - 0 * obj.extinction_i,6,3) AS i,
STR(obj.psfmag_z - 0 * obj.extinction_z,6,3) AS z,
STR(60 * distance,3,1) AS d,
dbo.FFIELD(neighborobjid) AS nfield,
STR(dbo.FOBJ(neighborobjid),4) AS nid,
'new' AS 'new'
FROM (SELECT obj.objid,
run,
camcol,
field,
rowc,
colc,
psfmag_u,
extinction_u,
psfmag_g,
extinction_g,
psfmag_r,
extinction_r,
psfmag_i,
extinction_i,
psfmag_z,
extinction_z,
nn.neighborobjid,
nn.distance
FROM photoobj AS obj
JOIN neighbors AS nn
ON obj.objid = nn.objid
WHERE 60 * nn.distance BETWEEN 0 AND 15
AND nn.mode = 1
AND nn.neighbormode = 1
AND run = 756
AND camcol = 5
AND obj.TYPE = 6
AND (obj.flags & 0x40006) = 0
AND nchild = 0
AND obj.psfmag_i < 20
AND (g - r BETWEEN 0.3 AND 1.1
AND r - i BETWEEN -0.1 AND 0.6)) AS obj
JOIN photoobj AS nobj
ON nobj.objid = obj.neighborobjid
WHERE nobj.run = obj.run
AND (ABS(obj.psfmag_g - nobj.psfmag_g) < 0.5
OR ABS(obj.psfmag_r - nobj.psfmag_r) < 0.5
OR ABS(obj.psfmag_i - nobj.psfmag_i) < 0.5)
ORDER BY obj.run,
obj.camcol,
obj.field

Comments

  • Anonymous
    June 13, 2008
    And if you listened to me and started to write nice looking SQL , maybe you wanna look ate making your

  • Anonymous
    June 25, 2010
    Funny that you should post about formatting SQL, yet your code is poorly formatted..

  • Anonymous
    June 25, 2010
    @Ryan: Which code is poorly formatted? The examples? Something else?