Prerequisites: This module assumes you can read a SELECT statement and know what a table, a row, and a foreign key are. It does not teach SQL from scratch. It teaches you to reason about what a query returns, which is a different skill from writing one.
Before you read anything: make a call
The task: return all products that have never been ordered.
The schema has two tables: products (id, name) and order_items (order_id, product_id).
The sample data has 3 products, all of which have at least one order. Both queries below return an empty result set on the test data. But only one is correct in the general case.
Query A:
SELECT p.id, p.name
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.product_id IS NULL;Query B:
SELECT p.id, p.name
FROM products p
WHERE p.id NOT IN (SELECT product_id FROM order_items);Both return the same output on the sample data. Which one is correct, and what condition causes the other to fail?
See the answer
Query A is correct. Query B is wrong, and it will fail silently in production.
NOT IN with a subquery breaks when the subquery returns any NULL values. If any row in order_items has a NULL product_id (which happens in real data with optional foreign keys or incomplete imports), then NOT IN (...) evaluates to NULL (unknown) for every product. The entire query returns zero rows, even for products that have no orders at all.
The sample data has no NULL product_id values, so the bug is invisible during testing. On clean test data, Query B looks fine. On production data, it silently drops valid results.
Query A uses the LEFT JOIN ... WHERE IS NULL pattern: the standard safe approach for “rows in A with no match in B.” It explicitly checks for the absence of a match rather than relying on subquery behavior that NULL can corrupt.
This is the hardest class of SQL bug to catch: a query that executes without error, returns plausible results on test data, and produces wrong results on real data. Your job as an annotator is to reason about what a query does, not just whether it runs.
Read a query for what it returns rather than whether it runs
Most SQL annotation mistakes come from treating queries the same way a linter does: checking whether they’ll execute. That’s the wrong frame.
AI-generated SQL frequently executes without error and returns the wrong answer. A query that returns 847 rows when the correct answer is 1,000 rows produces no error message. A LEFT JOIN silently replaced with INNER JOIN will drop rows and say nothing about it. NOT IN with a subquery will return zero results if the subquery contains a NULL, and the only way to catch it is to know how NULL behaves in SQL.
Your job is to read a query and reason about what it will return, including what it gets wrong quietly.
Fundamentals you must know without reference
SELECT, WHERE, GROUP BY, HAVING
The most common AI errors happen at the GROUP BY/HAVING boundary. The rule:
WHEREfilters rows before aggregationHAVINGfilters after aggregation, on aggregate results likeCOUNT,SUM,AVG
Common AI error:
-- WRONG: aggregate function in WHERE clause
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE COUNT(*) > 10
GROUP BY department;
-- CORRECT: use HAVING for post-aggregation filters
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;The first query raises a syntax error in most databases. The AI generates it anyway. You need to spot it, and more importantly, explain why it’s wrong in your rationale.
JOIN types and their semantics
| JOIN type | Returns |
|---|---|
| INNER JOIN | Only rows with matches in both tables |
| LEFT JOIN | All rows from left table; NULLs for unmatched right rows |
| RIGHT JOIN | All rows from right table; NULLs for unmatched left rows |
| FULL OUTER JOIN | All rows from both tables; NULLs where no match |
| CROSS JOIN | Cartesian product: every combination of rows |
AI models frequently use INNER JOIN when the task semantics require LEFT JOIN. The symptom: the query silently drops rows for customers, orders, or users that have no matching record in the joined table. If the task says “show all customers and their order counts,” customers with no orders should appear with a count of 0. That requires LEFT JOIN, not INNER JOIN.
NULL semantics
NULL in SQL means unknown. It does not mean zero, an empty string, or false. This has consequences that catch annotators constantly.
Any comparison with NULL returns NULL:
NULL = 5 -- NULL (not FALSE)
NULL != 5 -- NULL (not TRUE)
NULL = NULL -- NULL (not TRUE)To test for NULL, you must use IS NULL or IS NOT NULL.
The WHERE clause implication: WHERE status != 'cancelled' will silently exclude rows where status is NULL, because NULL != 'cancelled' evaluates to NULL, not TRUE. If those rows should be included, the correct filter is:
WHERE status != 'cancelled' OR status IS NULLThis is one of the most common silent correctness errors in AI-generated SQL.
Try It: INNER JOIN vs. LEFT JOIN
The task: “Write a SQL query that returns each customer’s name and the total number of orders they have placed. Include customers who have placed no orders: show 0 for them.”
The AI generates this query against a customers table and an orders table, where orders.customer_id references customers.id:
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;Is this query correct? If not, what is the specific error and how should it be fixed?
See answer
The query is incorrect. INNER JOIN will silently exclude any customer who has no orders, because INNER JOIN only returns rows where a match exists in both tables. Customers with no orders have no rows in orders, so they are dropped from the result entirely.
The task explicitly requires including customers with zero orders. No error message will appear; the query simply returns fewer rows than required. This is a semantic correctness error.
Correct query:
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;LEFT JOIN preserves all rows from the left table (customers). Where no matching order exists, o.id is NULL, and COUNT(o.id) correctly returns 0 because COUNT ignores NULLs.
One subtlety worth noting in your rationale: COUNT(*) would return 1 for unmatched rows, not 0. COUNT(o.id) is correct here because it counts non-NULL values only. If the AI used COUNT(*) instead, that’s a second error on top of the JOIN type.
Window functions
Window functions are a common source of AI errors and a reliable signal of query sophistication:
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;Key distinctions between the three ranking functions:
RANK(): tied rows get the same rank; next rank is skipped (1, 1, 3)DENSE_RANK(): tied rows get the same rank; no ranks skipped (1, 1, 2)ROW_NUMBER(): always unique sequential numbers regardless of ties (1, 2, 3)
An AI that uses RANK() when the task requires unique row numbering (or DENSE_RANK() when the task requires gap-skipping) produces a semantically wrong result even though the query executes without error.
Try It: WHERE and HAVING in the same query
A task asks for: “the average salary per department, counting only full-time employees, and showing only departments where that average exceeds 60,000.” The AI writes:
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING employment_type = 'full_time' AND AVG(salary) > 60000;The query runs on the test data and returns rows. Is it correct? If not, what is the specific error and how should it be fixed?
See answer
The query is incorrect. It puts a row-level filter in HAVING, where it does not belong.
The task has two filters that operate at different stages. “Counting only full-time employees” restricts which rows feed the average, so it belongs in WHERE, before aggregation. “Departments where the average exceeds 60,000” tests an aggregate result, so it belongs in HAVING, after aggregation. The AI collapsed both into HAVING.
Why this is worse than a plain syntax error. employment_type is not in the GROUP BY and is not wrapped in an aggregate. Strict dialects (PostgreSQL, and MySQL with ONLY_FULL_GROUP_BY enabled) reject this outright. Permissive ones (SQLite, older MySQL defaults) accept it and evaluate employment_type against an arbitrarily chosen row from each group, so the query silently filters departments on one unpredictable employee’s status. That is the failure pattern this module keeps returning to: it runs, it returns plausible rows, and it is wrong.
Correct query:
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE employment_type = 'full_time'
GROUP BY department
HAVING AVG(salary) > 60000;Note that the WHERE filter also changes the average itself, not only which rows are visible. Part-time salaries are excluded from the computation, which is what the task asked for. An annotator who moves the filter but does not say why the resulting number differs has found the bug without explaining its consequence.
Why the rationale matters: “This query has a syntax error” is less useful than “employment_type is a row-level predicate, so it belongs in WHERE; HAVING is for filtering on aggregate results after GROUP BY. As written the query is either rejected or evaluated against an arbitrary row per group, and it also computes the average over part-time employees the task excluded.” The first tells a reviewer the query breaks. The second tells the model which clause does what, and what it costs to confuse them.
Schema reading
When evaluating AI SQL, read the schema before reading the query. Common schema-related errors:
Wrong table: The AI joins orders to products when it should join order_items to products, because orders doesn’t have a direct product foreign key, it goes through order_items.
Hallucinated column names: AI generates customer_name when the schema has first_name and last_name as separate columns. The query references a column that doesn’t exist.
Aggregation without grouping: SELECT department, AVG(salary) FROM employees without GROUP BY department is invalid in most SQL dialects, or returns a single row with an arbitrary department value.
Reading the schema takes 30 seconds. It catches a class of errors that no amount of SQL knowledge will help you spot without it.
Data science tasks pair SQL with pandas code
Data science tasks often pair SQL with Python pandas code. The evaluation principles are the same: reason about what the code will return, not only whether it runs.
groupby with aggregation:
# Common AI mistake: not resetting the index
result = df.groupby('department')['salary'].mean()
# Returns a Series with department as index, not a clean DataFrame
# Correct for most downstream use:
result = df.groupby('department')['salary'].mean().reset_index()Merging DataFrames: The pandas equivalent of SQL JOINs. pd.merge(df1, df2, on='id', how='left'): the how parameter mirrors SQL JOIN semantics. An AI that defaults to how='inner' (the pandas default) when the task requires preserving all rows from one table is making the same semantic error as the INNER JOIN case above.
Handling missing data: df.dropna() versus df.fillna(0) have very different effects on aggregate statistics. An AI that silently drops NaN rows before computing a mean may produce a misleading result. The task might require acknowledging that data is missing rather than removing it.
Pandas is covered here at the level you’ll encounter in most annotation tasks. If a task requires deeper pandas work (complex transformations, time series, multi-index operations), treat it as Specialist-tier and evaluate accordingly.
Common SQL evaluation mistakes by annotators
Accepting a query that looks right. SQL that executes and returns rows is not necessarily correct. Always reason about what the query should return and whether the approach achieves it.
Ignoring NULL handling. NULL semantics are non-obvious and AI models get them wrong frequently. Check any WHERE clause involving inequality or NOT IN for potential NULL issues.
Not reading the schema. An AI may write a perfectly syntactic query against a different table structure than the one provided. Check column names, foreign key paths, and table relationships before evaluating the query logic.
Accepting the sample output as proof. As the opening exercise showed, a query can return the correct result on clean test data and produce wrong results on real data. Sample output is a starting point, not a verdict.
SQL flashcards
WHERE filters individual rows and runs before GROUP BY. HAVING filters aggregated groups and runs after. Using WHERE to filter on an aggregate is a syntax error.
LEFT JOIN returns all rows from the left table plus matches from the right, with NULLs where nothing matches. INNER JOIN would silently drop the left-table rows with no match.
RANK() ties rows equally then skips the next rank. DENSE_RANK() ties equally without skipping, so the sequence stays gapless. ROW_NUMBER() gives every row a unique number regardless of ties.
Any comparison with NULL (=, !=, <, >) returns NULL, not TRUE or FALSE. Testing for it requires IS NULL or IS NOT NULL.
AI SQL errors that produce no error message and return plausible-but-wrong results are the hardest to catch, like an INNER JOIN that silently drops rows the task needed included.
SQL treats NULL as a grouping value: every row where the GROUP BY column IS NULL lands in one group together.
- Read the schema before the query: Column names, foreign key paths, and table structure must match the query. A syntactically valid query that references the wrong table or a hallucinated column name is a correctness failure, and you won’t catch it without reading the schema first.
- Where to go next: Python for AI Training covers the code-side equivalent of these silent correctness failures.
