AP Subject A #14: SQL joins, aggregation, and NULL

This independent study material is not affiliated with or endorsed by IPA. It uses no past questions or third-party commentary and offers no pass guarantee.

Learning index

• Previous: AP13 — keys and normalization (we use only its PK/FK schema connection).
• This installment: scope · terms · data · SELECT · joins · grouping · ON vs WHERE · NULL · NOT IN · duplicates · quick sheet · questions · review
• Next: AP15 — transactions, locking, and ACID (deferred; not introduced here).

1. Official scope

Source: IPA, 応用情報技術者試験（レベル3）シラバス Ver.7.2, https://www.ipa.go.jp/shiken/syllabus/omgdg50000005kq5-att/syllabus_ap_ver7_2.pdf (checked 2026-10-21; PDF pp. 43–44, printed footer pp. 39–40).

Hierarchy: 大分類3 技術要素 → 中分類9 データベース → 2. データベース設計 → (2) ③ データベースの論理設計 (NULL), and 3. データ操作 → (1) relational operations and (2) ② (b) data manipulation language (SELECT). This lesson covers SELECT/WHERE, joins, grouping, aggregation, NULL, duplicates, and ordering. AP13 supplies the normalized-key connection only; transaction processing beginning on PDF p. 44 (printed footer p. 40) belongs to AP15.

2. Terminology

Korean	Japanese	English
선택 / 조건절	選択 / WHERE句	selection / WHERE clause
내부 조인	内部結合	inner join
왼쪽 외부 조인	左外部結合	left outer join
그룹화 / 그룹 조건	グループ化 / HAVING句	grouping / HAVING clause
집계 함수	集約関数	aggregate function
널(NULL)	NULL（ヌル）	NULL
3값 논리	3値論理	three-valued logic (3VL)
중복 제거	重複排除	duplicate elimination
정렬	整列	ordering
상관 서브쿼리	相関副問合せ	correlated subquery

3. Fixed data

DEPARTMENT(dept_id PK, dept_name) and EMPLOYEE.dept_id connect through the AP13 PK/FK idea; the FK is nullable. We do not revisit normalization.

DEPARTMENT.dept_id	dept_name
10	Sales
20	Dev
30	HR
40	Legal

emp_id	emp_name	dept_id	bonus
1	Aki	10	100
2	Bo	10	NULL
3	Chika	20	200
4	Dai	NULL	50
5	Emi	20	NULL
6	Fumi	30	100

BLOCKED_DEPT(dept_id nullable) contains exactly (20) and (NULL).

Logical query-processing intuition is FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This is a semantic aid, not a claim about the DBMS’s physical execution order.

4. SELECT, WHERE, and ORDER BY

SELECT emp_id, emp_name, bonus
FROM EMPLOYEE
WHERE bonus >= 100
ORDER BY bonus DESC, emp_id ASC;

emp_id	emp_name	bonus
3	Chika	200
1	Aki	100
6	Fumi	100

WHERE retains only TRUE. Comparison with Bo’s and Emi’s NULL is UNKNOWN, so they disappear. The second ordering key makes the 100-point tie deterministic.

5. INNER JOIN and LEFT JOIN

An inner join keeps matches only:

SELECT e.emp_id, e.emp_name, d.dept_name
FROM EMPLOYEE AS e
INNER JOIN DEPARTMENT AS d ON d.dept_id = e.dept_id
ORDER BY e.emp_id;

emp_id	emp_name	dept_name
1	Aki	Sales
2	Bo	Sales
3	Chika	Dev
5	Emi	Dev
6	Fumi	HR

Dai has no TRUE equality match (NULL = value is UNKNOWN), so is omitted. A left join preserves every left row and NULL-extends missing right columns:

SELECT e.emp_id, e.emp_name, d.dept_id, d.dept_name
FROM EMPLOYEE AS e
LEFT JOIN DEPARTMENT AS d ON d.dept_id = e.dept_id
ORDER BY e.emp_id;

emp_id	emp_name	dept_id	dept_name
1	Aki	10	Sales
2	Bo	10	Sales
3	Chika	20	Dev
4	Dai	NULL	NULL
5	Emi	20	Dev
6	Fumi	30	HR

6. GROUP BY, HAVING, and COUNT

Follow portable standard grouping: every selected nonaggregate column is in GROUP BY.

SELECT d.dept_id, d.dept_name,
       COUNT(*) AS joined_rows,
       COUNT(e.emp_id) AS employee_count,
       COUNT(e.bonus) AS known_bonus_count
FROM DEPARTMENT AS d
LEFT JOIN EMPLOYEE AS e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;

dept_id	dept_name	joined_rows	employee_count	known_bonus_count
10	Sales	2	2	1
20	Dev	2	2	1
30	HR	1	1	1
40	Legal	1	0	0

COUNT(*) counts rows; COUNT(expression) counts only rows where the expression is non-NULL. Legal has one NULL-extended joined row, hence 1, 0, 0.

WHERE filters rows before grouping; HAVING filters groups after aggregation:

SELECT d.dept_id, d.dept_name, COUNT(e.emp_id) AS employee_count
FROM DEPARTMENT AS d
LEFT JOIN EMPLOYEE AS e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING COUNT(e.emp_id) >= 2
ORDER BY d.dept_id;

dept_id	dept_name	employee_count
10	Sales	2
20	Dev	2

The aggregate expression is repeated in HAVING; alias-in-HAVING support varies.

7. A right-side condition: ON versus WHERE

Putting e.bonus >= 150 in ON controls which right rows match while preserving all departments:

SELECT d.dept_id, d.dept_name, e.emp_name, e.bonus
FROM DEPARTMENT AS d
LEFT JOIN EMPLOYEE AS e
  ON e.dept_id = d.dept_id AND e.bonus >= 150
ORDER BY d.dept_id, e.emp_id;

dept_id	dept_name	emp_name	bonus
10	Sales	NULL	NULL
20	Dev	Chika	200
30	HR	NULL	NULL
40	Legal	NULL	NULL

Putting it in WHERE first forms the left join and then rejects both nonqualifying and NULL-extended rows:

SELECT d.dept_id, d.dept_name, e.emp_name, e.bonus
FROM DEPARTMENT AS d
LEFT JOIN EMPLOYEE AS e ON e.dept_id = d.dept_id
WHERE e.bonus >= 150
ORDER BY d.dept_id, e.emp_id;

dept_id	dept_name	emp_name	bonus
20	Dev	Chika	200

This null-rejecting right-side predicate makes this particular result resemble an inner join. Do not generalize that every WHERE condition turns every left join into an inner join.

8. NULL and three-valued logic

SQL predicates can be TRUE, FALSE, or UNKNOWN. WHERE and HAVING retain only TRUE.

Expression	Result
100 >= 100	TRUE
NULL >= 100	UNKNOWN
TRUE AND UNKNOWN	UNKNOWN
FALSE AND UNKNOWN	FALSE
TRUE OR UNKNOWN	TRUE

bonus = NULL and bonus <> NULL are not NULL tests: their result is UNKNOWN. Use IS NULL or IS NOT NULL.

SELECT emp_id, emp_name
FROM EMPLOYEE
WHERE bonus IS NULL
ORDER BY emp_id;

emp_id	emp_name
2	Bo
5	Emi

9. NOT IN with NULL

The tempting query returns no rows:

SELECT e.emp_id, e.emp_name
FROM EMPLOYEE AS e
WHERE e.dept_id NOT IN (SELECT dept_id FROM BLOCKED_DEPT)
ORDER BY e.emp_id;

emp_id	emp_name
(no rows)

Conceptually x NOT IN (20, NULL) requires x <> 20 AND x <> NULL. For non-20 values the latter is UNKNOWN; for 20 it is FALSE; a NULL employee department is also UNKNOWN. None is TRUE.

Filter NULL out of the subquery:

SELECT e.emp_id, e.emp_name
FROM EMPLOYEE AS e
WHERE e.dept_id NOT IN
      (SELECT dept_id FROM BLOCKED_DEPT WHERE dept_id IS NOT NULL)
ORDER BY e.emp_id;

emp_id	emp_name
1	Aki
2	Bo
6	Fumi

Dai still fails because NULL NOT IN (20) is UNKNOWN. A correlated NOT EXISTS asks whether no TRUE equality match exists:

SELECT e.emp_id, e.emp_name
FROM EMPLOYEE AS e
WHERE NOT EXISTS (
  SELECT 1 FROM BLOCKED_DEPT AS b WHERE b.dept_id = e.dept_id
)
ORDER BY e.emp_id;

emp_id	emp_name
1	Aki
2	Bo
4	Dai
6	Fumi

For Dai, no equality is TRUE, so NOT EXISTS is TRUE. If the business intent excludes unknown departments, add e.dept_id IS NOT NULL:

SELECT e.emp_id, e.emp_name
FROM EMPLOYEE AS e
WHERE e.dept_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM BLOCKED_DEPT AS b WHERE b.dept_id = e.dept_id
  )
ORDER BY e.emp_id;

emp_id	emp_name
1	Aki
2	Bo
6	Fumi

The two NOT EXISTS versions express different intent; neither is universally “the” replacement.

10. Duplicates, DISTINCT, and order

SQL preserves duplicates unless asked to remove them:

SELECT dept_id
FROM EMPLOYEE
WHERE dept_id IS NOT NULL
ORDER BY dept_id;

dept_id
10
10
20
20
30

SELECT DISTINCT dept_id
FROM EMPLOYEE
WHERE dept_id IS NOT NULL
ORDER BY dept_id;

dept_id
10
20
30

Without ORDER BY, row order is undefined. Default NULL placement and support for NULLS FIRST/LAST differ by DBMS. A portable NULL-last pattern with a deterministic tie-breaker is:

SELECT emp_id, emp_name, bonus
FROM EMPLOYEE
ORDER BY CASE WHEN bonus IS NULL THEN 1 ELSE 0 END,
         bonus DESC,
         emp_id ASC;

emp_id	emp_name	bonus
3	Chika	200
1	Aki	100
6	Fumi	100
4	Dai	50
2	Bo	NULL
5	Emi	NULL

Examples were deterministically verified with SQLite. Core NULL, join, and COUNT semantics are standard SQL; exact explain plans, collation/case sensitivity, default NULL placement, and some syntax vary by DBMS.

11. Quick sheet and traps

Need	Remember
filter rows / groups	WHERE before grouping; HAVING after
preserve the left side	LEFT JOIN; put match restrictions in ON when preservation is intended
count joined entities	COUNT(right_key), not blindly COUNT(*)
test NULL	IS NULL / IS NOT NULL
exclude subquery values	define NULL intent; beware NULL-bearing NOT IN
remove duplicates	DISTINCT
deterministic output	explicit ORDER BY plus tie-breaker

Traps: equality to NULL; assuming NULL is zero/blank; selecting an ungrouped nonaggregate column; relying on alias-in-HAVING; mistaking logical order for physical execution; assuming DISTINCT sorts; assuming every right-side filter belongs in WHERE; or assuming rows have a natural order.

12. Original practice questions

All five are original four-option questions, not past questions. Labels are ア/イ/ウ/エ.

Q1 — SELECT/WHERE output

For the query in section 4, which ordered employee-ID list is returned?

• ア. 1, 3, 6
• イ. 3, 1, 6
• ウ. 3, 1, 2, 5, 6
• エ. 3, 6, 1

Answer: イ (2). ア ignores descending bonus order. イ is correct: 200 first, then the 100 tie by ID. ウ incorrectly includes NULL bonuses. エ reverses the ascending ID tie-breaker.

Q2 — ON versus WHERE

Which statement correctly compares the two section 7 queries?

• ア. Both return only Dev/Chika.
• イ. The WHERE version preserves all four departments.
• ウ. The ON version returns no row for Legal.
• エ. The ON version returns four department rows, while the WHERE version returns only Dev/Chika.

Answer: エ (4). ア ignores left preservation in ON. イ reverses the behavior. ウ is false because Legal is NULL-extended. エ exactly matches the traced tables.

Q3 — COUNT

For Legal in section 6, what is (COUNT(*), COUNT(e.emp_id), COUNT(e.bonus))?

• ア. (0, 0, 0)
• イ. (1, 1, 0)
• ウ. (1, 0, 0)
• エ. (1, 0, 1)

Answer: ウ (3). ア forgets the NULL-extended row. イ counts a NULL emp_id. ウ correctly counts one row but no non-NULL expressions. エ incorrectly counts the NULL bonus.

Q4 — NULL-bearing NOT IN

How many rows does the first query in section 9 return?

• ア. 0
• イ. 3
• ウ. 4
• エ. 5

Answer: ア (1). ア is correct because (20, NULL) makes every candidate FALSE or UNKNOWN. イ is the filtered-subquery result. ウ is the unguarded NOT EXISTS result. エ has no support in the data.

Q5 — duplicates and ordering

Which statement is correct under standard SQL?

• ア. DISTINCT always sorts ascending.
• イ. Without DISTINCT, the non-NULL department query returns 10,10,20,20,30 when ordered; without ORDER BY, row order is undefined.
• ウ. A primary key prevents duplicate projected dept_id values.
• エ. NULL always sorts last on every DBMS.

Answer: イ (2). ア confuses duplicate elimination with sorting. イ is correct. ウ confuses unique employee IDs with a nonunique projection. エ ignores DBMS differences.

Answer key (1-based): [2, 4, 3, 1, 2] = [イ, エ, ウ, ア, イ].

13. Self-check and review plan

Can you predict every result table without running SQL? Explain Legal’s 1,0,0; explain why NOT IN returns none; decide whether Dai belongs under each business intent; and write a deterministic NULL-last ordering.

• Day 1: redraw the fixed tables and hand-trace WHERE, both joins, and the two counts.
• Day 3: reproduce ON-vs-WHERE and all three exclusion queries; explain each row with TRUE/FALSE/UNKNOWN.
• Day 7: answer the five questions closed-book, then write one GROUP/HAVING query and its result table.

Source/integrity note: official scope only comes from the cited IPA syllabus. No past questions or third-party explanations were used.