Solved

do these statements receive the same results - ordering of clauses

Posted on 2014-07-24
9
94 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 69

Expert Comment

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

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
Comment Utility
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
Comment Utility
With those " implicit " inner join mean?
0
 

Author Comment

by:al4629740
Comment Utility
"What does implicit inner join mean exactly?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now