SQL AND and OR: Make the Intended Grouping Visible

The practice task

Return active applicants in either Design or Support from a fictional workshop volunteer list. An inactive Design applicant must not qualify simply because the department condition is true.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE volunteers(name TEXT, dept TEXT, active INTEGER);
INSERT INTO volunteers VALUES ('Ari','Design',1),('Bo','Design',0),('Cy','Support',1),('Dee','Research',1);

SELECT name FROM volunteers
WHERE (dept = 'Design' OR dept = 'Support') AND active = 1
ORDER BY name;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The parenthesized department test first defines the two allowed departments. The active condition then applies to that whole group. Ari and Cy qualify, while Bo fails the active test and Dee fails the department test.

Without the parentheses, dept = 'Design' OR dept = 'Support' AND active = 1 would allow any Design row, including inactive Bo. The active condition would bind only to the Support branch. The query could return convincing results while breaking the stated rule.

Translate a verbal requirement into grouped conditions before extending it. If the requirement later adds a date or region restriction, decide whether it applies to both departments or only one branch. Parentheses document that decision for the next person who edits the report.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Put string literals in single quotes.
• Test a row that satisfies only one side of each OR.
• Add a counterexample that would pass if parentheses were misplaced.