do these statements receive the same results - ordering of clauses

I have two querys below.  The way it is expressed seems to give the same results.  Except example two below is more orderly as to where each expression relates to.  r.Board and Hours > 0 are moved to up in the query statement.  Is there any rule to this?

SELECT r.RegID,r.[LastName], r.[FirstName], SUM(h.hours) as Hours, h.Program, o.[Agency],r.Sector
        FROM tblOrgRegistrations AS r
              LEFT JOIN tblOrgHours AS h 
                    ON h.RegID = r.RegID 
                   AND Month(ActivityDate) = 7
                    LEFT JOIN tblOrgActivities AS a 
                    ON h.ActivityID = a.ActivityID 
              LEFT JOIN tblOrgProfile AS o 
                    ON h.AgencyID = o.AgencyID Where o.AgencyID = '74' And r.Board = 1 And Hours > 0 And a.ActivityName = 'Board Meeting'
                Group By r.RegID, o.[Agency],h.Program, r.FirstName, r.LastName, r.Sector order by r.LastName

Open in new window



SELECT r.RegID,r.[LastName], r.[FirstName], SUM(h.hours) as Hours, h.Program, o.[Agency],r.Sector
        FROM tblOrgRegistrations AS r
              LEFT JOIN tblOrgHours AS h 
                    ON h.RegID = r.RegID 
                   AND Month(ActivityDate) = 7 And r.Board = 1
                    LEFT JOIN tblOrgActivities AS a 
                    ON h.ActivityID = a.ActivityID And Hours > 0
              LEFT JOIN tblOrgProfile AS o 
                    ON h.AgencyID = o.AgencyID Where o.AgencyID = '74' And a.ActivityName = 'Board Meeting'
                Group By r.RegID, o.[Agency],h.Program, r.FirstName, r.LastName, r.Sector order by r.LastName

Open in new window

al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
run both queries in SSMS with the Execution plan set to on and check the plans. They might be identical.
0
Éric MoreauSenior .Net ConsultantCommented:
I would guess that your first query might be more efficient as your WHERE clause is limiting the number of rows returned on the main table (r)
0
Anthony PerkinsCommented:
They are two different queries:
In the first query even though you state:
LEFT JOIN tblOrgHours AS h ON h.RegID = r.RegID  AND MONTH(ActivityDate) = 7

If it is in fact an implicit INNER JOIN as you have included Hours > 0 in the WHERE clause.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
The same logic applies to both queries for:
LEFT JOIN tblOrgProfile AS o ON h.AgencyID = o.AgencyID
LEFT JOIN tblOrgActivities AS a ON h.ActivityID = a.ActivityID

They are both implicit INNER JOINs
0
Anthony PerkinsCommented:
If those are truly LEFT JOINs then they should be written as:
SELECT  r.RegID,
        r.[LastName],
        r.[FirstName],
        SUM(h.[hours]) AS Hours,
        h.Program,
        o.[Agency],
        r.Sector
FROM    tblOrgRegistrations AS r
        LEFT JOIN tblOrgHours AS h ON h.RegID = r.RegID
                                      AND MONTH(ActivityDate) = 7
                                      AND h.[Hours] > 0
        LEFT JOIN tblOrgActivities AS a ON h.ActivityID = a.ActivityID AND a.ActivityName = 'Board Meeting'
        LEFT JOIN tblOrgProfile AS o ON h.AgencyID = o.AgencyID AND o.AgencyID = '74'
WHERE   r.Board = 1
GROUP BY r.RegID,
        o.[Agency],
        h.Program,
        r.FirstName,
        r.LastName,
        r.Sector
ORDER BY r.LastName

Open in new window


Also, always include all aliases it makes it easier to read.  e.g. Hours and ActivityDate
0
al4629740Author Commented:
With those " implicit " inner join mean?
0
al4629740Author Commented:
"What does implicit inner join mean exactly?
0
Anthony PerkinsCommented:
It means that even though you have asked for a LEFT JOIN you are actually doing an INNER JOIN.
0
PortletPaulfreelancerCommented:
The reason for having a left join is to permit unmatched rows. If you reference a left joined table in the where clause and do not then also allow for a NULL, you create the same conditions as having used an inner join. Follow the examples below, or got to http://sqlfiddle.com/#!3/9f3be/2 
CREATE TABLE Table1
    ([ID] int, [Name] varchar(6))
;
    
INSERT INTO Table1
    ([ID], [Name])
VALUES
    (1, 'Fred'),
    (2, 'Wilma'),
    (3, 'Barney'),
    (4, 'Betty')
;

CREATE TABLE Table2
    ([ID] int, [Discount] int)
;
    
INSERT INTO Table2
    ([ID], [Discount])
VALUES
    (2, 100),
    (3, 10),
    (4, 85)
;

**Query 1**:

-- implied inner join, referenced left join table in where clause
SELECT
      t1.id
    , t1.name
    , t2.discount
FROM table1 t1
      LEFT JOIN table2 t2
                  ON t1.id = t2.id
WHERE t2.discount > 0


**[Results][2]**:

| ID |   NAME | DISCOUNT |
|----|--------|----------|
|  2 |  Wilma |      100 |
|  3 | Barney |       10 |
|  4 |  Betty |       85 |


**Query 2**:

-- inner join without where clause
SELECT
      t1.id
    , t1.name
    , t2.discount
FROM table1 t1
      INNER JOIN table2 t2
                  ON t1.id = t2.id


**[Results][3]**:

| ID |   NAME | DISCOUNT |
|----|--------|----------|
|  2 |  Wilma |      100 |
|  3 | Barney |       10 |
|  4 |  Betty |       85 |


**Query 3**:

-- allow NULLS from the join in the where clause
SELECT
      t1.id
    , t1.name
    , t2.discount
FROM table1 t1
      LEFT JOIN table2 t2
                  ON t1.id = t2.id
WHERE ( t2.discount > 0 OR t2.discount IS NULL )


**[Results][4]**:

| ID |   NAME | DISCOUNT |
|----|--------|----------|
|  1 |   Fred |   (null) |
|  2 |  Wilma |      100 |
|  3 | Barney |       10 |
|  4 |  Betty |       85 |



[1]: http://sqlfiddle.com/#!3/9f3be/2

[2]: http://sqlfiddle.com/#!3/9f3be/2/0

[3]: http://sqlfiddle.com/#!3/9f3be/2/1

[4]: http://sqlfiddle.com/#!3/9f3be/2/2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.