Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL INNER JOIN

The `INNER JOIN` in PostgreSQL is used to retrieve records that have matching values in both tables involved in the join. It is one of the most common operations for combining rows from two or more tables based on a related column.

Usage

The `INNER JOIN` is used when you need to select rows that have corresponding values in another table. This operation is essential for combining data stored across tables with a common key.


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

In this syntax, `INNER JOIN` is used to connect `table1` and `table2` on the basis of a common column. If no matches are found between the tables, no rows will be returned.

Examples

1. Basic INNER JOIN


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

This example retrieves the names of customers and their corresponding order IDs by joining the `customers` and `orders` tables on the `customer_id` column.

2. INNER JOIN with Additional Conditions


SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
WHERE departments.location = 'New York';

Here, the `INNER JOIN` is used alongside a `WHERE` clause to filter the results to only include employees working in departments located in New York.

3. INNER JOIN with Multiple Tables


SELECT students.name, courses.course_name, instructors.instructor_name
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.course_id
INNER JOIN instructors ON courses.instructor_id = instructors.instructor_id;

This example shows how to use multiple `INNER JOIN` clauses to pull data from four tables: `students`, `enrollments`, `courses`, and `instructors`.

Tips and Best Practices

  • Use `INNER JOIN` for matching data only. It is ideal when you need rows that exist in both tables.
  • Ensure indexing on join columns. Index the columns used in the `ON` condition to improve query performance.
  • Use aliases for readability. Simplify complex queries by using table aliases for better clarity. For example: 
    
    SELECT c.name, o.order_id
    FROM customers AS c
    INNER JOIN orders AS o ON c.customer_id = o.customer_id;
  • Combine with other clauses. Enhance filtering and data manipulation by using `INNER JOIN` with `WHERE`, `GROUP BY`, and `ORDER BY` clauses.
  • Check for nulls. Remember that `INNER JOIN` excludes rows with nulls in the join column, which may lead to missing data if not handled properly. Consider using `COALESCE` or similar functions to handle potential null values in join columns.