3.7.2 - SQL retrieval from two related tables

Matching related records

A two-table query must say how records belong together, not merely list both tables.

This lesson uses exactly two tables. Member has MemberID (integer primary key) and MemberName (text). Loan has LoanID (integer primary key), MemberID (integer foreign key referring to Member.MemberID) and BookTitle (text).

Member

Loan

To find the name associated with loan 103, match Loan.MemberID, which is 11, to Member.MemberID. The matching row supplies Maya. Member 13 also has the name Maya but is not the linked borrower. Keys avoid this ambiguity.

Because both tables contain a field named MemberID, write the table name followed by a dot when referring to it in a join: Member.MemberID or Loan.MemberID. The matching condition is:

Member.MemberID = Loan.MemberID

Without that relationship, listing both tables can combine each member with each loan. Here that could produce nine combinations, most of which are not real borrower–loan pairs. The correct relationship produces three matched pairs; member 11 appears twice because that member has two loans, and member 13 appears zero times because that member has none. Repetition of a member in the result is not automatically an error.

Retrieving fields from two tables

SQL can combine the related records using a linking condition in WHERE or an INNER JOIN.

SELECT Member.MemberName, Loan.BookTitle
FROM Member, Loan
WHERE Member.MemberID = Loan.MemberID
ORDER BY Loan.LoanID ASC;

The equivalent explicit join is:

SELECT Member.MemberName, Loan.BookTitle
FROM Member INNER JOIN Loan
ON Member.MemberID = Loan.MemberID
ORDER BY Loan.LoanID ASC;