JOINs in PostgreSQL: Types with 9 Examples

PostgreSQL is an open-source relational database management system (RDBMS) known for its robust features, reliability, and scalability. It is used to manage and store data, both for small-scale applications and for large enterprise systems. PostgreSQL supports SQL standards and offers advanced features such as data reporting, editing, import and export, building pivot tables, master-detail relations, and so on.

Today, we are going to talk about JOINs in PostgreSQL as nothing works as good when it comes to combining data from one or multiple tables. They allow you to retrieve related information by matching rows across a database according to the specified criteria. Stay tuned to learn more about the types of JOINs and see how they work in practice within dbForge Studio.

Types of JOINs in PostgreSQL

As we have already mentioned, JOINs combine rows from two or more tables based on a common column between them with the help of complex queries that involve data from different sources. Specifying the type of JOIN (such as INNER JOIN, LEFT JOIN, etc.) allows you to control how rows are matched and returned from the tables you are working with. In order to cover the topic fully, let us begin with the list of different types of JOINs and work our way to practical examples demonstrated within dbForge Studio for PostgreSQL — a powerful and user-friendly IDE. Its cutting-edge features allow you to create, write and execute queries, as well as edit and adjust the code to all your requirements.

There are nine types of JOINs used in PostgreSQL:

Let us assume that there are two test tables in the public schema: employee and department. We have pre-populated these tables with test data using Data Generator — a part of a powerful PostgreSQL GUI client aimed at creating massive volumes of meaningful, realistic test data. Having prepared the testing ground, let us examine each of the aforementioned JOINs individually, exploring their theoretical background and providing practical code illustrations. As we will be working with SQL queries, SQL coding assistance will help us eliminate errors and speed up the code writing process.

INNER JOIN

Our initial showcase is the INNER JOIN, which exclusively selects rows from both tables that meet the join condition, effectively creating an intersection of the rows based on the specified criteria.

SELECT
  e.employee_name,
  d.department_name
FROM employee e
  INNER JOIN department d
    ON e.department_id = d.department_id;

In the provided query, the INNER JOIN combines data from the employee and department tables using the department_id and department_id columns respectfully. This way only rows with matching IDs in both tables are included in the result set.

LEFT JOIN (or LEFT OUTER JOIN)

Our next example concerns LEFT JOIN. It retrieves all rows from the left table (the first table specified in the JOIN clause), and includes matched rows from the right table (the second table specified) based on the join condition. In cases where no match is found in the right table, NULL values are added to the result set.

SELECT
  e.employee_name,
  d.department_name
FROM employee e
  LEFT JOIN department d
    ON e.department_id = d.department_id;

The structure of the query above is similar to the previous example, but with a single difference: instead of using INNER JOIN, it features LEFT OUTER JOIN. All employee details from the employee table are included in the result set, regardless of whether there is a corresponding entry in the department table. This way, we get employee names with their respective departments, with NULL values for department names in case no match is found in department.

RIGHT JOIN (or RIGHT OUTER JOIN)

A RIGHT JOIN is similar to a LEFT JOIN, but it returns all rows from the right table, along with matched rows from the left one. If there is no match in the left table, NULL values are appended to the result set.

SELECT
  e.employee_name,
  d.department_name
FROM Employee e
  RIGHT JOIN department d
    ON e.department_id = d.department_id;

FULL JOIN (or FULL OUTER JOIN)

A FULL JOIN returns all rows from both tables, matching rows where the join condition is met and appending NULL values for unmatched rows from either table.

SELECT
  e.employee_name,
  d.department_name
FROM Employee e
  FULL JOIN department d
    ON e.department_id = d.department_id;

FULL OUTER JOIN effectively combines the results of a LEFT JOIN and a RIGHT JOIN.

CROSS JOIN

A CROSS JOIN produces the Cartesian product of two tables, resulting in each row from the first table being paired with every row from the second one. Unlike other JOIN types, it does not require a join condition and, therefore, generates a huge number of records, especially if the initial tables have numerous rows.

SELECT
  e.employee_name,
  d.department_name
FROM employee e
  CROSS JOIN department d;

This query generates a result set where each row from the employee table is combined with every row from department. The output will contain every possible combination of employee details paired with department names.

SELF JOIN

A SELF JOIN is an operation where a table is joined with itself. This technique is valuable for comparing rows within the same table, particularly when seeking related records or conducting hierarchical queries.

SELECT
  e1.Employee_Name AS Employee_Name,
  e1.Employee_Position AS Employee_Position,
  e2.Employee_Name AS Manager_Name
FROM Employee e1
  INNER JOIN employee e2
    ON e1.manager_id = e2.employee_id;

The provided query retrieves data from the employee table twice, aliasing it as e1 and e2. It then performs an inner join operation on employee, combining it with itself based on the condition that manager_id from the first alias matches employee_id from the second one. As a result, the output contains information about employees and their corresponding managers.

NATURAL JOIN

A NATURAL JOIN automatically matches columns with the same name in both tables, simplifying the JOIN syntax by omitting the explicit join condition. It returns all rows where the values in the matched columns are equal.

SELECT
  e.Employee_Name,
  d.department_name
FROM Employee e
  NATURAL JOIN department d;

JOIN with USING clause

A JOIN with a USING clause is similar to a NATURAL JOIN but allows you to specify explicitly the columns to be used for the join. It joins the tables based on the specified columns and returns all rows where the values in those columns are equal.

SELECT
  e.employee_name,
  d.department_name
FROM employee e
  JOIN department d USING (department_id);

Anti JOIN

An Anti JOIN in PostgreSQL, also known as an Anti Semi Join, is a technique used to select rows from one table that have no corresponding match in another table. It is achieved by using a LEFT JOIN operation followed by filtering out the rows where the join condition fails, typically by looking for NULL values in the columns from the second table. Anti JOINs are useful for identifying records that do not meet certain criteria or do not have related data in another table. This allows for validating data or detecting possible anomalies.

SELECT
  Employee_Name
FROM Employee e
  LEFT OUTER JOIN department d
    ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Conclusion

Mastering the different JOIN types in PostgreSQL is vital for effective data retrieval and analysis. Each JOIN serves distinct purposes, from combining related data to identifying discrepancies across tables. With dbForge Studio for PostgreSQL, users benefit from a user-friendly interface and powerful query-writing tools, streamlining the process of constructing JOIN queries. Moreover, its intuitive features like Intelligent SQL coding, code formatting, Query Profiler, Data Generator, Data and Schema Compare, and many more can be of use not only when you are working with JOINs but in your everyday working routine.