SQL Course: One-to-many Relationships and Left Joins

In the last chapter we learned about one-to-one fields and inner joins. In this chapter we learn about one-to-many relationships and left joins. If you followed the last chapter this should be easy.

We will create a posts table and a post is related to one user and a user can have many posts. That is why it is called a one-to-many relationship.

Prerequisites with links

• Left Joins

Create the posts table

We create the post table with its own primary key and a foreign key to the user's table.

By letting the posts table have an independent primary key there is no restriction of multiple posts having the same foreign key.

CREATE TABLE Posts
(
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  UserID INTEGER NOT NULL,
  Title VARCHAR(255) NOT NULL,
  Content TEXT NOT NULL,
  FOREIGN KEY (UserID) REFERENCES Users(ID)
);

Insert data to the posts table

INSERT INTO Posts (UserID, Title, Content) VALUES 
   (1, 'sql', 'sql content'),
   (1, 'java', 'java content'),
   (2, 'NLP', 'NLP content'),
   (2, 'rust', 'rust content');

Query with a left join.

We will make a query where we get all the users and if the user has a post we retrieve that post.

SELECT u.Username, p.Title, p.Content
FROM Users u
LEFT JOIN Posts p ON u.ID = p.UserID;

Result:
Ben | java | java content
Ben | sql | sql content
Jim | NLP | NLP Content
Jim | rust | rust content
Luk | |