TutorialsArena

PostgreSQL: Using INNER JOIN to Combine Data from Tables

Learn how to use INNER JOIN in PostgreSQL to combine rows from related tables based on a common column. Examples and explanations provided.

PostgreSQL INNER JOIN

What is the PostgreSQL INNER JOIN Clause?

In relational databases, data is often spread across multiple tables. The INNER JOIN clause combines rows from two or more tables based on a related column. It only returns rows where the join condition is met.

In PostgreSQL, INNER JOIN is the default join type; you don't always need to explicitly write the "INNER" keyword.

PostgreSQL INNER JOIN Syntax

There are several ways to write an INNER JOIN:

Using the ON Clause

Syntax with ON

SELECT [column_list | *]
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Using the USING Clause (for columns with the same name)

Syntax with USING

SELECT [column_list | *]
FROM table1
INNER JOIN table2
USING (column_name);

Using the WHERE Clause (less preferred)

Syntax with WHERE (less efficient)

SELECT [column_list | *]
FROM table1, table2
WHERE table1.column_name = table2.column_name;

The ON or USING clauses are generally preferred for clarity and efficiency.

Example: Joining Two Tables

Let's create two tables, Employee and department, and join them using an INNER JOIN.

Creating and Populating Tables

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    emp_fname VARCHAR NOT NULL,
    emp_lname VARCHAR NOT NULL,
    location VARCHAR(30)
);

CREATE TABLE department (
    emp_id INT PRIMARY KEY,
    dept_id INT NOT NULL,
    dept_name VARCHAR NOT NULL
);

INSERT INTO Employee (emp_id, emp_fname, emp_lname, location) VALUES
(1, 'John', 'Smith', 'New York'),
(2, 'Mia', 'Clark','Florida'),
(3, 'Noah','Rodriguez','Chicago'),
(4, 'Ava','Gracia','Houston'),
(5,'James','Luther','Los Angeles');

INSERT INTO department (emp_id, dept_id, dept_name) VALUES
(1, 1, 'ACCOUNTING'),
(2, 2,'SALES'),
(3, 3,'RESEARCH'),
(4, 4,'OPERATIONS'),
(5, 5,'HUMAN RESOURCES');

SELECT emp_fname, emp_lname, location, dept_name
FROM Employee
INNER JOIN department
ON Employee.emp_id = department.emp_id;

Table Aliasing

To make queries more readable, especially when joining tables with similarly named columns, use aliases:

Using Table Aliases

SELECT e.emp_id, emp_fname, emp_lname, location, dept_name
FROM Employee e
INNER JOIN department d
ON e.emp_id = d.emp_id;

Joining with USING

When columns have the same name, the USING clause simplifies the join condition:

Using USING

SELECT emp_id, emp_fname, location, dept_name
FROM Employee
INNER JOIN department
USING (emp_id);

Joining with WHERE

You can also filter results with a WHERE clause:

Using WHERE

SELECT emp_fname, dept_id, dept_name, location
FROM Employee
INNER JOIN department
USING (emp_id)
WHERE dept_name = 'SALES';

Joining Three Tables

To join more than two tables, chain INNER JOIN clauses:

Joining Three Tables

CREATE TABLE Jobs (
    job_id INT PRIMARY KEY,
    job_description VARCHAR NOT NULL
);

INSERT INTO Jobs (job_id, job_description) VALUES
(1, 'Training'),
(2, 'Management'),
(3, 'Executive'),
(4, 'Non-Executive');

SELECT emp_id, emp_fname, dept_name, location, job_description
FROM Employee
INNER JOIN department USING (emp_id)
INNER JOIN Jobs
ON department.emp_id = jobs.job_id
ORDER BY emp_id;

Using Operators

You can use various comparison operators (=, !=, >, <, etc.) in the join condition:

Using Operators

SELECT emp_fname, emp_lname, location, dept_name
FROM Employee
INNER JOIN department
ON Employee.emp_id = department.emp_id
WHERE dept_name != 'SALES';

Overview

This section demonstrated various ways to perform INNER JOIN operations in PostgreSQL, including using the ON and USING clauses, table aliasing, joining multiple tables, and incorporating WHERE clauses for filtering.