Operator precedence in WHERE in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Understanding AND and OR precedence in WHERE clause

Given the table Employees with columns id, department, and salary, what rows will this query return?

SELECT * FROM Employees WHERE department = 'Sales' OR department = 'HR' AND salary > 50000;

SQL

CREATE TABLE Employees (id INT, department VARCHAR(20), salary INT);
INSERT INTO Employees VALUES
(1, 'Sales', 40000),
(2, 'HR', 60000),
(3, 'HR', 45000),
(4, 'Sales', 70000),
(5, 'IT', 55000);

A Rows with id 1, 2, and 4

B Rows with id 1, 3, and 4

C Rows with id 2 and 4 only

D Rows with id 1, 2, 3, and 4

💡 Hint

Remember that AND has higher precedence than OR in SQL WHERE clauses.

The condition is evaluated as: department = 'Sales' OR (department = 'HR' AND salary > 50000). So rows where department is 'Sales' are included regardless of salary, plus rows where department is 'HR' and salary is greater than 50000. That matches rows with id 1 (Sales, 40000), 2 (HR, 60000), and 4 (Sales, 70000).

❓ query_result

intermediate

Effect of parentheses on operator precedence

Using the same Employees table, what rows will this query return?

SELECT * FROM Employees WHERE (department = 'Sales' OR department = 'HR') AND salary > 50000;

SQL

CREATE TABLE Employees (id INT, department VARCHAR(20), salary INT);
INSERT INTO Employees VALUES
(1, 'Sales', 40000),
(2, 'HR', 60000),
(3, 'HR', 45000),
(4, 'Sales', 70000),
(5, 'IT', 55000);

A Rows with id 1, 2, and 4

B Rows with id 2 and 4 only

C Rows with id 1, 3, and 4

D Rows with id 2, 3, and 5

💡 Hint

Parentheses change the order in which conditions are evaluated.

Here, the condition means rows where department is either 'Sales' or 'HR' AND salary is greater than 50000. So only rows with id 2 (HR, 60000) and 4 (Sales, 70000) satisfy both conditions.

📝 Syntax

advanced

Identify the syntax error in WHERE clause

Which option contains a syntax error in the WHERE clause due to incorrect operator precedence or missing parentheses?

A SELECT * FROM Employees WHERE department = 'Sales' AND salary > 50000 OR department = 'HR';

B SELECT * FROM Employees WHERE (department = 'Sales' AND salary > 50000) OR department = 'HR';

C SELECT * FROM Employees WHERE department = 'Sales' AND (salary > 50000 OR department = 'HR');

D SELECT * FROM Employees WHERE department = 'Sales' AND salary > 50000 OR;

💡 Hint

Look for incomplete or misplaced operators.

Option D ends with an OR operator without a following condition, causing a syntax error. The other options are syntactically valid even if their logic differs.

❓ optimization

advanced

Optimizing WHERE clause with mixed AND and OR

Which query is logically equivalent but optimized for clarity and performance when filtering employees from 'Sales' or 'HR' departments with salary over 50000?

A SELECT * FROM Employees WHERE salary > 50000 AND (department = 'Sales' OR department = 'HR');

B SELECT * FROM Employees WHERE (department = 'Sales' OR department = 'HR') AND salary > 50000;

C SELECT * FROM Employees WHERE department = 'Sales' OR department = 'HR' AND salary > 50000;

D SELECT * FROM Employees WHERE salary > 50000 OR (department = 'Sales' AND department = 'HR');

💡 Hint

AND has higher precedence than OR, but grouping conditions can improve readability.

Options B and C are logically equivalent, but C places the salary condition first, which can help the database engine filter rows faster if salary is indexed. Option A has different logic due to operator precedence. Option A is logically incorrect.

🧠 Conceptual

expert

Operator precedence impact on query results

Consider the query:

SELECT * FROM Employees WHERE department = 'Sales' OR department = 'HR' AND salary > 50000 OR department = 'IT' AND salary < 60000;

Which of the following best describes how SQL evaluates this WHERE clause?

A SQL evaluates all conditions simultaneously without precedence rules.

B SQL evaluates OR operators first, then AND operators, ignoring parentheses.

C SQL evaluates AND operators first, then OR operators from left to right, grouping conditions accordingly.

D SQL evaluates all operators strictly left to right without precedence.

💡 Hint

Recall the standard operator precedence rules in SQL WHERE clauses.

In SQL, AND has higher precedence than OR. So the conditions with AND are evaluated first, then combined with OR operators from left to right. Parentheses can override this order.