PostgreSQL INNER JOIN

PostgreSQL `INNER JOIN`

An INNER JOIN is used to retrieve data from two tables where there is a match between the specified columns in both tables. Only rows that satisfy the join condition will be included in the result. If there is no match, the row is excluded from the final result.

1. Basic Syntax of `INNER JOIN`

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

table1 and table2: The tables that are being joined.
table1.column = table2.column: The condition that must be met for the rows to be matched between the two tables.

2. Example: Using `INNER JOIN` to Retrieve Data from Two Tables

Example 1: Get All Customers and Their Orders

Consider two tables:

customers: Contains customer details (e.g., customer_id, first_name, last_name).
orders: Contains order details (e.g., order_id, customer_id, order_date).

Query: Retrieve All Customers with Orders

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

This query returns customers who have placed orders, along with the order date. If a customer has no order, they will not appear in the result.

Result:

first_name	last_name	order_date
John	Doe	2025-01-01
Alice	Johnson	2025-01-05

Note: Only customers who have placed orders are included in the result.

3. Example: `INNER JOIN` with Multiple Tables

Example 2: Get Employees and Their Assigned Projects

Consider the following tables:

employees: Contains employee details (employee_id, first_name, last_name).
projects: Contains project details (project_id, employee_id, project_name).

Query: Retrieve Employees and Their Assigned Projects

SELECT employees.first_name, employees.last_name, projects.project_name
FROM employees
INNER JOIN projects
ON employees.employee_id = projects.employee_id;

This query returns employees who are assigned to projects, including the project name.

4. Example: Filtering with `WHERE` Clause

You can combine INNER JOIN with a WHERE clause to filter the result further.

Example 3: Get Customers and Orders on a Specific Date

Query: Retrieve Orders Made by Customers on '2025-01-01'

SELECT customers.first_name, customers.last_name, orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id
WHERE orders.order_date = '2025-01-01';

This query returns customers who placed orders on January 1st, 2025.

5. Difference Between `INNER JOIN` and `LEFT JOIN`

INNER JOIN: Only includes rows where there is a match in both tables.
LEFT JOIN: Includes all rows from the left table, and matching rows from the right table. If no match is found, the result will have NULL values for columns from the right table.

Example: Customers and Orders with `LEFT JOIN`

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

In the case of LEFT JOIN, even customers who haven't placed any orders will appear, with NULL in the order_date field.

6. Performance Considerations

Efficiency: INNER JOIN is typically faster than LEFT JOIN because it does not need to return rows from the left table when there is no matching data in the right table.
Indexes: Creating indexes on the columns used in the ON condition can help improve performance for INNER JOIN queries, especially when working with large datasets.

7. Summary of `INNER JOIN`

Operation	SQL Command
Retrieve Rows with Matching Data from Two Tables	`SELECT * FROM table1 INNER JOIN table2 ON condition;`
Filter Data Based on Join Condition	Only rows that match the condition are included in the result.
When to Use	Use when you need only rows with matching data in both tables.

Would you like to see more examples or use cases with different kinds of joins (e.g., multiple INNER JOIN operations)? 🚀

Souy Soeng

PostgreSQL INNER JOIN