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

  • 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'

  • 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

  • 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';

  • Thanx.

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply