Modules

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.

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

Table 2: facebook_posts

This table stores information about each post made by users.

And a sample of this table:

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!