Understanding SELF JOIN in PostgreSQL: A Comprehensive Guide


We’ve journeyed through various JOIN types in PostgreSQL—LEFT, RIGHT, FULL, and CROSS JOINS—and now we arrive at a unique one: the SELF JOIN. As the name suggests, a SELF JOIN is a regular JOIN, but it involves a single table. Let’s dive in to understand its utility and nuances.

What is a SELF JOIN?

A SELF JOIN is a way to combine rows from a single table based on a related column between them. Essentially, it allows you to join a table with itself. This can be very useful for comparing rows within the same table or finding duplicate records, among other use cases.


The syntax for a SELF JOIN is not very different from other JOIN operations:

SELECT columns
FROM table AS alias1
JOIN table AS alias2
ON alias1.column = alias2.column;

The aliases are essential here to help PostgreSQL distinguish between the two instances of the same table.

Example Using the tv_series Table

Suppose we want to find all pairs of TV series in our tv_series table that belong to the same genre.

Sample Data for tv_series Table

Let’s assume we have a variety of TV series across genres like Drama, Comedy, and Sci-Fi, among others.


Here’s how you can accomplish this using a SELF JOIN:

SELECT AS series1, AS series2, A.genre
FROM tv_series AS A, tv_series AS B
WHERE != AND A.genre = B.genre;

This can produce a large result set depending on the size of your table. Reduce this using the LIMIT Clause, e.g. LIMIT 10 at the end of the query.

This query will list all pairs of TV series that share the same genre, while making sure not to pair a series with itself.

series1 series2 genre
Breaking Bad Narcos Drama
Breaking Bad The Crown Drama
Breaking Bad Better Call Saul Drama
Breaking Bad House of Cards Drama
Breaking Bad The Sopranos Drama
Breaking Bad Ozark Drama
Breaking Bad Chernobyl Drama
Breaking Bad Succession Drama
Breaking Bad Euphoria Drama
Breaking Bad Homeland Drama

Common Errors and Solutions

Error Message:

ERROR: table name "tv_series" specified more than once


This error occurs when you try to do a SELF JOIN without using aliases to differentiate between the two instances of the same table.


Always use aliases when performing a SELF JOIN, as demonstrated in the example query above.

Best Practices and Tips

  • Use SELF JOIN when you need to compare rows within the same table.
  • Always use aliases to make your queries clear and to avoid errors.
  • Like other JOIN operations, be mindful of performance implications, especially when working with large datasets.


SELF JOINs offer a unique way to extract valuable insights from a single table by comparing its rows against each other. While they may appear complicated at first, understanding their utility and syntax can add another powerful tool to your PostgreSQL arsenal. Keep an eye out for more in-depth tutorials as we continue to explore the capabilities of PostgreSQL.

Leave a Reply