Solved

do these statements receive the same results - ordering of clauses

Posted on 2014-07-24
9
99 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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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 48

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

685 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