PostgreSQL INNER JOIN

Key INNER JOIN Concepts

SELECT *
FROM table1
INNER JOIN table2
ON table1.id = table2.id

ON customers.id = 
   orders.customer_id

FROM customers c
INNER JOIN orders o
ON c.id = o.customer_id

JOIN table2 ON ...
JOIN table3 ON ...
JOIN table4 ON ...

🔹 Basic INNER JOIN

The basic INNER JOIN combines rows from two tables where the join condition is met. Only rows with matching values in both tables appear in the result. This is the foundation of relational database queries, allowing you to connect related data stored in separate tables and retrieve meaningful combined information in a single query.

-- Get customer names with their order information
SELECT 
    customers.customer_id,
    customers.customer_name,
    customers.email,
    orders.order_id,
    orders.order_date,
    orders.total_amount
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

🔹 INNER JOIN with Table Aliases

Table aliases provide shorthand names for tables, making queries more readable and easier to write. They're especially useful when joining multiple tables or when table names are long. Aliases are defined after the table name and can be used throughout the query to reference columns, reducing typing and improving code clarity for complex queries.

-- Using aliases 'c' for customers and 'o' for orders
SELECT 
    c.customer_name,
    c.email,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > 100;

🔹 INNER JOIN with WHERE Clause

You can add WHERE clauses to filter the results of an INNER JOIN. The WHERE clause is applied after the join is performed, allowing you to narrow down results based on specific conditions. This is useful for finding specific subsets of joined data, such as orders above a certain amount or customers from a particular region.

-- Get orders over $500 with customer details
SELECT 
    c.customer_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > 500
ORDER BY o.total_amount DESC;

🔹 Multiple INNER JOINs

You can chain multiple INNER JOINs to combine data from three or more tables. Each join connects two tables based on their relationship, building a comprehensive result set. This is essential for complex queries in normalized databases where related information is spread across multiple tables, enabling you to retrieve complete data in a single efficient query.

-- Join customers, orders, and order_items tables
SELECT 
    c.customer_name,
    o.order_id,
    o.order_date,
    oi.product_id,
    oi.quantity,
    oi.price
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id;

🔹 INNER JOIN with Aggregate Functions

Combining INNER JOIN with aggregate functions like COUNT, SUM, or AVG allows you to perform calculations on joined data. This is powerful for generating reports and analytics, such as calculating total sales per customer or counting orders by region. Use GROUP BY to organize results by specific columns when using aggregates with joins.

-- Calculate total spending per customer
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS average_order
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC;

🔹 INNER JOIN with USING Clause

When the join columns have the same name in both tables, you can use the USING clause as a shorthand instead of ON. This makes the query more concise and readable. The USING clause automatically matches columns with identical names, reducing redundancy in your SQL code and making the join condition clearer when column names are consistent across tables.

-- If both tables have 'customer_id' column
SELECT 
    c.customer_name,
    o.order_id,
    o.order_date
FROM customers c
INNER JOIN orders o USING (customer_id);

-- Equivalent to:
-- INNER JOIN orders o ON c.customer_id = o.customer_id

🔹 INNER JOIN with Multiple Conditions

You can specify multiple conditions in the ON clause using AND or OR operators. This allows for more complex join logic where rows must meet multiple criteria to be matched. This is useful when relationships between tables depend on more than one column, such as matching on both ID and date, or when you need composite key joins.

-- Join with multiple conditions
SELECT 
    e.employee_name,
    d.department_name,
    p.project_name
FROM employees e
INNER JOIN departments d 
    ON e.department_id = d.department_id
    AND e.status = 'active'
INNER JOIN projects p 
    ON e.employee_id = p.employee_id
    AND p.status = 'ongoing';

🔹 INNER JOIN Performance Tips

To optimize INNER JOIN performance, ensure join columns are indexed, especially foreign keys. Use table aliases for readability, select only needed columns instead of SELECT *, and filter early with WHERE clauses. Consider the order of joins in complex queries, as joining smaller result sets first can improve performance significantly in large databases.

-- Optimized INNER JOIN query
-- 1. Use indexes on join columns
-- 2. Select only needed columns
-- 3. Filter early with WHERE
SELECT 
    c.customer_name,
    o.order_id,
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active'
  AND o.order_date >= '2024-01-01'
LIMIT 100;