Querying Databases: WHERE and JOIN in SQL

Imagine a school library keeps two lists. The first list has every student's name and their student ID number. The second list has every borrowed book and the student ID of the person who borrowed it. Could you figure out which student has which book? Of course — you match the ID numbers. That matching idea is exactly what SQL does for you automatically. Before you dive in, think about this: if you had a list of 10,000 students, how would you quickly find just the ones in Year 9? You need a way to filter. SQL gives you two powerful tools for exactly these tasks: WHERE and JOIN.

SQL (Structured Query Language) is the language used to talk to a relational database — a collection of data stored in organised tables made of rows and columns. **The WHERE Clause — Filtering Rows** Every time you run a SELECT query, the database returns rows. Without a filter, you get every single row. The WHERE clause lets you set a condition, so only rows that match that condition are returned. The basic shape looks like this: SELECT column1, column2 FROM table_name WHERE condition; For example, imagine a table called Students with columns StudentID, Name, and Year. To find only Year 9 students you would write: SELECT Name FROM Students WHERE Year = 9; The database reads every row, checks whether Year equals 9, and returns only the matching rows. You can use other comparison operators too: > (greater than), < (less than), <> (not equal to), and LIKE for matching text patterns. **The JOIN Clause — Combining Two Tables** A JOIN brings together rows from two different tables by matching values in a shared column. That shared column is called a key — specifically, a foreign key in one table matches a primary key in the other. The most common type is the INNER JOIN. It returns only the rows where there is a match in both tables. The shape looks like this: SELECT table1.column, table2.column FROM table1 INNER JOIN table2 ON table1.shared_column = table2.shared_column; Using the library example, to find each student's name alongside the book they borrowed: SELECT Students.Name, BorrowedBooks.BookTitle FROM Students INNER JOIN BorrowedBooks ON Students.StudentID = BorrowedBooks.StudentID; The ON keyword tells SQL which columns to match. Rows where StudentID appears in both tables are combined into a single result row. If a student has no borrowed book, they do not appear in the result — that is what makes it an INNER JOIN (only matched pairs come through). You can combine WHERE and JOIN in one query. For example, to find only Year 9 students and their borrowed books: SELECT Students.Name, BorrowedBooks.BookTitle FROM Students INNER JOIN BorrowedBooks ON Students.StudentID = BorrowedBooks.StudentID WHERE Students.Year = 9;