How to perform an inner join in PostgreSQL

· 469 words · 3 minute read

How to perform an inner join in PostgreSQL 🔗

INNER JOIN is a fundamental concept in SQL that allows you to combine data from two or more tables based on a related column between them. In this tutorial, we’ll cover the basics of INNER JOIN, its syntax, and examples to help you master this essential skill.

What is an INNER JOIN? 🔗

An INNER JOIN is a type of SQL join that returns only the rows that have matching values in both tables. This type of join is useful when you want to retrieve data from multiple tables where there is a matching condition.

Syntax 🔗

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Understanding the Syntax 🔗

  • SELECT column_name(s): This clause specifies the columns you want to retrieve from the joined tables.
  • FROM table1: This clause specifies the first table you want to join.
  • INNER JOIN table2: This clause specifies the second table you want to join.
  • ON table1.column_name = table2.column_name: This clause specifies the condition for joining the two tables.

Example: Combining Customer and Order Data 🔗

Let’s say we have two tables: customers and orders.

customers table:

cust_id name
1 John
2 Mary
3 David

orders table:

order_id cust_id order_date
1 1 2022-01-01
2 1 2022-01-15
3 2 2022-02-01
4 3 2022-03-01

We want to retrieve the customer names and their corresponding order dates. We can use an inner join to combine the two tables based on the cust_id column.

SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders
ON customers.cust_id = orders.cust_id;

Result:

name order_date
John 2022-01-01
John 2022-01-15
Mary 2022-02-01
David 2022-03-01

Exercises 🔗

Try solving the following exercises to practice your skills:

  1. Retrieve the customer names and their corresponding order totals from the customers and orders tables.
  2. Retrieve the product names and their corresponding supplier names from the products and suppliers tables.
  3. Retrieve the employee names and their corresponding department names from the employees and departments tables.

Solutions

Check your answers against the following solutions:

SELECT customers.name, SUM(orders.total) AS total_orders
FROM customers
INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.name;

SELECT products.name, suppliers.name
FROM products
INNER JOIN suppliers
ON products.supplier_id = suppliers.supplier_id;

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.dept_id = departments.dept_id;

Bonus: Use Table Aliases with Inner Join Query 🔗

You can use aliases to shorten the table names and make the query more readable. In the query below, c is used as an alias for the customers table, and o is used as an alias for the orders table.

SELECT c.name, o.order_date
FROM customers c
INNER JOIN orders o
ON c.cust_id = o.cust_id;

Conclusion 🔗

In this tutorial, we’ve covered the basics of INNER JOIN, its syntax, and examples to help you master this essential skill. Practice the exercises and experiment with different join scenarios to solidify your understanding of INNER JOIN.

PostgreSQL