logo

SQL Self-Joins

Hold up, another join?! ๐Ÿ˜ตโ€๐Ÿ’ซ

Yes, you guessed it right! After learning about the different joins in this tutorial, now it's time to learn self-joins!

Self-joins might sound complex, but they're not. It's just like a table joining hands with itself. Think of it as a table of Amazon transactions joining with another table of Amazon transactions. Don't worry, it's simpler than it seems! ๐Ÿ˜‰

In this tutorial, we'll explore self-joins using the dataset in this Goodreads Books SQL question.

Self-Joins Example: Personalizing Book Suggestions

Imagine you're part of the Goodreads Books team and you're designing a book recommendation system that provides users with personalized book suggestions tailored to their preferences. In other words, if someone loves romance, you want to give them more romantic options!

Finding Similar Books Within the Same Genre

Here's the query that generates meaningful book recommendations:


Here's the breakdown:

  1. We're using a self-join on the column to find books of the same genre.
  2. The clause ensures that we don't suggest the same book as itself.
  3. The result will show their genres, the book they're reading, and the suggested book from the same genre.
current_genrecurrent_booksuggested_book
Non-FictionAce the Data Science InterviewData Engineering Cookbook
Non-FictionAce the Data Science InterviewBuilding Data-Intensive Apps with Flask and SQLAlchemy
Non-FictionAce the Data Science InterviewBlink: The Power of Thinking Without Thinking
Non-FictionAce the Data Science InterviewThe Power of Habit
Non-FictionAce the Data Science InterviewStorytelling with Data: A Data Visualization Guide for Business Professionals

Cool, right? ๐Ÿค“ You're suggesting awesome books to readers based on their genre preferences!

Expanding the Suggestions: A Second Book

What if you want to suggest a second book? Well, just like that, we'll add another twist:

Taking Book Recommendations to the Next Level

โ€ผ๏ธ Hold on, though! Be cautious when dealing with large tables. Joining a table to itself can create a lot of matches, which might slow things down. In this case, we're talking about 2,886 rows, so we've got a trick up our sleeveโ€”a clause:


Here's what's happening:

  1. We've levelled up with 2 additional self-joins () to generate a 2nd suggested book.
  2. The conditions ( and ) ensure that the 2nd suggestion is different from both the 1st suggestion and the current book.
current_genrecurrent_booksuggested_book_1suggested_book_2
Non-FictionAce the Data Science InterviewPython for Data AnalysisDesigning Data-Intensive Applications
Non-FictionAce the Data Science InterviewPython for Data AnalysisData Engineering with Python
Non-FictionAce the Data Science InterviewPython for Data AnalysisFundamentals of Data Engineering
Non-FictionAce the Data Science InterviewPython for Data AnalysisEducated: A Memoir
Non-FictionAce the Data Science InterviewPython for Data AnalysisData Science for Business

And there you have it! Self-joins in actionโ€”making book recommendations exciting and personal. ๐Ÿ˜‰

What's Next: JOINING TABLES (WITHOUT JOINS)

Often joining two tables is synonymous with combining two tables. But did you know there is another way to combine tables WITHOUT USING A JOIN?

In the next tutorial, we'll think about joining tables VERTICALLY using , instead of HORIZONTALLY (which is how the joins we've covered so far work).

SQL JOIN vs. UNION

Spatially challenged, and confused by what we mean by vertical vs. horizontal? That's a good sign to dive into the next tutorial!

SQL UNION, INTERCEPT, EXCEPT


Next Lesson

SQL UNION ๐Ÿ™