Solved

TSQL - Order of evaluation

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now