Topic 5: Joint Query, Nested Query, Filtering DATA

📖 7 min read · 🎯 intermediate · 🧭 Prerequisites: working-with-asynchronous-programming, building-a-http-server-with-node-js-using-http-apis

Why this matters

Up until now, every query you've written pulls from a single table — one neat box of data. But real databases don't work that way. Student names live in one table, course details in another, enrollment records in a third. The moment you try to answer something like "which students enrolled in the Python course and are under 25?" — you're stuck, because that answer is spread across multiple tables.

That's exactly what this lesson solves. We'll use JOINs to connect tables, nested queries to filter with precision, and WHERE to cut results down to only what matters. By the end, you'll query across your entire database like it's one unified source of truth.

What You'll Learn

How to combine rows from multiple tables using INNER JOIN, LEFT JOIN, and RIGHT JOIN
How to write nested queries (subqueries) that feed results into an outer query
How to filter result sets precisely using the WHERE clause
How to wire all three techniques together inside a PHP script backed by MySQLi

The Analogy

Think of your database as a university registrar's office with separate binders — one for student records, one for course enrollments. A JOIN is the clerk who physically opens both binders and lines up matching student IDs side by side so you can read across them. A nested query is like asking that clerk, "First, tell me every student ID enrolled in Math — then go back to the student binder and pull only those names." Filtering with WHERE is the sticky-note rule you leave on the desk: "Only hand me pages where the student's age is above 20." Each technique narrows and connects raw records into exactly the answer you need, without photocopying the entire archive.

A join combines rows from two or more tables based on a shared column value. The three most common types are:

Join Type	What it returns
`INNER JOIN`	Only rows with a matching value in both tables
`LEFT JOIN`	All rows from the left table, matched rows from the right (NULLs where no match)
`RIGHT JOIN`	All rows from the right table, matched rows from the left (NULLs where no match)

Setting up the schema

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');

INNER JOIN

Returns every row where students.id matches courses.student_id — students with no courses are excluded.

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

Expected result:

name	course_name
Alice	Math
Bob	Science
Alice	History

INNER JOIN in PHP

<?php
$servername = "localhost";
$username   = "root";
$password   = "";
$dbname     = "school";

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

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

$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();
?>

erDiagram
    students {
        int id PK
        varchar name
        int age
    }
    courses {
        int id PK
        int student_id FK
        varchar course_name
    }
    students ||--o{ courses : "has"

Chapter 2: Nested Queries — Queries Within Queries

A nested query (also called a subquery) is a SELECT statement placed inside another SQL statement. The inner query runs first, and its result set is handed to the outer query.

Use case: find students enrolled in Math

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

The database engine:

Executes SELECT student_id FROM courses WHERE course_name = 'Math' → returns [1]
Executes SELECT name FROM students WHERE id IN (1) → returns ['Alice']

Nested query in PHP

<?php
$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();
?>

Subqueries work anywhere a value or list is expected: inside WHERE, FROM, SELECT, and HAVING clauses. When performance matters on large datasets, consider rewriting correlated subqueries as JOINs — but for clarity and moderate data volumes, subqueries are perfectly readable.

Chapter 3: Filtering Data — The WHERE Clause

The WHERE clause restricts which rows appear in the result set. It evaluates a condition for every row and includes only the rows where the condition is TRUE.

Retrieve students older than 20

SELECT * FROM students WHERE age > 20;

Common WHERE operators:

Operator	Meaning
`=`	Equal
`<>` or `!=`	Not equal
`>` / `<`	Greater / less than
`>=` / `<=`	Greater-or-equal / less-or-equal
`BETWEEN x AND y`	Inclusive range
`IN (...)`	Matches any value in list
`LIKE 'pattern'`	Pattern match (`%` wildcard)
`IS NULL`	Column has no value

WHERE clause in PHP

<?php
$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();
?>

Chapter 4: Putting It All Together

The following complete PHP script creates the database, seeds data, and runs all three query techniques in sequence.

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');

<?php
$servername = "localhost";
$username   = "root";
$password   = "";
$dbname     = "school";

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

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 browser 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

🧪 Try It Yourself

Task: Extend the school database with a grades table and write a query that uses a JOIN plus a WHERE filter to list every student whose Math grade is above 70.

Success criterion: Running the script in the browser should print only the names and grades of qualifying students — no students without Math grades, no grades at or below 70.

Starter snippet:

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

INSERT INTO grades (student_id, course_name, grade)
VALUES (1, 'Math', 85), (2, 'Math', 65), (3, 'Math', 90);

<?php
$conn = new mysqli("localhost", "root", "", "school");

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

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

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo $row["name"] . " scored " . $row["grade"] . " in Math<br>";
    }
}
$conn->close();
?>

You should see Alice (85) and Carol (90) — Bob's 65 is filtered out.

🔍 Checkpoint Quiz

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

A) INNER JOIN returns all rows from both tables; LEFT JOIN returns only matched rows
B) INNER JOIN returns only matched rows from both tables; LEFT JOIN returns all rows from the left table plus matched rows from the right
C) They are identical in behavior
D) LEFT JOIN only works on tables with foreign keys

Q2. Given this snippet, what will be printed if no student is enrolled in 'Physics'?

$sql = "SELECT name FROM students
        WHERE id IN (
            SELECT student_id FROM courses WHERE course_name = 'Physics'
        )";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "Student: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}

A) A PHP fatal error
B) An empty white page
C) 0 results
D) Student:

Q3. The courses table has a FOREIGN KEY (student_id) REFERENCES students(id). What does this constraint enforce?

A) Every student_id in courses must match an existing id in students
B) The course_name column cannot be NULL
C) A student can only be enrolled in one course
D) The courses table is automatically indexed on course_name

Q4. You need to list all students whose names start with the letter 'A'. Which WHERE clause is correct?

A) WHERE name = 'A'
B) WHERE name LIKE 'A%'
C) WHERE name IN ('A')
D) WHERE name BETWEEN 'A' AND 'B'

A1. B — INNER JOIN only includes rows where the join condition matches in both tables. LEFT JOIN keeps every row from the left table and fills in NULLs for columns from the right table when there is no match.

A2. C — The subquery returns an empty set, so the outer query's IN () matches nothing. num_rows is 0, so the else branch executes and prints 0 results.

A3. A — A FOREIGN KEY constraint ensures referential integrity: you cannot insert a student_id into courses that doesn't already exist as an id in students, preventing orphaned records.

A4. B — LIKE 'A%' uses the % wildcard to match any string beginning with 'A'. Option D is syntactically valid but would include names starting with characters between 'A' and 'B' alphabetically, which is not the intent.

🪞 Recap

An INNER JOIN returns only rows where the join condition is satisfied in both tables; LEFT JOIN and RIGHT JOIN preserve all rows from one side even when the other has no match.
A nested query (subquery) runs first and feeds its result into the outer query, enabling multi-step logic inside a single SQL statement.
The WHERE clause filters rows by evaluating a condition — operators like >, IN, LIKE, and BETWEEN give precise control over which records are returned.
MySQLi's $conn->query() executes all three query types the same way; the SQL string is the only thing that changes.
Combining JOINs with WHERE filters is the everyday pattern for answering real-world questions like "which high-scoring students are enrolled in Math?"

📚 Further Reading

MySQL JOIN documentation — the source of truth on all join types and syntax
MySQL Subquery syntax — full reference for correlated and non-correlated subqueries
MySQLi PHP extension — official PHP docs for every MySQLi method used in this lesson
SQL WHERE clause operators — complete list of filtering operators
⬅️ Previous: Building a HTTP Server with Node.js Using HTTP APIs
➡️ Next: JSON Data

Topic 5 : Joint Query, Nested Query, Filtering DATA