4.10.4 - SQL retrieval across related tables (Java)

SELECT, filtering and ordering

A query states which information is wanted; the database management system decides how to retrieve it.

Start by identifying the fields to return, the table holding them and the condition that a qualifying row must satisfy. SELECT lists output fields; FROM names the source; WHERE filters rows; ORDER BY sorts the result. SQL keywords are shown in capitals for readability, rather than because capitals are essential. Use single quotes for text literals and no quotes for numeric literals. A semicolon ends a statement.

An original workshop database includes Workshop(WorkshopID, WorkshopTitle, Fee):

SELECT WorkshopTitle, Fee
FROM Workshop
WHERE Fee >= 8
ORDER BY Fee DESC, WorkshopTitle ASC;

The result is Robotics, 12.00 followed by Databases, 8.00. The comparison includes the boundary value 8. DESC sorts descending; ASC sorts ascending and is the default. A second sort field resolves equal first-field values. Without ORDER BY, the database does not promise a particular row order.

AND requires both conditions; OR requires at least one. Parentheses make the intended grouping explicit: (YearGroup = 12 OR YearGroup = 13) AND MemberName LIKE 'A%'. The % wildcard in LIKE means any sequence of characters, including an empty sequence. BETWEEN 8 AND 12 includes both endpoints. DISTINCT removes duplicate output rows, rather than deleting stored records. SELECT * returns all fields, but a precise request normally names only the needed fields.

NULL represents a missing or unknown value, not zero or an empty string. Use IS NULL or IS NOT NULL when checking for it; = NULL does not test missingness. Comparisons with an unknown value do not pass a WHERE filter.

Joining tables

A join combines rows using their relationship, so a foreign key can lead from an activity record to the member and workshop it describes.

The remaining relations are Member(MemberID, MemberName, YearGroup) and Booking(BookingID, MemberID, WorkshopID, Status). MemberID and WorkshopID in Booking reference the corresponding primary keys. BookingID identifies a booking; it is not the key to match against MemberID.

SELECT m.MemberName, w.WorkshopTitle
FROM Member AS m
INNER JOIN Booking AS b ON m.MemberID = b.MemberID
INNER JOIN Workshop AS w ON b.WorkshopID = w.WorkshopID
WHERE b.Status = 'Confirmed'
ORDER BY m.MemberName, w.WorkshopTitle;

The four result rows are Asha–Databases, Asha–Robotics, Ben–Robotics and Chen–Databases. An inner join keeps only matching rows. Dara has no booking, so does not appear. Asha appears twice because two bookings meet the request. That is a meaningful one-to-many result, not necessarily an error. If the request were only for distinct member names, SELECT DISTINCT m.MemberName would return each qualifying name once.

Aliases such as m shorten names. Qualification such as m.MemberID also removes ambiguity when several tables contain a field with the same name. A comma-separated FROM Member m, Booking b, Workshop w with both key equalities in WHERE can express the same inner join. Omitting either equality combines unrelated rows and can multiply the results; a plausible-looking name is not evidence of a correct relationship.

A left join retains every row from its left table and supplies NULLs when the right side has no match. For example, Member left-joined to Booking can include Dara. Putting b.Status = 'Confirmed' in a later WHERE would remove the unmatched row because its status is NULL. Use the join type and condition placement that match the request; do not add a left join merely because there are two tables.