Explicit vs Implicit Joins in PostgreSQL

In PostgreSQL, joining tables is an important aspect of querying data from relational databases. PostgreSQL offers two primary methods for joining tables which are explicit joins and implicit joins. Each method serves a distinct purpose.

In this article, we will understand their differences along with the examples are essential for efficient database querying and data manipulation in PostgreSQL.

Explicit joins in PostgreSQL

Explicit joins in PostgreSQL involve specifying the join condition in the PostgreSQL query itself using the JOIN keyword. This method explicitly defines how the tables should be combined based on the specified criteria. Two common types of explicit joins are INNER JOIN and LEFT JOIN.

Syntax:

SELECT column1, column2
FROM table1
JOIN table2 ON table1.column_id = table2.column_id;

Explanation: In the above syntax select specific columns (column1, column2) from table1 and table2, joining them based on a common column (column_id).

The ON keyword specifies the condition for the join, ensuring that rows from table1 and table2 are matched where their column_id values are equal.

Implicit joins in PostgreSQL

Implicit joins, also known as comma-separated joins, involve listing the tables in the FROM clause and specifying the join condition in the WHERE clause. Although they achieve the same result as explicit joins, implicit joins lack readability and can lead to ambiguity in complex queries.

Syntax:

SELECT column1,column2,...
FROM table1, table2, ...
WHERE table1.column = table2.column;

Explanation: In the above query, it selects specific columns (column1, column2, etc.) from multiple tables (table1, table2, etc.) based on a condition where a column in table1 is equal to a column in table2.

It retrieves data that satisfies the specified condition from the specified columns in the specified tables.

Examples of Explicit vs Implicit Joins

To understand the Explicit vs implicit Joins in PostgreSQL we need 2 tables on which we will perform various operations and queries. Here we have orders and customers where the orders table consists of order_id, customer_id, order_date, and total_amount. The customers consists of customer_id, customer_name and email as Columns.
After Inserting some data into the orders table, the table looks:

Orders table

After Inserting some data into the customers table, the table looks:

Customers Table

Exmaple 1: Explicit Inner Join

Consider orders and customers. To retrieve a list of orders along with their corresponding customer information, you can use an INNER JOIN as follows:

SELECT orders.*, customers.*
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Ouptut:

Explicit Inner Join

Example 2 :Explicit Left Join

In situations where we want to retrieve all records from the left table (even if there are no matches in the right table), you can use a LEFT JOIN.

For instance, to fetch all orders and their corresponding customer information, including orders without associated customers, we can execute the following query.

SELECT orders.*, customers.*
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

Ouptut:

Explicit Left Join

Example 3: Implicit Inner Join

Using the same example of orders and customers tables, an implicit inner join can be written as follows

SELECT orders.*, customers.*
FROM orders, customers
WHERE orders.customer_id = customers.customer_id;

Output:

Implicit Left Join

Example 4: Implicit Left Join

Similarly, an implicit left join can be performed as shown below:

SELECT orders.*, customers.*
FROM orders, customers
WHERE orders.customer_id = customers.customer_id(+);

Output:

Implicit Left Join

Difference Between Explicit vs Implicit Joins in PostgreSQL

Feature	Explicit Join	Implicit Join
Syntax	Uses the JOIN keyword to link tables	Uses a WHERE clause to specify join conditions
Readability	Generally considered more readable and explicit	May be less readable, especially with complex joins
Join Types	Supports various join types (INNER, LEFT, RIGHT, FULL)	Supports only INNER joins
Filter Conditions	Join conditions are explicitly specified in the ON clause	Join conditions are mixed with WHERE clause
Performance	Generally more efficient as the query planner can optimize the join strategy	Can be less efficient, especially with complex queries

Conclusion

In summary, explicit joins offer a more structured and readable approach to combining tables in PostgreSQL queries. They provide clarity regarding the relationship between tables and are preferred for maintainability and understanding.

On the other hand, implicit joins, while syntactically shorter, sacrifice readability and can lead to confusion, especially in complex queries. Therefore, it is advisable to use explicit joins for better query comprehension and code maintainability in PostgreSQL.