Syntax of a Join | SQL Intermediate

Let's say we are dealing with 2 tables from the Facebook database: one for user profile information (facebook_users) and another for posts (facebook_posts).

And our objective is: Find for each user name, what is the content of their post!

Table 1: facebook_users

This table stores information about each user's profile.

Column Name	Description
user_id	Unique identifier for each user
name	The user's full name
birthday	The user's date of birth
email	The user's email address
join_date	Date when the user joined Facebook

And here is small small of how the data looks like if you pull it:

user_id	name	birthday	email	join_date
1	Alice Johnson	1990-04-15	alicej@example.com	2015-03-01
2	Bob Smith	1985-08-22	bobsmith@example.com	2016-05-12
3	Carol Martinez	1992-11-30

Table 2: facebook_posts

This table stores information about each post made by users.

Column Name	Description
post_id	Unique identifier for each post
user_id	ID of the user who created the post
content	Text content of the post
post_date	Date when the post was created

And a sample of this table:

post_id	user_id	content	post_date
101	1	Enjoying the sunny beach!	2021-06-15
102	2	Just read a great book.	2021-06-16
103	3	Loving my new yoga class!	2021-06-17

Accessing Both Tables Using a Common Identifier

To link these two tables and access complete information, we need to find a common identifier. In this case, it's user_id.

This identifier is present in both tables and acts as a bridge, allowing us to join data from facebook_users with facebook_posts. By matching the user_id in the users table with the user_id in the posts table, we can access and combine the user's profile information with their posts.

Here’s a simplified example of how it might look in SQL:

SELECT users.name AS user_name,
       posts.content AS post_content
  FROM facebook_users users
  JOIN facebook_posts posts
    ON users.user_id = posts.user_id;

Step 1: Using Aliases

First off, notice how we've shortened facebook_users to users and facebook_posts to posts. That’s just to make our SQL code easier to read and write. It's like giving a nickname to a table.

Step 2: Next, the JOIN part

We're telling SQL to take the users table and link it up with the posts table. The ON part is super important – it's how SQL knows which user's posts to match up with which user profile. In our case, we're matching the user_id in the users table with the user_id in the posts table:

ON users.user_id = posts.user_id;

Step 3: Running the query

Now, when you run this query, SQL goes through both tables, links up rows based on user_id, and shows us the results – each post along with the name of the person who posted it.

Now, let's dive into our first lesson INNER JOIN. No need to know what is it, you will find out soon!