Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

do these statements receive the same results - ordering of clauses

Posted on 2014-07-24
9
Medium Priority
?
103 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 1000 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 1000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

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
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

577 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