PostgreSQL Joins

PostgreSQL Joins

In PostgreSQL, joins are used to combine rows from two or more tables based on a related column between them. There are several types of joins, each serving a specific purpose depending on how you want to combine the data.

Types of Joins in PostgreSQL

1. INNER JOIN
2. LEFT JOIN (or LEFT OUTER JOIN)
3. RIGHT JOIN (or RIGHT OUTER JOIN)
4. FULL JOIN (or FULL OUTER JOIN)
5. CROSS JOIN
6. SELF JOIN

1. INNER JOIN

• INNER JOIN returns rows when there is a match in both tables involved in the join. It excludes rows that don’t have a matching counterpart in the other table.

Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example

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

• This query will return only customers who have placed orders. Customers without orders will not be included.

2. LEFT JOIN (or LEFT OUTER JOIN)

• LEFT JOIN returns all rows from the left table and matching rows from the right table. If there is no match, NULL values will be returned for columns from the right table.

Syntax

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Example

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

• This query returns all customers and their orders. If a customer hasn’t placed an order, the order_date will be NULL.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

• RIGHT JOIN is similar to LEFT JOIN, but it returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.

Syntax

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example

SELECT customers.first_name, orders.order_date
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

• This query returns all orders and the customers who placed them. If an order does not have a corresponding customer, the customer details will be NULL.

4. FULL JOIN (or FULL OUTER JOIN)

• FULL JOIN returns all rows when there is a match in either the left or the right table. Rows that do not match will have NULL values in the columns from the table that lacks a matching row.

Syntax

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

Example

SELECT customers.first_name, orders.order_date
FROM customers
FULL JOIN orders ON customers.customer_id = orders.customer_id;

• This query returns all customers and all orders. If a customer has no orders, or an order has no matching customer, the corresponding values will be NULL.

5. CROSS JOIN

• CROSS JOIN returns the Cartesian product of the two tables. That means it will return every possible combination of rows from both tables. If the first table has m rows and the second table has n rows, the result will have m * n rows.

Syntax

SELECT columns
FROM table1
CROSS JOIN table2;

Example

SELECT customers.first_name, products.product_name
FROM customers
CROSS JOIN products;

• This query returns a combination of all customers and all products, so it might generate a large number of rows depending on the size of the tables.

6. SELF JOIN

• A SELF JOIN is a regular join but the table is joined with itself. It’s typically used when there is a hierarchical relationship within the same table.

Syntax

SELECT columns
FROM table1 AS alias1
JOIN table1 AS alias2
ON alias1.column = alias2.column;

Example

SELECT e1.first_name AS Employee, e2.first_name AS Manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

• In this query, the employees table is joined with itself to show each employee and their corresponding manager.

Summary of Joins

Join Type	Description
INNER JOIN	Returns only rows with matching data in both tables.
LEFT JOIN	Returns all rows from the left table, and matching rows from the right table. Non-matching rows from the right table have NULL values.
RIGHT JOIN	Returns all rows from the right table, and matching rows from the left table. Non-matching rows from the left table have NULL values.
FULL JOIN	Returns all rows when there is a match in either the left or right table. Non-matching rows from both tables have NULL values.
CROSS JOIN	Returns the Cartesian product of both tables (every combination of rows).
SELF JOIN	Joins a table with itself, often used for hierarchical data.

Would you like examples of combining multiple joins or working with complex data sets?