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
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.