Solved

TSQL - Order of evaluation

Posted on 2009-06-30
3
716 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
  • 2
3 Comments
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 250 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 250 total points
ID: 24743786
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

832 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