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 | PK | StudentName | YearGroup |
|---|---|---|
| 1 | Maya | 12 |
| 2 | Leon | 13 |
| 3 | Aisha | 12 |
| 4 | Noah | 13 |
| 5 | Mei | 12 |
| LoanID | PK | StudentID | FK | BookTitle | DaysLoaned |
|---|---|---|---|
| 101 | 1 | Python Basics | 12 |
| 102 | 1 | Robot Design | 8 |
| 103 | 2 | Python Projects | 20 |
| 104 | 3 | Web Design | 12 |
| 105 | 4 | Data Science | 15 |
| 106 | 2 | SQL Basics | 5 |
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;
| StudentName | BookTitle | DaysLoaned |
|---|---|---|
| Maya | Python Basics | 12 |
| Maya | Robot Design | 8 |
| Leon | Python Projects | 20 |
| Aisha | Web Design | 12 |
| Noah | Data Science | 15 |
| Leon | SQL Basics | 5 |
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;
| StudentName | BookTitle | DaysLoaned |
|---|---|---|
| Maya | Python Basics | 12 |
| Leon | Python Projects | 20 |
| Aisha | Web Design | 12 |
| Noah | Data Science | 15 |
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;
| StudentName | BookTitle | DaysLoaned |
|---|---|---|
| Maya | Python Basics | 12 |
| Maya | Robot Design | 8 |
| Aisha | Web Design | 12 |
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;
| Pattern | Meaning | Example match |
|---|---|---|
| 'Python%' | Starts with Python | Python Basics |
| '%Design' | Ends with Design | Robot Design |
| '%Design%' | Contains Design anywhere | Web Design |
| '%' | Any sequence, including an empty string | Any 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;
| StudentName | BookTitle | DaysLoaned |
|---|---|---|
| Leon | Python Projects | 20 |
| Noah | Data Science | 15 |
| Aisha | Web Design | 12 |
| Maya | Python Basics | 12 |
| Maya | Robot Design | 8 |
| Leon | SQL Basics | 5 |
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;
| StudentID | StudentName |
|---|---|
| 1 | Maya |
| 2 | Leon |
| 3 | Aisha |
| 4 | Noah |
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;
| StudentID | StudentName | LoanCount |
|---|---|---|
| 1 | Maya | 2 |
| 2 | Leon | 2 |
| 3 | Aisha | 1 |
| 4 | Noah | 1 |
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;
| StudentID | StudentName | LoanCount |
|---|---|---|
| 1 | Maya | 2 |
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 clause | Role when reasoning about this query |
|---|---|
| SELECT | Specify output columns or expressions. |
| FROM ... JOIN ... ON | Identify sources and matching rows. |
| WHERE | Keep individual rows satisfying the condition. |
| GROUP BY | Form groups from the surviving rows. |
| HAVING | Keep qualifying groups. |
| ORDER BY | Specify 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;
| StudentName | BookTitle | DaysLoaned |
|---|---|---|
| Aisha | Web Design | 12 |
| Maya | Python Basics | 12 |
| Maya | Robot Design | 8 |
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.