Understanding Join Queries

What is a JOIN?

A JOIN combines data from multiple tables into a single result. There are several types of JOINs (INNER, LEFT, RIGHT, FULL), but we’ll focus on INNER JOIN, which is the most commonly used.

INNER JOIN returns only rows where there’s a match in both tables. For example, if you’re joining posts and users tables, it would only show posts where the user_id matches an existing user id - essentially filtering out any posts with invalid or missing authors.

Note:

In SQL, JOIN without any qualifier defaults to INNER JOIN. This means JOIN and INNER JOIN are equivalent - you’ll see both syntaxes used interchangeably in examples and documentation.

Your First JOIN Query

Let’s write a query that shows posts from a specific user along with their information. First, check your users table to see what user IDs you have:

SELECT id, username FROM users;

Now pick a user ID from your results and use it in this JOIN query (we’ll use ID 4 as an example - replace it with an actual ID from your users table):

SELECT posts.title, posts.content, users.username, users.email
FROM posts
INNER JOIN users ON posts.user_id = users.id
WHERE users.id = 4;

Breaking down the syntax

SELECT posts.title, posts.content, users.username, users.email

• We specify which columns we want from each table.
• posts.title means “the title column from the posts table”.
• users.username means “the username column from the users table”.

FROM posts

• Start with the posts table as our main table.

INNER JOIN users ON posts.user_id = users.id

• Join with the users table.
• ON posts.user_id = users.id specifies how to match rows.
• “Match posts to users where the post’s user_id equals the user’s id”.

WHERE users.id = 4

• Filter the results to show only posts by user ID 4 (replace 4 with your chosen user ID).
• This focuses our results on one user’s posts.

Understanding the Results

When you run this query, you’ll see something like:

Notice how:

• Only posts from your chosen user are shown.
• Each row combines post data with user data.
• The database automatically matched the relationships.
• The post title and content appear because we selected posts.title and posts.content in our query.
• The user’s username and email appear in each row because we selected users.username and users.email in our query.