Topic 5: Joint Query, Nested Query, Filtering DATA

📖 15 min read · 🎯 intermediate · 🧭 Prerequisites: arithmetic-operations-with-while-and-if-conditions-prime-odd-even, javascript-dom

Why this matters

Up until now, you've been querying one table at a time — great for simple lookups, but real apps don't work that way. Think about a student portal: student names live in one table, course enrollments in another, grades in a third. If those tables can't talk to each other, you can't answer a single useful question like "which students passed this course?" That's exactly what JOINs, nested queries, and WHERE filters solve — they let you pull connected data from separate tables and shape the answer you actually need.

What You'll Learn

How to use INNER JOIN to combine rows from two related tables based on a shared key
How to write nested queries (subqueries) using the IN keyword for complex lookups
How to filter result sets with the WHERE clause and comparison operators
How to execute all three query types through PHP's MySQLi extension and display the results

The Analogy

Think of a university registrar's filing room. Student records live in one cabinet and course enrollment slips live in another. On their own, neither cabinet tells the full story — you need a clerk who pulls both drawers, matches the student ID on the enrollment slip to the student ID on the record card, and hands you a combined sheet. That clerk is your JOIN. The nested query is like asking, "First find every enrollment slip that says Math, then go to the student cabinet and pull only those students." The WHERE clause is the sticky note on the drawer that says "only pull cards for students older than 20." Together, these three tools let you ask the filing room any question imaginable.

A join combines rows from two or more tables based on a related column. The three most common types are INNER JOIN, LEFT JOIN, and RIGHT JOIN. This lesson focuses on INNER JOIN, which returns only the rows that have matching values in both tables.

Setting up the schema

Before writing join queries, create the two tables and seed them with data:

CREATE DATABASE school;

USE school;

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL
);

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_name VARCHAR(50),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO students (name, age) VALUES ('Alice', 23), ('Bob', 25), ('Carol', 22);

INSERT INTO courses (student_id, course_name) VALUES (1, 'Math'), (2, 'Science'), (1, 'History');

The courses.student_id column is the foreign key — it's the shared column that lets the two tables be joined.

INNER JOIN syntax

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

ON students.id = courses.student_id is the join condition. Only rows where the two IDs match are included in the output. Carol has no course enrollment, so she does not appear.

INNER JOIN in PHP

<?php

$servername = "localhost";
$username   = "root";
$password   = "";
$dbname     = "school";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// SQL query with INNER JOIN
$sql = "SELECT students.name, courses.course_name
        FROM students
        INNER JOIN courses ON students.id = courses.student_id";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Student: " . $row["name"] . " – Course: " . $row["course_name"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Key MySQLi methods used here:

new mysqli(host, user, pass, db) — opens the connection
$conn->query($sql) — executes the SQL and returns a result object
$result->num_rows — count of returned rows
$result->fetch_assoc() — pulls one row as an associative array, keyed by column name
$conn->close() — releases the connection

Chapter 2: Nested Queries — Queries Within Queries

A nested query (also called a subquery) is a SELECT statement embedded inside another SELECT statement. The inner query runs first; its result is fed into the outer query.

When to use a subquery

Use a subquery when you need to first compute a set of values, then use that set as a filter condition. The IN keyword is the most common bridge between the two queries.

SQL subquery example

Find the names of all students who are enrolled in Math:

SELECT name FROM students
WHERE id IN (
    SELECT student_id FROM courses WHERE course_name = 'Math'
);

Execution order:

Inner query runs: SELECT student_id FROM courses WHERE course_name = 'Math' → returns [1]
Outer query runs: SELECT name FROM students WHERE id IN (1) → returns Alice

Subquery in PHP

<?php

// SQL query with nested query
$sql = "SELECT name FROM students
        WHERE id IN (
            SELECT student_id FROM courses WHERE course_name = 'Math'
        )";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Student: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

The PHP side is identical in structure — only the SQL string changes. MySQLi handles the two-pass execution internally; your PHP code never needs to know the subquery exists.

Chapter 3: Filtering Data — The WHERE Clause

The WHERE clause narrows a query's result set to rows that satisfy a condition. It works on any SELECT, UPDATE, or DELETE statement.

Common filtering operators

Operator	Meaning
`=`	Equals
`!=` or `<>`	Not equals
`>`	Greater than
`<`	Less than
`>=`	Greater than or equal
`<=`	Less than or equal
`LIKE`	Pattern match (`%` wildcard)
`IN (...)`	Value is in a list
`BETWEEN a AND b`	Value in inclusive range

Filtering example

Retrieve all students older than 20:

SELECT * FROM students WHERE age > 20;

Filtering in PHP

<?php

// SQL query with WHERE clause
$sql = "SELECT * FROM students WHERE age > 20";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " – Name: " . $row["name"] . " – Age: " . $row["age"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Because all three students (Alice 23, Bob 25, Carol 22) have age > 20, all three rows are returned.

Chapter 4: Putting It All Together

Here is a single, complete PHP script that opens one database connection and demonstrates all three techniques in sequence — INNER JOIN, nested query, and WHERE filtering.

Complete PHP script

<?php

$servername = "localhost";
$username   = "root";
$password   = "";
$dbname     = "school";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// ── Inner Join Query ────────────────────────────────────────────
$sql = "SELECT students.name, courses.course_name
        FROM students
        INNER JOIN courses ON students.id = courses.student_id";

$result = $conn->query($sql);

echo "<h2>Inner Join Results:</h2>";

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Student: " . $row["name"] . " – Course: " . $row["course_name"] . "<br>";
    }
} else {
    echo "0 results";
}

// ── Nested Query ────────────────────────────────────────────────
$sql = "SELECT name FROM students
        WHERE id IN (
            SELECT student_id FROM courses WHERE course_name = 'Math'
        )";

$result = $conn->query($sql);

echo "<h2>Nested Query Results:</h2>";

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Student: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}

// ── Filtering Data ──────────────────────────────────────────────
$sql = "SELECT * FROM students WHERE age > 20";

$result = $conn->query($sql);

echo "<h2>Filtering Data Results:</h2>";

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " – Name: " . $row["name"] . " – Age: " . $row["age"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Expected output

Inner Join Results:
Student: Alice – Course: Math
Student: Bob – Course: Science
Student: Alice – Course: History

Nested Query Results:
Student: Alice

Filtering Data Results:
ID: 1 – Name: Alice – Age: 23
ID: 2 – Name: Bob – Age: 25
ID: 3 – Name: Carol – Age: 22

The data flow looks like this:

flowchart TD
    A[students table] -->|ON students.id = courses.student_id| B[INNER JOIN]
    C[courses table] --> B
    B --> D[Joined result set]

    C -->|course_name = 'Math'| E[Subquery: matching student_ids]
    E -->|id IN subquery result| F[Nested query result set]

    A -->|age > 20| G[Filtered result set]

🧪 Try It Yourself

Task: Extend the school database with a grades table and write a query that retrieves every student's name alongside their grade for any course they completed with a score above 70.

Add the table and some rows:

CREATE TABLE grades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    course_name VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO grades (student_id, course_name, score)
VALUES (1, 'Math', 88), (2, 'Science', 65), (1, 'History', 72), (3, 'Math', 91);

Write and run this PHP script:

<?php
$conn = new mysqli("localhost", "root", "", "school");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

$sql = "SELECT students.name, grades.course_name, grades.score
        FROM students
        INNER JOIN grades ON students.id = grades.student_id
        WHERE grades.score > 70";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo $row["name"] . " – " . $row["course_name"] . " – Score: " . $row["score"] . "<br>";
    }
} else {
    echo "No results";
}

$conn->close();
?>

Success criterion: You should see three rows in the browser: Alice/Math/88, Alice/History/72, and Carol/Math/91. Bob's Science score of 65 should be absent because 65 > 70 is false.

🔍 Checkpoint Quiz

Q1. What is the key difference between an INNER JOIN and a LEFT JOIN?

A) INNER JOIN returns all rows from the left table; LEFT JOIN returns only matches
B) INNER JOIN returns only matching rows from both tables; LEFT JOIN returns all rows from the left table plus matches from the right
C) They are identical; the keyword is just an alias
D) LEFT JOIN requires a foreign key; INNER JOIN does not

Q2. Given the following SQL, what will the subquery return before the outer query runs?

SELECT name FROM students
WHERE id IN (
    SELECT student_id FROM courses WHERE course_name = 'Math'
);

A) A list of student names enrolled in Math
B) A list of student_id values from the courses table where course_name = 'Math'
C) The full courses table
D) An error, because nested queries are not valid in MySQL

Q3. Read this PHP snippet. What does $result->num_rows guard against?

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}

A) It prevents the script from running if the database connection fails
B) It avoids entering the while loop when the query returned no matching rows
C) It limits the number of rows fetched to the value stored in num_rows
D) It checks whether the SQL syntax is valid

Q4. You have a products table with columns id, name, and price. Write a single SQL query that retrieves the name and price of all products priced between 10 and 50 (inclusive), ordered by price from lowest to highest.

A1. B — INNER JOIN only returns rows where a match exists in both tables. LEFT JOIN returns every row from the left table and fills in NULL for columns from the right table when no match exists.

A2. B — The inner query executes first and returns a set of student_id integers (in this case [1]). The outer query then uses that set in its IN condition to look up student names.

A3. B — If the query matched zero rows, $result->num_rows equals 0, which is not > 0, so the while loop is skipped entirely and "0 results" is echoed instead of entering an empty loop.

A4.

SELECT name, price
FROM products
WHERE price BETWEEN 10 AND 50
ORDER BY price ASC;

BETWEEN 10 AND 50 is inclusive on both ends, equivalent to price >= 10 AND price <= 50. ORDER BY price ASC sorts from lowest to highest.

🪞 Recap

INNER JOIN links two tables on a shared key column and returns only the rows where a match exists in both tables.
A nested query (subquery) runs first and feeds its output — typically a list of IDs — into the outer query's IN clause for complex lookups without multiple round trips.
The WHERE clause filters rows using comparison operators (>, <, =, BETWEEN, LIKE, IN) before the result set is returned.
PHP's MySQLi extension executes all three query types identically: $conn->query($sql) → check $result->num_rows → loop with $result->fetch_assoc().
One database connection can service multiple queries sequentially; always call $conn->close() at the end of the script.

📚 Further Reading

MySQL JOIN documentation — the source of truth on all join types including CROSS and SELF joins
MySQL Subquery syntax — full reference for correlated and non-correlated subqueries
PHP MySQLi documentation — complete API reference for every method used in this lesson
SQL WHERE clause operators (W3Schools) — quick visual reference for every comparison operator
⬅️ Previous: PHP with Update & Delete
➡️ Next: Table-Forms, Page-Layout Introduction