Solved

do these statements receive the same results - ordering of clauses

Posted on 2014-07-24
9
101 Views
Last Modified: 2014-07-28
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

0
Comment
Question by:al4629740
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 40217638
run both queries in SSMS with the Execution plan set to on and check the plans. They might be identical.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 40217646
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40217647
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40217652
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 40217673
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
 

Author Comment

by:al4629740
ID: 40217920
With those " implicit " inner join mean?
0
 

Author Comment

by:al4629740
ID: 40218075
"What does implicit inner join mean exactly?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40218431
It means that even though you have asked for a LEFT JOIN you are actually doing an INNER JOIN.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40218506
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

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question