Faith Njenga

Posted on Sep 8

SQL Is Surviving, Franklin: When Tables Start Talking

#tutorial #beginners #database #sql

So, you survived the basics of SQL.

You learned how to create tables, insert data, retrieve records, update rows, and hopefully avoid accidentally updating the entire database.

Because if you’ve ever run a DELETE statement without a WHERE clause and survived the experience, congratulations. You’ve already developed character.

But eventually, SQL gives you another problem: “Cool. Now get information from multiple tables.”

And suddenly, SELECT * FROM students; doesn’t feel so powerful anymore.

Welcome to JOINs.

Previously in SQL…

In Part 1, we looked at the basic categories of SQL commands:

DDL - Data Definition Language
DML - Data Manipulation Language
DQL - Data Query Language

We also worked with individual tables. But real databases rarely keep everything in one giant table.

Imagine trying to store an entire school database in one table:

student_name	class	teacher	subject	score
Brian	Form 4A	Mr. Kamau	Mathematics	78
Mercy	Form 4A	Mr. Kamau	Mathematics	91
Kevin	Form 3B	Ms. Achieng	Biology	84

At first, this looks convenient. Until you realize that you’re repeating class, teacher, subject, and student information over and over again.

That’s where relational databases come in.

Why Do We Have Multiple Tables?

A good database tries to avoid unnecessary repetition. Instead of putting everything into one massive table, we separate related information.

Let’s say our school is called Greenwood Academy. We could split our data into three clean tables:

`students`

student_id	student_name	class_id
1	Brian	101
2	Mercy	101
3	Kevin	102

`classes`

class_id	class_name	teacher_id
101	Form 4A	501
102	Form 3B	502

`teachers`

teacher_id	teacher_name	subject
501	Mr. Kamau	Mathematics
502	Ms. Achieng	Biology

Now the information is nicely separated. But there’s a problem. If someone asks: “Show me each student’s name, their class, and their teacher.” - no single table contains all three pieces of information.

So how do we bring them together? You guessed it. JOINs.

Before JOINs: Primary Keys and Foreign Keys

Before we start joining tables like we’re collecting Pokémon, we need to understand what connects them. That connection usually comes from primary keys and foreign keys.

Primary Key

A primary key uniquely identifies each record in a table. For example, student_id might uniquely identify every student. No two students should ever share the same student_id.

Foreign Key

A foreign key is a column that refers to a primary key in another table.

For example, look at the relationship between our students and classes tables:

students                 classes
-----------              -----------
student_id               class_id  <--- (Primary Key)
student_name                ↑
class_id    ────────────────┘ 
   │
   └─> (Foreign Key)

That’s the exact relationship SQL uses to connect the tables. Think of it as the database saying: “I don’t have the whole story here, but I know exactly where the rest of it lives.”

So… What Exactly Is a JOIN?

A JOIN allows you to combine rows from two or more tables based on a related column.

SELECT
    students.student_name,
    classes.class_name
FROM students
JOIN classes
    ON students.class_id = classes.class_id;

Here, we’re saying: “Take the students table, find the class that belongs to each student, and bring the information together side-by-side.”

The magic happens right here: ON students.class_id = classes.class_id. That is the structural bridge we are using to link them.

Meet the JOIN Family

SQL has several types of JOINs. The main ones you’ll encounter are:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN

Let’s break down how they behave differently.

1. INNER JOIN

INNER JOIN returns only the records that have a match in both tables. Think of it as: “Only show me people who are actually on both guest lists.”

SELECT
    students.student_name,
    classes.class_name
FROM students
INNER JOIN classes
    ON students.class_id = classes.class_id;

Imagine our dataset looks like this:

Students Table:

student_id	student_name	class_id
1	Brian	101
2	Mercy	101
3	Kevin	102
4	Sarah	999

Classes Table:

class_id	class_name
101	Form 4A
102	Form 3B

Sarah’s class_id is 999. Since there is no class with a class_id of 999 in the classes table, Sarah disappears from the results entirely.

The INNER JOIN basically says: “If we can’t find a perfect match, drop the row.”

2. LEFT JOIN

A LEFT JOIN returns all records from the left table, and the matching records from the right table. If there is no match, SQL fills the right-side columns with NULL.

SELECT
    students.student_name,
    classes.class_name
FROM students
LEFT JOIN classes
    ON students.class_id = classes.class_id;

Using the same data from before, Sarah would now appear in our results:

student_name	class_name
Brian	Form 4A
Mercy	Form 4A
Kevin	Form 3B
Sarah	NULL

Because the students table is written first (on the left side of the JOIN keyword), the LEFT JOIN mandates: “I don’t care if you find a match or not. Everybody from my table is coming along.”

This is incredibly useful when you want to look for missing or unlinked records. For example:

SELECT
    students.student_name,
    classes.class_name
FROM students
LEFT JOIN classes
    ON students.class_id = classes.class_id
WHERE classes.class_id IS NULL;

Now we are specifically filtering for students who don’t have a matching class. SQL has officially entered detective mode.

3. RIGHT JOIN

RIGHT JOIN is the exact opposite of LEFT JOIN. It keeps all records from the right table, even if there isn’t a matching record in the left table.

SELECT
    students.student_name,
    classes.class_name
FROM students
RIGHT JOIN classes
    ON students.class_id = classes.class_id;

This states: “Every class stays. If a student belongs to it, bring them along. If a class has zero students, display the class name anyway and fill the student column with NULL.”

That said, RIGHT JOIN isn’t used as often in the real world. Why? Because you can always rewrite it as a LEFT JOIN simply by flipping the order of your tables in the query:

SELECT
    students.student_name,
    classes.class_name
FROM classes
LEFT JOIN students
    ON students.class_id = classes.class_id;

Same idea, different direction. Keeping everything as a LEFT JOIN usually makes scripts much easier to read from top to bottom.

4. FULL OUTER JOIN

A FULL OUTER JOIN goes completely all-in. It keeps matching records, unmatched records from the left table, and unmatched records from the right table.

Essentially: “Bring everybody to the party. We’ll figure out the missing pieces later.”

SELECT
    students.student_name,
    classes.class_name
FROM students
FULL OUTER JOIN classes
    ON students.class_id = classes.class_id;

This query will simultaneously surface:

Students assigned to classes.
Students without classes (like Sarah).
Dynamic classes that currently have no students assigned to them.

Important Note: Not every database system supports FULL OUTER JOIN directly out of the box. For example, MySQL does not support it natively (you have to fake it using a UNION of a Left and Right Join), whereas engines like PostgreSQL do. Always verify which database system you're building for.

JOIN Cheat Sheet

If you forget everything else, keep this quick mental framework handy:

JOIN Type	What does it keep?	Quick Analogy
`INNER JOIN`	Only matching records	The intersection only
`LEFT JOIN`	Everything from the left table + matches	Left table gets full VIP treatment
`RIGHT JOIN`	Everything from the right table + matches	Right table gets full VIP treatment
`FULL OUTER JOIN`	Everything from both tables	Universal invite list

The ON Clause: Where the Magic Happens

You’ve probably noticed this piece of syntax repeating: ON students.class_id = classes.class_id.
The ON clause tells SQL exactly how the tables are related.

JOINing Three Tables

What if we want to bridge all three of our tables together to find out which teacher is teaching which student?

You aren't limited to joining just two tables at a time. You can chain JOIN statements sequentially to build broader horizons. SQL will process them linearly, using the cumulative dataset from the first join to connect to the next table.

Here is how you link students to classes, and then connect those classes to their respective teachers:

SELECT 
s.student_name,
c.class_name,
t.teacher_name,
t.subject
FROM students sINNER JOIN classes cON s.class_id = c.class_idINNER JOIN teachers tON c.teacher_id = t.teacher_id;

Pro-Tip: Table Aliasing

Notice the letters s, c, and t right after the table names? Those are aliases. They save you from having to type out long table names like students. student_name repeatedly. By declaring FROM students s, you tell SQL: "For the rest of this query, I'll just use s as a shorthand for this table." It keeps your multi-table joins beautifully organized and readable!

DEV Community