Up until now, we’ve been working with simple tables and writing basic queries. But in real-world applications, data is almost always connected across multiple tables. In this sec
MSMuhammad SufiyanSoftware Engineer · 6d ago
Backend Engineering HubT-
Up until now, we’ve been working with simple tables and writing basic queries.
But in real-world applications, data is almost always connected across multiple tables.
In this section, we are going to level up.
We now have a database with:
users
photos
comments
Each table is connected using foreign keys:
A photo belongs to a user
A comment belongs to a user
A comment belongs to a photo
We also now have:
5 users
20 photos
100 comments
This is finally starting to look like a real application database.
Now we’re ready to write more powerful queries.
What Kind of Questions Do We Want to Answer?
Here are some example questions:
What is the average number of comments per photo?
For photo with ID = 3, get all comments.
For each comment, also show the username of the author.
Which photo has the most comments?
Which user has the most activity?
These are not simple “SELECT * FROM table” type queries anymore.
To answer them, we need two powerful SQL concepts:
Joins
Aggregation
1️⃣ Joins – Combining Multiple Tables
A JOIN is used when we want to combine rows from multiple tables.
Think about this question:
Find all comments for photo with ID = 3 and also show the username of the person who wrote each comment.
This question involves:
comments table
photos table
users table
Whenever a query mentions multiple resources, that’s a strong signal that we probably need a JOIN.
Why?
Because the data is stored in different tables.
For example:
comments table has content, user_id, photo_id
users table has username
If we want both comment content AND username in the same result, we must combine those tables.
That’s exactly what JOIN does.
So remember:
👉 If a query mentions more than one table, think JOIN.
2️⃣ Aggregation – Calculating From Many Rows
Aggregation is about turning many rows into one value.
For example:
Average number of comments per photo
Most comments on a photo
Least number of likes
Total number of comments
Whenever you hear words like:
average
most
least
total
count
sum
max
min
That is a signal that we probably need aggregation.
Aggregation functions include:
COUNT()
AVG()
SUM()
MAX()
MIN()
These functions take multiple rows and calculate a single result.
For example:
SELECT COUNT(*) FROM comments;
This does not return all rows.
It returns a single number.
That is aggregation.
How Joins and Aggregation Work Together
In real-world queries, we often combine both.
For example:
Find the photo that has the most comments.
To answer that, we need:
JOIN (because comments are connected to photos)
Aggregation (because we need COUNT of comments)
This is how real backend development works.
Mental Model for Students
When reading a query question, ask:
Step 1:
Does it involve multiple tables?
👉 Use JOIN.
Step 2:
Does it involve words like average, most, count, total?
👉 Use aggregation.
Very often, the answer is both.
HIRINGMINE CAREER SIGNAL
This writing is proof of expertise.
Explore the author’s verified skills, projects and availability—or start a professional conversation.
We now have a database with: