What is the "industry standard" for "filtering" a query set?

• NineIron

  SSChampion

  Points: 12537

  More actions

  July 6, 2017 at 10:58 am

  #322465

  What is the "industry standard" to write a query? Here are two examples. One has qualifiers in the where clause and the other as part of the join.

  


  selectRAM.AccountNumber,

  
  

  HRM.Name,

  
  

  HRM.Birthdate,

  
  

  HRM.Sex

  
  

  from livefdb.dbo.RegAcct_Main RAM

  
  

  inner join livefdb.dbo.HimRec_VisitData HRVD

  
  

  on RAM.SourceID=HRVD.SourceID

  
  

  and RAM.VisitID=HRVD.VisitID

  
  

  and RAM.PatientID=HRVD.PatientID

  
  

  and RAM.RegistrationType_MisRegTypeID=HRVD.VisitType_MisRegTypeID

  
  

  inner join livefdb.dbo.HimRec_Main HRM

  
  

  on RAM.SourceID=HRM.SourceID

  
  

  and RAM.PatientID=HRM.PatientID

  
  

  where RAM.SourceID='BRO'

  
  

  and HRM.Sex='F'

  
  

  and RAM.RegistrationType_MisRegTypeID='IN'

  selectRAM.AccountNumber,

  
  

  HRM.Name,

  
  

  HRM.Birthdate,

  
  

  HRM.Sex

  
  

  from livefdb.dbo.RegAcct_Main RAM

  
  

  inner join livefdb.dbo.HimRec_VisitData HRVD

  
  

  on RAM.SourceID=HRVD.SourceID

  
  

  and RAM.VisitID=HRVD.VisitID

  
  

  and RAM.PatientID=HRVD.PatientID

  
  

  and RAM.RegistrationType_MisRegTypeID=HRVD.VisitType_MisRegTypeID

  
  

  and RAM.SourceID='BRO'

  
  

  and RAM.RegistrationType_MisRegTypeID='IN'

  
  

  inner join livefdb.dbo.HimRec_Main HRM

  
  

  on RAM.SourceID=HRM.SourceID

  
  

  and RAM.PatientID=HRM.PatientID

  
  

  and HRM.Sex='F'

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 6, 2017 at 11:03 am

  #1949775

  a little easier to read for others to comment on

  SELECT RAM.AccountNumber,
   HRM.Name,
   HRM.Birthdate,
   HRM.Sex
FROM livefdb.dbo.RegAcct_Main RAM
  INNER JOIN livefdb.dbo.HimRec_VisitData HRVD ON RAM.SourceID = HRVD.SourceID
                  AND RAM.VisitID = HRVD.VisitID
                  AND RAM.PatientID = HRVD.PatientID
                  AND RAM.RegistrationType_MisRegTypeID = HRVD.VisitType_MisRegTypeID
  INNER JOIN livefdb.dbo.HimRec_Main HRM ON RAM.SourceID = HRM.SourceID
                AND RAM.PatientID = HRM.PatientID
WHERE RAM.SourceID = 'BRO'
  AND HRM.Sex = 'F'
  AND RAM.RegistrationType_MisRegTypeID = 'IN';

  SELECT RAM.AccountNumber,
   HRM.Name,
   HRM.Birthdate,
   HRM.Sex
FROM livefdb.dbo.RegAcct_Main RAM
  INNER JOIN livefdb.dbo.HimRec_VisitData HRVD ON RAM.SourceID = HRVD.SourceID
                  AND RAM.VisitID = HRVD.VisitID
                  AND RAM.PatientID = HRVD.PatientID
                  AND RAM.RegistrationType_MisRegTypeID = HRVD.VisitType_MisRegTypeID
                  AND RAM.SourceID = 'BRO'
                  AND RAM.RegistrationType_MisRegTypeID = 'IN'
  INNER JOIN livefdb.dbo.HimRec_Main HRM ON RAM.SourceID = HRM.SourceID
                AND RAM.PatientID = HRM.PatientID
                AND HRM.Sex = 'F';

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 6, 2017 at 11:07 am

  #1949777

  Not sure if there is an actual standard, but the way I like to work it is like this.  The join criteria between tables belong in the FROM clause and the filter criteria that limits the result set belong in the WHERE clause.  That means, using the code you posted, that this is the code I would write:

  
SELECT
  RAM.AccountNumber
  , HRM.Name
  , HRM.Birthdate
  , HRM.Sex
FROM
  livefdb.dbo.RegAcct_Main RAM
  INNER JOIN livefdb.dbo.HimRec_VisitData HRVD
    ON RAM.SourceID                          = HRVD.SourceID
       AND RAM.VisitID                       = HRVD.VisitID
       AND RAM.PatientID                     = HRVD.PatientID
       AND RAM.RegistrationType_MisRegTypeID = HRVD.VisitType_MisRegTypeID
  INNER JOIN livefdb.dbo.HimRec_Main HRM
    ON RAM.SourceID                          = HRM.SourceID
       AND RAM.PatientID                     = HRM.PatientID
WHERE
  RAM.SourceID                          = 'BRO'
  AND HRM.Sex                           = 'F'
  AND RAM.RegistrationType_MisRegTypeID = 'IN';

• NineIron

  SSChampion

  Points: 12537

  More actions

  July 6, 2017 at 11:10 am

  #1949779

  Thanx.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 6, 2017 at 11:37 am

  #1949786

  I agree with Lynn.
  Going a little bit further. Unless the conditions are set on a column from a table in an outer join, the conditions are on the WHERE clause. If the condition is on a table with an outer join, then the condition should go on the ON clause. e.g.
  
SELECT RAM.AccountNumber,
    HRM.Name,
    HRM.Birthdate,
    HRM.Sex
FROM livefdb.dbo.RegAcct_Main RAM
INNER JOIN livefdb.dbo.HimRec_VisitData HRVD
               ON RAM.SourceID = HRVD.SourceID
               AND RAM.VisitID = HRVD.VisitID
               AND RAM.PatientID = HRVD.PatientID
               AND RAM.RegistrationType_MisRegTypeID = HRVD.VisitType_MisRegTypeID
LEFT JOIN livefdb.dbo.HimRec_Main HRM
               ON RAM.SourceID = HRM.SourceID
               AND RAM.PatientID = HRM.PatientID
               AND HRM.Sex = 'F'
WHERE RAM.SourceID = 'BRO'
AND RAM.RegistrationType_MisRegTypeID = 'IN';


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2