Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

TSQL - Order of evaluation

Posted on 2009-06-30
3
Medium Priority
?
727 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 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

885 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