PostgreSQL - INNER JOIN

The INNER JOIN in PostgreSQL returns only the rows that have matching values in both tables based on a common column.

• Retrieve related information from multiple tables.
• Filter out rows without matching values.
• Produce combined results for analysis and reporting.

Syntax

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

Where:

• table1: The left table whose all rows are returned.
• table2: The right table that provides matching rows.
• matching_column: The common column used to join the tables.

Example

Firstly, create the Authors and Books tables and insert some records.

Authors Table

Books Table

The following query retrieves book titles along with their corresponding author names.

Query:

SELECT
    b.BookTitle,
    a.AuthorName
FROM Books b
INNER JOIN Authors a
ON b.AuthorID = a.AuthorID;

Output:

• The INNER JOIN matches rows where AuthorID exists in both the Books and Authors tables.
• The book Python Basics is excluded because AuthorID = 105 does not exist in the Authors table.

Example: INNER JOIN with WHERE Clause

The following query retrieves books written by authors from the USA.

Query:

SELECT
    b.BookTitle,
    a.AuthorName,
    a.Country
FROM Books b
INNER JOIN Authors a
ON b.AuthorID = a.AuthorID
WHERE a.Country = 'USA';

Output: