In this tutorial, we explores how an INNER JOIN works in PostgreSQL. We also explore the examples on how to work with INNER JOINS using a real-world database.
Requirements:
- Installed PostgreSQL on your system.
- The Pagila sample database that is loaded into your PostgreSQL server. Feel free to use any other database for demonstration purposes.
With the given requirements met, we can proceed to the following steps.
Understanding an INNER Join
Before diving into the query syntax and examples, let us understand the building block of an SQL INNER JOIN.
An INNER JOIN selects records that have matching values in the provided tables. It fetches the rows with at least one match between the columns that are specified in the JOIN condition.
Inner Join Syntax
The following describes the syntax of an INNER JOIN in PostgreSQL:
FROM table1
INNER JOIN table2 ON join_condition;
We use the join_condition to define the columns on which the inner join is carried out.
PostgreSQL INNER JOIN Examples
Let us explore some basic example usage to perform an INNER JOIN.
Let us take the Pagila database which consists of several tables: actor, category, film, film_actor, film_category, and more. We use the film and category tables for our examples.
The film table contains information about films such as film_id, title, description, release_year, etc.
The category table contains various categories that the films can belong to such as category_id and name.
Basic Example of PostgreSQL INNER JOIN
We can use a basic INNER JOIN to combine more than two tables. For example, suppose we wish to retrieve a list of films along with their corresponding categories. We can run a basic INNER JOIN query as follows:
FROM film f
INNER JOIN film_category fc ON f.film_id = fc.film_id
INNER JOIN category c ON fc.category_id = c.category_id;
The given query joins the film and category tables using the film_category table as an intermediary. The query retrieves the film title from the film table and the category name from the category table.
NOTE: We use the film_id and category_id columns as the JOIN conditions.
The resulting table is as follows:
PostgreSQL INNER JOIN Multiple Tables
We can also use an INNER JOIN to combine more than two tables. For example, suppose we wish to get the list of films, their corresponding categories, and the respective films’ actors. Then, we can use an INNER JOIN to join three tables as shown in the following query:
FROM film f
INNER JOIN film_category fc ON f.film_id = fc.film_id
INNER JOIN category c ON fc.category_id = c.category_id
INNER JOIN film_actor fa ON f.film_id = fa.film_id
INNER JOIN actor a ON fa.actor_id = a.actor_id;
The previous query extends on the previous example by joining the film_actor and the actor_tables. The query retrieves the film title, category name, and the actor’s first and last names. In this case, we use the film_id and the actor_id as the JOIN conditions.
The resulting table is as follows:
PostgreSQL INNER JOIN Aliases
We can also use aliases to provide shorter and more readable table references. For example, we can shorten the previous query using aliases as shown in the following:
FROM film AS f
INNER JOIN film_category AS fc ON f.film_id = fc.film_id
INNER JOIN category AS c ON fc.category_id = c.category_id
INNER JOIN film_actor AS fa ON f.film_id = fa.film_id
INNER JOIN actor AS a ON fa.actor_id = a.actor_id;
We assign aliases to the tables using the AS keyword in this query. The aliases (f, c, fa, a) are shorthand references in the SELECT and JOIN clauses which make the query more concise and readable.
PostgreSQL INNER JOIN Where Clause
We can also add the conditional filtering using the WHERE clause. An example is as follows:
FROM film AS f
INNER JOIN film_category AS fc ON f.film_id = fc.film_id
INNER JOIN category AS c ON fc.category_id = c.category_id
INNER JOIN film_actor AS fa ON f.film_id = fa.film_id
INNER JOIN actor AS a ON fa.actor_id = a.actor_id
WHERE f.release_year > 2005;
Output:
Conclusion
We explored the various examples of INNER JOIN usage using the Pagila sample database in PostgreSQL.
