Join - (PostgreSQL Join)

JOIN is a clause in SQL that is used to combine rows from two or more tables based on a related column between them. PostgreSQL supports various types of joins including inner join, left join, right join, and full outer join. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of joins in PostgreSQL.

Syntax

SELECT column1, column2, ...
FROM table1
[INNER|LEFT|RIGHT|FULL] JOIN table2
ON table1.column = table2.column;

• SELECT: List of columns to retrieve.
• FROM: First table to join.
• [INNER|LEFT|RIGHT|FULL] JOIN: Join type, where INNER is the default join type.
  • INNER JOIN: Returns only the matching rows between two tables.
  • LEFT JOIN: Returns all the rows in the left table and matching rows from the right table. Returns null for nonmatching rows on the right table.
  • RIGHT JOIN: Returns all the rows in the right table and matching rows from the left table. Returns null for nonmatching rows on the left table.
  • FULL OUTER JOIN: Returns all rows from both tables, with null for nonmatching rows on either side.
• ON: Specifies the columns to join on.

Example

Let's take a look at an example of inner join in PostgreSQL.

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

In this example, we have two tables orders and customers. want to retrieve the order_id and customer_name columns from these two tables.

We use the INNER JOIN clause to join these two tables based on the customer_id column. The resulting output would be a table with order_id and customer_name columns where the customer_id column in orders matches the customer_id column in customers.

Explanation

In the above example, we used the INNER JOIN clause to combine the rows from orders and customers tables based on their related customer_id column values. The resulting table only includes the rows from both tables where the customer_id value matches.

Use

Joining tables using JOIN clause gives more flexibility in querying data from multiple tables which have a relationship between them.

Important Points

• JOIN is an expensive operation and it's important to keep performance in mind while using it.
• There are different types of joins that can be used based on the requirement like INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
• ON specifies the relationship between columns in tables.

Summary

In this tutorial, we discussed the JOIN clause in PostgreSQL. We covered the syntax, example, output, explanation, use, and important points of the JOIN clause. Understanding the concepts of join can help you master your SQL skills and write complex queries in PostgreSQL.