Simple Joins in PostgreSQL

1. Introduction

In PostgreSQL, joins are used to combine rows from two or more tables based on a related column. This lesson covers the basic types of joins—Inner Join, Left Join, Right Join, and Full Outer Join—and provides examples for better understanding.

2. Types of Joins

Joins can be classified into several types:

• Inner Join
• Left Join (or Left Outer Join)
• Right Join (or Right Outer Join)
• Full Outer Join

3. Inner Join

An Inner Join returns only the rows that have matching values in both tables.

SELECT a.id, a.name, b.order_date
FROM customers a
INNER JOIN orders b ON a.id = b.customer_id;
        

4. Left Join

A Left Join returns all rows from the left table, and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.

SELECT a.id, a.name, b.order_date
FROM customers a
LEFT JOIN orders b ON a.id = b.customer_id;
        

5. Right Join

A Right Join 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.

SELECT a.id, a.name, b.order_date
FROM customers a
RIGHT JOIN orders b ON a.id = b.customer_id;
        

6. Full Outer Join

A Full Outer Join returns all rows when there is a match in either the left or right table records. Rows without a match will return NULL for the columns of the table that does not have a match.

SELECT a.id, a.name, b.order_date
FROM customers a
FULL OUTER JOIN orders b ON a.id = b.customer_id;
        

7. Best Practices

When using joins in PostgreSQL, consider the following best practices:

• Always specify the join condition to avoid Cartesian products.
• Use INNER JOIN when you need only matching records.
• Prefer LEFT JOIN when you need all records from the left table.
• Optimize your queries by indexing the columns used in join conditions.

8. FAQ