WHERE, AND, OR and explicit precedence

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

WHERE, AND, OR and explicit precedence

Concept and contract

Translate combined business rules into predicates that admit exactly the intended records.

A filter is a logical contract, not a collection of search words. The worked request means active roles in either Ukraine or Israel. It combines the active requirement with a parenthesized choice of countries. Each output row must satisfy the whole expression.

Before writing SQL, mark qualifying fixture identities by hand: 1, 2 and 6. Role 7 is Ukrainian but archived; role 5 is Israeli but archived. They are useful negative cases because a country match alone must not admit them.

Mechanism and worked example

WHERE keeps rows whose expression is true.

WHERE keeps rows whose expression is true. AND binds more tightly than OR in the engines used here, but explicit parentheses communicate the intended business grouping without requiring the reader to remember precedence. The two country alternatives form one condition.

Evaluate the expression on role 5: its country matches IL, while active is zero. The grouped country condition is true, but the complete AND expression is false. Walking one excluded row is often a better explanation than reciting operator rules.

Complete worked example

sql

SELECT id FROM roles
WHERE active = 1 AND (country = 'UA' OR country = 'IL')
ORDER BY id;

Reasoning: Parentheses keep both country alternatives under the active requirement.

Counterexamples and common mistakes

The counterexample drops parentheses.

The counterexample drops parentheses. It effectively means active Ukrainian roles or any Israeli role, so archived role 5 leaks into the result. The query still parses and may look plausible when tested only with active Ukrainian rows.

Test both positive and negative boundaries of each condition. If you later add a title search, decide whether it combines with AND or introduces another parenthesized alternative. Do not repair incorrect logic by applying DISTINCT or filtering only in the user interface.

Counterexample: a plausible but wrong query

sql

SELECT id FROM roles
WHERE active = 1 AND country = 'UA' OR country = 'IL'
ORDER BY id;

Reasoning: The ungrouped IL branch admits an archived row.

Independent practice: small query

Find active US roles with a known salary of at least 100.

Find active US roles with a known salary of at least 100. Predict identity 4. Role 3 is active and US but has unknown salary; role 8 is active with a known zero but is Canadian. These rows separate the three requirements.

Write one predicate per requirement and combine them with AND. You do not need a join or grouping. State what an unknown salary does under the comparison; the next lesson explores that behavior in more detail rather than treating it as zero.

Step 1: Mark each requirement separately. Step 2: Intersect the three sets of matching identities.

Practice starter: solve before reading the solution

sql

SELECT id FROM roles
WHERE /* active AND US AND salary threshold */
ORDER BY id;

Reasoning: Combine three independent requirements.

Hint 1: Find active US rows first.
Hint 2: Then apply salary >= 100 without coalescing unknown values.

Solution, tests and trade-offs

The solution admits only role 4.

The solution admits only role 4. Comparing the exact identity rejects both an overly broad country rule and a query returning the right count for the wrong role. The salary threshold is inclusive, so a value exactly 100 would qualify in the selected country.

Use the unmodified teaching fixture for the first acceptance check. As a stretch, add an active US role at exactly 100 in a disposable copy and predict its inclusion. Keep that modified fixture separate so other lesson expectations remain reproducible.

Practice solution and acceptance checks

sql

SELECT id FROM roles
WHERE active = 1 AND country = 'US' AND salary >= 100
ORDER BY id;

Reasoning: Identity 4 is the only shared match.

Interview reasoning and next step

In a technical conversation, translate the predicate back into plain language and test an adversarial row.

In a technical conversation, translate the predicate back into plain language and test an adversarial row. This catches the common gap between a syntactically valid expression and a correct business rule. Explain why an archived row in an allowed country is still excluded.

Filtering belongs before unrelated presentation choices. Sorting and limiting a wrong population merely presents the wrong answer neatly. Next, inspect SQL unknown values so that comparisons and negative conditions remain correct when fields are missing.

Optional stretch — after the core task works: Add a threshold-boundary fixture and explain why it should qualify.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.

Translate combined business rules into predicates that admit exactly the intended records.