IB COMPUTER SCIENCE | A3.3.2 Queries Between Two Tables

Lesson objective

Construct two-table SQL queries using joins, comparisons, filtering, pattern matching, ordering, DISTINCT, GROUP BY and HAVING.

Learn

A3.3.2 | QUERIES BETWEEN TWO TABLES IN SQL

01 | ANSWERING A QUESTION THAT NEEDS TWO TABLES

The librarian asks: “Who borrowed Python Basics, and how long has the loan lasted?” The Loan table stores a StudentID, but the readable student name is in Student. Looking in either table alone leaves part of the answer missing.

A join combines related rows in a query result. It lets us use a student’s stored identifier to connect a loan with that student’s name. It does not permanently merge the tables or copy names into the stored Loan records.

We will build queries in small steps: identify the link, choose output columns, filter rows, then control the result’s order. After that, we will group matched rows and filter groups.

The command word construct means you must produce a working query from requirements, not just recognise command names. Each clause should have a clear job.

02 | MEET THE TWO TABLES

StudentID | PKStudentNameYearGroup
1Maya12
2Leon13
3Aisha12
4Noah13
5Mei12
LoanID | PKStudentID | FKBookTitleDaysLoaned
1011Python Basics12
1021Robot Design8
1032Python Projects20
1043Web Design12
1054Data Science15
1062SQL Basics5

One Student row represents one student. One Loan row represents one borrowing event. A student can have several loans, so Student.StudentID is unique while Loan.StudentID can repeat.

Loan.StudentID references Student.StudentID. For example, the 1 in loan 101 belongs with Maya’s Student row because her StudentID is 1. It does not mean “match whichever student happens to appear in row one”.

DaysLoaned is a supplied whole-number value in this teaching dataset. We are not calculating it from dates. Mei has no loans, which will help us distinguish matching rows from all students.

03 | START WITH THE JOIN CONDITION

FROM Student
JOIN Loan ON Student.StudentID = Loan.StudentID

FROM Student introduces one source. JOIN Loan brings in the second. ON supplies the matching rule: the StudentID values must be equal. A plain JOIN here means an INNER JOIN.

The dot means “this column from this table”. Both tables contain StudentID, so writing only StudentID in a reference can be ambiguous. Qualification tells the DBMS which column you mean.

Do not join Student.StudentID to Loan.LoanID just because both are identifiers. LoanID identifies a loan, while Loan.StudentID identifies its borrower. Match the relationship, not the words “ID” or the row positions.

A foreign key helps document and enforce the relationship, but you still write the join condition in this query. SQL does not automatically supply the intended join just because the tables have a foreign key.

04 | WATCH ONE LOAN FIND ITS STUDENT

Move through the six loans. Each loan’s borrower ID is compared with the student IDs, and the matching student supplies the name. StudentID 1 matches twice because Maya has two separate loans.

The display shows a conceptual match, not the DBMS’s physical algorithm. Real query optimisers can use different execution strategies.

05 | CHOOSE WHAT THE RESULT SHOULD SHOW

SELECT s.StudentName, l.BookTitle, l.DaysLoaned
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
ORDER BY l.LoanID;
StudentNameBookTitleDaysLoaned
MayaPython Basics12
MayaRobot Design8
LeonPython Projects20
AishaWeb Design12
NoahData Science15
LeonSQL Basics5

AS s gives Student the alias s; AS l gives Loan the alias l. An alias is a temporary query name, not a permanent table rename. Thus s.StudentName means StudentName from the table called s in this query.

SELECT lists the required output columns. The join may use IDs even when those IDs are not displayed. ORDER BY LoanID gives this example a specified order so you can compare its rows with the source.

The result has six rows, one for each matching loan. Repeated Maya and Leon values are expected. Mei is absent because an inner join only returns matched pairs; she has no Loan row.

06 | A JOIN IS NOT A PERMANENT TABLE MERGE

Think of each joined result row as a pairing: one Loan record plus the Student record whose key matches. One student can participate in several pairs. The query does not overwrite either source table.

If you combine every student with every loan without a matching condition, five students and six loans give 30 combinations. A cross join does this deliberately; omitting the intended relationship in other query forms can do it accidentally.

Our equality condition produces six matching pairs. Do not “fix” 30 incorrect combinations with DISTINCT. First correct the relationship. DISTINCT removes duplicate output combinations; it cannot determine which borrower belongs to a loan.

07 | FILTER ROWS WITH WHERE AND RELATIONAL OPERATORS

SELECT s.StudentName, l.BookTitle, l.DaysLoaned
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
WHERE l.DaysLoaned > 10
ORDER BY l.LoanID;
StudentNameBookTitleDaysLoaned
MayaPython Basics12
LeonPython Projects20
AishaWeb Design12
NoahData Science15

WHERE keeps joined rows whose condition is true. Here the join still identifies each borrower; the filter then keeps loans longer than ten days.

The operators compare values: = equal, <> not equal, < less than, > greater than, <= at most and >= at least. SQL equality uses one equals sign, not Python’s double equals.

A row can use a column in a filter without displaying that column. Write text literals in single quotes, such as s.StudentName = 'Maya'. Some systems support additional not-equal spellings, but <> is a useful standard form.

08 | BETWEEN INCLUDES BOTH ENDS

SELECT s.StudentName, l.BookTitle, l.DaysLoaned
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
WHERE l.DaysLoaned BETWEEN 8 AND 12
ORDER BY l.LoanID;
StudentNameBookTitleDaysLoaned
MayaPython Basics12
MayaRobot Design8
AishaWeb Design12

BETWEEN 8 AND 12 includes 8 and 12. For these non-null numeric values it is equivalent to DaysLoaned >= 8 AND DaysLoaned <= 12.

The AND inside BETWEEN separates the lower and upper bounds. It is part of the range expression, not a request to test whether the column equals both numbers.

Read the wording carefully. “Between 8 and 12 inclusive” uses BETWEEN; “greater than 8 and less than 12” excludes the endpoints and needs the strict comparisons.

09 | AND REQUIRES BOTH CONDITIONS

SELECT s.StudentName, l.BookTitle
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
WHERE s.YearGroup = 12 AND l.DaysLoaned >= 10
ORDER BY l.LoanID;

This query keeps loans only when the borrower is in Year 12 and the loan is at least ten days old. Maya’s Python Basics and Aisha’s Web Design match. Maya’s eight-day Robot Design loan fails the second condition.

Each side of AND needs a complete condition. Write s.YearGroup = 12 OR s.YearGroup = 13, not s.YearGroup = 12 OR 13. A standalone 13 does not express the intended second comparison.

10 | OR, NOT AND PARENTHESES

SELECT s.StudentName, l.BookTitle
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
WHERE s.YearGroup = 12
  AND (l.BookTitle LIKE 'Python%' OR l.BookTitle LIKE '%Design%')
ORDER BY l.LoanID;

The parentheses group two title alternatives: starts with Python or contains Design. The Year 12 rule must apply to both alternatives. The result is Maya’s Python Basics, Maya’s Robot Design and Aisha’s Web Design.

AND normally binds more tightly than OR. Without suitable parentheses, the Year 12 restriction might apply only to the first title alternative, allowing a Design loan from another year. Use grouping to make the requirement explicit.

SELECT s.StudentName, l.BookTitle
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
WHERE NOT (s.YearGroup = 13)
ORDER BY l.LoanID;

NOT reverses a condition’s truth value. With the non-null YearGroup values in this example, NOT (YearGroup = 13) keeps the Year 12 students’ loans. It does not introduce Mei because there is still no matching loan.

NULL requires care in other datasets: comparisons with NULL can be unknown, and NOT unknown is still unknown. WHERE keeps true rows. Use IS NULL or IS NOT NULL to test missing values, rather than = NULL.

11 | PATTERN MATCHING WITH LIKE AND %

SELECT s.StudentName, l.BookTitle
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
WHERE l.BookTitle LIKE 'Python%'
ORDER BY l.LoanID;
PatternMeaningExample match
'Python%'Starts with PythonPython Basics
'%Design'Ends with DesignRobot Design
'%Design%'Contains Design anywhereWeb Design
'%'Any sequence, including an empty stringAny non-null title in this example

The percent sign is a wildcard matching zero or more characters. LIKE 'Python%' can match Python itself as well as Python Basics. It does not mean “one character”.

Plain equality with = 'Python%' treats the percent sign as ordinary text. Use LIKE for the pattern operation. Keep the quotes around the whole pattern.

Whether uppercase and lowercase match depends on the DBMS and collation. Our fixed examples use the same letter case as the titles, so do not infer a universal case rule from them.

12 | ORDER RESULTS WITH ASC AND DESC

SELECT s.StudentName, l.BookTitle, l.DaysLoaned
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
ORDER BY l.DaysLoaned DESC, s.StudentName ASC, l.LoanID ASC;
StudentNameBookTitleDaysLoaned
LeonPython Projects20
NoahData Science15
AishaWeb Design12
MayaPython Basics12
MayaRobot Design8
LeonSQL Basics5

DESC places larger day counts first. For a tie, StudentName ASC places names in ascending order under the system’s text ordering. LoanID is a final tie-breaker so rows with equal earlier sort values still have a deterministic order.

Each sort column has its own direction. ORDER BY l.DaysLoaned DESC, s.StudentName ASC means days descending, then name ascending, not both descending. ASC is normally the default.

Without ORDER BY, SQL does not promise insertion order, primary-key order or alphabetical order. Sorting the result also does not rearrange the table’s stored facts or change the loan values.

13 | DISTINCT REMOVES DUPLICATE OUTPUT COMBINATIONS

SELECT DISTINCT s.StudentID, s.StudentName
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
ORDER BY s.StudentID;
StudentIDStudentName
1Maya
2Leon
3Aisha
4Noah

The inner join includes Maya twice and Leon twice because both have two loans. SELECT DISTINCT StudentID, StudentName returns each different ID-name combination once.

DISTINCT applies to the complete selected combination. If BookTitle is also selected, Maya’s two different titles remain different result rows. DISTINCT does not mean “remove all repeated names anywhere”.

Include StudentID when the request means distinct students. Different people can share a name, so selecting only DISTINCT StudentName could collapse different students. Mei still does not appear: DISTINCT removes repetition, it does not create missing matches.

14 | BUILD AND TEST A FILTERED QUERY

Adjust the controls to build a query from the two tables. The displayed SQL and result update together. Try Year 12, a minimum of 8, a maximum of 12 and “contains Design”, then predict the rows before checking.

This fixed-data activity models the available options in JavaScript; it is not a general SQL editor. Pattern choices use exact case, and the activity does not model arbitrary SQL, collation or NULL behaviour.

15 | GROUP BY COLLECTS MATCHING ROWS INTO GROUPS

SELECT s.StudentID, s.StudentName, COUNT(l.LoanID) AS LoanCount
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
GROUP BY s.StudentID, s.StudentName
ORDER BY s.StudentID;
StudentIDStudentNameLoanCount
1Maya2
2Leon2
3Aisha1
4Noah1

Now the librarian wants one summary per borrower, rather than one row per loan. GROUP BY gathers the joined rows with the same StudentID and StudentName. COUNT(l.LoanID) counts their non-null LoanID values.

Maya’s two loan rows become one group with a count of 2. Leon also has 2; Aisha and Noah have 1 each. Mei has no inner-join rows, so no group is formed for her.

Every displayed non-aggregated column is included in GROUP BY in this query. You cannot simply add BookTitle and expect a meaningful single title for Maya’s two-loan group. Grouping by IDs as well as names also keeps different students who share a name separate.

AS LoanCount names the calculated result column. It does not add a stored LoanCount column to Student. Aggregate calculations receive fuller treatment in A3.3.4; the grouping structure belongs in this lesson too.

16 | WHERE FILTERS ROWS; HAVING FILTERS GROUPS

SELECT s.StudentID, s.StudentName, COUNT(l.LoanID) AS LoanCount
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
WHERE l.DaysLoaned >= 7
GROUP BY s.StudentID, s.StudentName
HAVING COUNT(l.LoanID) >= 2
ORDER BY LoanCount DESC, s.StudentID ASC;
StudentIDStudentNameLoanCount
1Maya2

Read this in stages. First match borrowers and loans. WHERE keeps loans at least seven days old, excluding Leon’s five-day SQL Basics loan. Then GROUP BY collects the remaining rows per student.

HAVING keeps only groups with a count of at least 2. Maya qualifies with two loans, while Leon now has only one qualifying loan. The count is over the rows that survived WHERE, not every loan the student ever had.

Do not put COUNT(l.LoanID) >= 2 in WHERE at the same query level: the group count does not yet exist at that logical stage. Use HAVING for this aggregate group condition.

ORDER BY then sorts the retained summaries. The written clause order is SELECT, FROM/JOIN/ON, WHERE, GROUP BY, HAVING, ORDER BY.

17 | WRITTEN ORDER AND LOGICAL REASONING

Written clauseRole when reasoning about this query
SELECTSpecify output columns or expressions.
FROM ... JOIN ... ONIdentify sources and matching rows.
WHEREKeep individual rows satisfying the condition.
GROUP BYForm groups from the surviving rows.
HAVINGKeep qualifying groups.
ORDER BYSpecify the returned order.

Write SELECT first, but reason about where the rows come from before deciding the final output. A useful conceptual sequence is sources/join → row filter → grouping → group filter → output/DISTINCT → ordering.

This describes the query’s meaning, not a promise of the DBMS’s physical execution order. An optimiser can rearrange operations where that preserves the result. Likewise, do not assume a SELECT alias is available in WHERE; use the underlying column or expression there.

18 | WHEN YOU NEED STUDENTS WITH NO LOANS

SELECT s.StudentID, s.StudentName, l.LoanID
FROM Student AS s
LEFT JOIN Loan AS l ON s.StudentID = l.StudentID
ORDER BY s.StudentID, l.LoanID;

A LEFT JOIN retains every row from the left source, Student, and supplies matching Loan rows where they exist. For Mei, the loan-side columns are NULL because no loan matched. The result has seven rows: six matched loans and one unmatched student row.

This is a useful extension when a request says “all students, including those with no loans”. It differs from the inner join used in the core examples.

When counting loans after a left join, COUNT(l.LoanID) counts zero for Mei because it ignores NULL. COUNT(*) counts the preserved unmatched row as one, so it would not express her number of loans.

Be careful adding a WHERE condition on loan-side columns: a condition such as DaysLoaned > 10 rejects Mei’s NULL row and can remove the unmatched students you intended to retain.

19 | CONSTRUCT A COMPLETE QUERY FROM A REQUEST

Request: Show the names, book titles and days loaned for Year 12 students whose loans are between 8 and 12 days inclusive and whose titles start with Python or contain Design. Show longest loans first, with names ascending for ties.

Start by locating the requested fields and the relationship. Student supplies name and year; Loan supplies title and days. Join using the borrower StudentID. Translate each restriction into a complete condition, group the title alternatives, then choose output and sorting.

SELECT s.StudentName, l.BookTitle, l.DaysLoaned
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.StudentID
WHERE s.YearGroup = 12
  AND l.DaysLoaned BETWEEN 8 AND 12
  AND (l.BookTitle LIKE 'Python%' OR l.BookTitle LIKE '%Design%')
ORDER BY l.DaysLoaned DESC, s.StudentName ASC, l.LoanID ASC;
StudentNameBookTitleDaysLoaned
AishaWeb Design12
MayaPython Basics12
MayaRobot Design8

All three conditions must hold: Year 12, the inclusive day range, and either title pattern. LoanID is an additional stable tie-breaker; it does not change the requested ordering between unequal day/name values.

20 | REPAIR THE QUERY, NOT JUST THE OUTPUT

SELECT s.StudentName, l.BookTitle
FROM Student AS s
JOIN Loan AS l ON s.StudentID = l.LoanID
WHERE s.YearGroup = 12 OR 13
ORDER BY l.DaysLoaned DESC;

The first problem is the join: StudentID must match the borrower StudentID, not the loan identifier. The second is the incomplete alternative: use YearGroup = 12 OR YearGroup = 13.

A query can run yet express the wrong requirement, especially with incorrect Boolean grouping or a missing filter. Checking only syntax is insufficient. Predict representative rows and exclusions, then compare the result.

Do not add DISTINCT to hide a mistaken join or rely on the first rows appearing in a convenient order. Correct the relationship and make the requested ordering explicit.

21 | CHECK YOUR CLAUSE CHOICES

Choose the clause or mechanism that meets each requirement. Your first checked response counts toward the score; restart for another attempt.

22 | CHECK BEFORE YOU FINISH

Read the question again and check the requested output columns, the two source tables and the correct relationship. Then inspect inclusive/exclusive boundaries, full Boolean conditions, pattern placement and sort directions.

For a grouped query, ask what each group represents and which rows enter it. Use WHERE for the row restriction and HAVING for a condition on its aggregate. Keep non-aggregated output columns appropriately grouped.

Finally, test a repeated borrower, a boundary day value, a title matching one alternative and a student with no loans. These cases expose mistakes that a single easy record can hide. SQL syntax and collation vary across DBMSs, so verify dialect details in the environment being used.

Further reading: PostgreSQL joins tutorial.

MATCH THE SQL CLAUSE TO ITS PURPOSE

Terminology

Terminology

Query

A request for a result from database data.

SELECT

Specifies output expressions or columns.

FROM

Introduces the query’s data sources.

JOIN

Combines rows from sources according to join semantics.

ON

Specifies the matching condition for an explicit join.

INNER JOIN

Returns matching row pairs, excluding unmatched source rows.

LEFT JOIN

Retains every left-side row, supplying NULL right-side values when no match exists.

Primary key

The selected unique non-null identifier of a table’s rows.

Foreign key

A declared reference to an appropriate key in a related table.

Qualified column

A column reference identifying its table or alias, such as s.StudentID.

Table alias

A temporary query name for a table reference.

Result set

The returned rows and columns, distinct from permanent table changes.

WHERE

Filters individual rows according to a condition.

Relational operator

A comparison such as =, <>, <, >, <= or >=.

BETWEEN

Tests an inclusive lower-to-upper range.

AND

Requires both conditions to be true for a true result.

OR

Requires at least one condition to be true for a true result.

NOT

Negates a truth value; unknown remains unknown.

LIKE

Tests a value against a text pattern under the DBMS’s matching rules.

% wildcard

Matches zero or more characters in a LIKE pattern.

ORDER BY

Specifies the ordering of returned rows.

ASC

Ascending sort direction, normally the default.

DESC

Descending sort direction.

Tie-breaker

A further sort expression ordering rows equal on earlier sort expressions.

DISTINCT

Removes duplicate combinations of selected output values.

GROUP BY

Forms groups of rows with the same grouping values.

Aggregate function

Calculates a summary over a set of rows, such as COUNT.

HAVING

Filters groups, often using an aggregate condition.

NULL

An absent or unknown value; ordinary comparisons can yield unknown.

Cross join

Produces every combination of rows from the two sources.

Questions

Questions

CHECK YOUR UNDERSTANDING

Select all correct choices. Each exact set earns one point.

1. Which describe the correct relationship?
2. Which describe the core inner join result?
3. Which days match BETWEEN 8 AND 12?
4. Which Boolean forms express Year 12 or Year 13?
5. Which are true about LIKE?
6. Which describe ordering?
7. Which describe DISTINCT?
8. Which describe grouping?
9. Which are true of the seven-day HAVING example?
10. Which describe the LEFT JOIN extension?

EXPLAIN AND COMPARE

Write your answers before revealing each sample.

1. Construct the join that lists each borrower name and book title.

2. Explain why Maya appears twice but Mei is absent in the core inner join.

3. Construct a query for loans over ten days, showing name and title.

4. Construct a query for Year 12 loans between eight and twelve days inclusive.

5. Construct a query for titles beginning Python, longest loan first.

6. Construct the condition for Year 12 and either a Python prefix or Design anywhere.

7. Construct a query returning distinct borrowers, preserving students who share a name.

8. Construct a loan-count summary per borrower.

9. Construct a query for borrowers with at least two loans lasting at least seven days.

10. Explain WHERE versus HAVING using the loan example.

11. Construct a query listing all students, including those without loans.

12. Explain why DISTINCT cannot repair joining StudentID to LoanID.

CONSTRUCT IT | BORROWER REPORTS

Using Student and Loan, construct two reports. First show Year 12 borrowers with loans between eight and twelve days inclusive whose titles start with Python or contain Design; order by days descending then name ascending. Second show student IDs, names and counts for borrowers with at least two loans lasting at least seven days. Check expected rows and explain the roles of ON, WHERE and HAVING.

Flashcards

Flashcards

Click to flip. Select the ideas you need to revisit.

0 cards selected for revision.

    Selections are kept while this page is open.

    Workbook

    Workbook

    COMING SOON

    The workbook for A3.3.2 Queries Between Two Tables is coming soon. Complete the matching task and written questions in your exercise book.