Control the order of operations in a SQL query

When executing SQL queries, it is important to understand and manage the order in which clauses are evaluated so you can produce the results you expect.

Below are two examples of the same query. The first example does not use clause grouping, and the returned results are not as expected. In the second example, clauses are grouped to force the correct order of operations to return the expected results.

In this scenario, imagine you are working with a global demographics database, and you want to find all the cities in China that either have at least 100,000 people, or are provincial capitals.

Example 1: No grouping

You can build this query with three clauses, as shown in the image below. When you run this query, it first finds all cities in China, and then filters Chinese cities that have more than 100,000 people. Next, based on the Or operator, it evaluates the entire dataset to return provincial capitals. The final results are Chinese cities larger than 100,000 people and any global city that is a provincial capital, which is not the intended result. This is because the And operator has a higher precedence than the Or operator, and so And is evaluated first.

Three clauses in a query, ungrouped

Example 2: Grouping

In this case, to return the intended results, the Or operator must be evaluated first. You can group clauses to force the order of operations. The clauses in a group are evaluated before clauses outside of the group.

Three clauses in a query, the last two are grouped

To group two or more clauses, press the Shift key as you click to select them, then click Group Clauses Group Clauses. To ungroup clauses, select a clause in a group and click Ungroup Clauses Ungroup Clauses.

With grouping applied, the query first evaluates the clauses in the group: whether a city has at least 100,000 people or is a provincial capital. Next, it evaluates whether it is a city in China. The And operator then ensures that the query correctly returns the cities that meet both criteria: Chinese cities that have at least 100,000 people or are provincial capitals.

Grouping clauses is equivalent to enclosing them in parentheses. In SQL mode, this query looks like this:

CNTRY_NAME = 'China' And (POP > 100000 Or STATUS = 'Provincial capital')

Related topics