?
Solved

TSQL - Order of evaluation

Posted on 2009-06-30
3
Medium Priority
?
724 Views
Last Modified: 2012-05-07
Hi

I am quite new to SQL and want to confirm check how the order of evaluation works for the below statement. I want to ensure it pulls the right data. It involves four tables tblEmployee, tblBookingActual, tblBookingRequested and tlkpCostCentre.

I understand that AND statements take priority over OR statements. But how can you ensure AND statements are evaluated in the correct order? Is it simple the order  they are placed in the SQL statement and the how you place the Parentheses ( )?
SELECT     dbo.tlkpCostCentre.Grouping, dbo.tlkpCostCentre.Area, dbo.tblBookingActual.CostCentre, dbo.tblBookingActual.PayrollNo, 
                      COUNT(dbo.tblBookingActual.BookingRef) AS [Number of Bookings for UnQualified], @StartDate AS StartDate, @EndDate AS EndDate
FROM         dbo.tblEmployee INNER JOIN
                      dbo.tblBookingActual ON dbo.tblEmployee.PayrollNo = dbo.tblBookingActual.PayrollNo INNER JOIN
                      dbo.tlkpCostCentre ON dbo.tblBookingActual.CostCentre = dbo.tlkpCostCentre.CostCentre INNER JOIN
                      dbo.tblBookingRequested ON dbo.tblBookingActual.BookingRef = dbo.tblBookingRequested.BookingRef
WHERE     (dbo.tblBookingActual.PayrollNo = 'NON02') AND (dbo.tblBookingRequested.RequiredDate BETWEEN @StartDate AND @EndDate) AND 
                      (dbo.tblEmployee.Grade IN ('BAND 5', 'BAND 5 / 5', 'BAND 6', 'BAND 6 / 6', 'BAND 7', 'BAND 7 / G', 'BAND 8', 'D', 'E', 'F', 'G', 'H'))
GROUP BY dbo.tlkpCostCentre.Grouping, dbo.tlkpCostCentre.Area, dbo.tblBookingActual.CostCentre, dbo.tblBookingActual.PayrollNo
ORDER BY dbo.tlkpCostCentre.Grouping, dbo.tlkpCostCentre.Area, dbo.tblBookingActual.CostCentre

Open in new window

0
Comment
Question by:adypips
[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
  • 2
3 Comments
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 750 total points
ID: 24743774
"And" gets priority on "OR", if you have multiple "AND"s than it will go on left to right basis but be informed that parenthases always gets highest priority.
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 750 total points
ID: 24743786
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 750 total points
ID: 24743808
To find out how your query works exactly in your environment, you have to view the explain plan of the query:

In SSMS, Right click your Query Execution Pane and Click on Include Actual Execution plan and execute the query now.

Execution plan created will clearly display how your query is executed.

As far as your operator precedence doubt is cleared already.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

777 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