Solved

TSQL - Order of evaluation

Posted on 2009-06-30
3
719 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

713 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