Sample SQLs

Sample 1   Sample 2   Sample 3   Sample 4  

SELECT o.*,
       Isnull(nbor.nearest,999) AS nearest
FROM   ( 
       -- This selects the white dwarf candidates, meeting the following criteria
       -- 1) Stars with dereddened g magnitudes between 15 and 20
       -- 2) Proper motion > 2 arcsec/century
       -- 3) Meet either a reduced proper motion cut, or have dereddened g-i < 0
       -- A left outer join is also performed to fetch the spectroscopic information
       -- for those stars with spectra.
       SELECT p.objid,
              p.psfmag_g - p.extinction_g + 5 * LOG(u.propermotion / 100.) + 5 AS rpm,
              p.psfmag_g - p.extinction_g - (p.psfmag_i - p.extinction_i) AS gi,
              Isnull(s.plate,0) AS plate,
              Isnull(s.mjd,0) AS mjd,
              Isnull(s.fiberid,0) AS fiberid
        FROM   phototag p
               JOIN usno u
                 ON p.objid = u.objid
               LEFT OUTER JOIN specobj s
                 ON p.objid = s.bestobjid
        WHERE  p.TYPE = dbo.Fphototype('Star')
               AND (p.flags & dbo.Fphotoflags('EDGE')) = 0
               AND (p.psfmag_g - p.extinction_g) BETWEEN 15
                                                         AND 20
               AND u.propermotion > 2.
               AND (p.psfmag_g - p.extinction_g + 5 * LOG(u.propermotion / 100.) + 5 > 16.136 + 2.727 * (p.psfmag_g - p.extinction_g - (p.psfmag_i - p.extinction_i))
                     OR p.psfmag_g - p.extinction_g - (p.psfmag_i - p.extinction_i) < 0.)) AS o
       LEFT OUTER JOIN ( 
                       SELECT   n.objid,
                                MIN(n.distance) AS nearest
                        FROM     neighbors n
                                 JOIN phototag x
                                   ON n.neighborobjid = x.objid
                        WHERE    n.TYPE = dbo.Fphototype('Star')
                                 AND n.mode = dbo.Fphotomode('Primary')
                                 AND n.neighbormode = dbo.Fphotomode('Primary')
                                 AND (x.TYPE = dbo.Fphototype('Star')
                                       OR x.TYPE = dbo.Fphototype('Galaxy'))
                                 AND x.modelmag_g BETWEEN 10
                                                          AND 21
                        GROUP BY n.objid) AS nbor
         ON o.objid = nbor.objid


SQL before beautify

SELECT 
o.*, ISNULL(nbor.nearest,999) as nearest 
FROM 
     ( 
-- This selects the white dwarf candidates, meeting the following criteria 
-- 1) Stars with dereddened g magnitudes between 15 and 20 
-- 2) Proper motion > 2 arcsec/century 
-- 3) Meet either a reduced proper motion cut, or have dereddened g-i < 0 
-- A left outer join is also performed to fetch the spectroscopic information 
-- for those stars with spectra. 
SELECT p.objID, 
 p.psfMag_g - p.extinction_g + 5 * log(u.propermotion / 100.) + 5 AS rpm, 
 p.psfMag_g - p.extinction_g - (p.psfMag_i - p.extinction_i) AS gi, 
      ISNULL(s.plate,0) as plate, ISNULL(s.mjd,0) as mjd, 
      ISNULL(s.fiberID,0) as fiberID 
FROM       PhotoTag p JOIN USNO u ON p.objID = u.objID 
      LEFT OUTER JOIN SpecObj s ON p.objID = s.bestObjID 
WHERE       p.type = dbo.fPhotoType('Star')  AND (p.flags & dbo.fPhotoFlags('EDGE')) = 0 
      AND (p.psfMag_g - p.extinction_g) BETWEEN 15 AND 20       AND u.propermotion > 2. 
      AND (p.psfMag_g - p.extinction_g + 5 * log(u.propermotion / 100.) + 5 > 
           16.136 + 2.727 * (p.psfMag_g - p.extinction_g - 
           (p.psfMag_i - p.extinction_i)) OR 
      p.psfMag_g - p.extinction_g - (p.psfMag_i - p.extinction_i) < 0.) 
      ) AS o 
      LEFT OUTER JOIN 
      ( 
SELECT n.objID, MIN(n.distance) AS nearest 
FROM Neighbors n JOIN PhotoTag x ON n.neighborObjID = x.objID 
WHERE n.type = dbo.fPhotoType('Star') AND 
      n.mode = dbo.fPhotoMode('Primary') AND 
      n.neighborMode = dbo.fPhotoMode('Primary') AND 
      (x.type = dbo.fPhotoType('Star') OR x.type = dbo.fPhotoType('Galaxy')) 
      AND x.modelMag_g BETWEEN 10 AND 21 
GROUP BY n.objID 
      ) AS nbor ON o.objID = nbor.objID