SQL Course: Self Join

It’s time to add followers to our database application.

We will establish one more many-to-many relationship. Our join table will be the followers table.

The interesting thing with this table is that is the two foreign keys are referencing the same table, the users table. Therefore we need to query the followers with a self join.

Read more about self joins.

Create the join table

• The OwnerID is the user who follow another user.
• The FollowingID is the user who is being followed.

CREATE TABLE Follows
  (
  ID INTEGER PRIMARY KEY AUTOINCREMENT,
  OwnerID INTEGER,
  FollowingID INTEGER,
  FOREIGN KEY (OwnerID) REFERENCES Users(ID),
  FOREIGN KEY (FollowingID) REFERENCES Users(ID)
  );

Insert data

Ex user with ID 1, Ben follow user with ID 2, Jim.

INSERT INTO Follows (OwnerID, FollowingID) VALUES
  (1, 2),
  (2, 1),
  (2, 3);

Query with self join.

In this query we retrieve all users and the users they follow and then group by user.

SELECT 
owner.username, followsUser.Username
FROM Follows f
JOIN Users owner ON f.OwnerID = owner.ID
JOIN Users followsUser ON f.FollowingID = followsUser.ID
GROUP BY owner.username, followsUser.Username;

We can also get all the users a particular user is following.

SELECT 
owner.username, followsUser.Username
FROM Follows f
JOIN Users owner ON f.OwnerID = owner.ID
JOIN Users followsUser ON f.FollowingID = followsUser.ID
WHERE owner.Username = 'Ben';