PostgreSQL

Chapter 7 - DQL (Data Query Language)

INNER JOIN

Introduction to INNER JOIN in PostgreSQL (DQL)

In PostgreSQL, INNER JOIN is a type of join used in SQL to retrieve records that have matching values in both tables involved in the join. When performing an INNER JOIN, only rows that have matching values in both tables are returned. This is useful when you want to extract data that is related across multiple tables.

For example, consider a banking system with tables like customers, accounts, and transactions. Using INNER JOIN, you could combine data from the customers and accounts tables to get information about the customers who hold specific accounts.

Step-by-Step: How to use INNER JOIN in PostgreSQL

Basic Syntax

The basic syntax of an INNER JOIN query is as follows:

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

In this syntax:

table1 and table2 are the tables you are joining.
The ON clause specifies the condition that needs to be met (usually matching columns from both tables).

Example with Customers and Accounts
Let’s say we have two tables:
- customers: This table contains customer information (customer_id, customer_name, and other details).
- accounts: This table contains account information (account_id, customer_id, account_balance).
Now, you want to retrieve a list of customer names and their account balances. Here’s how you can do that using an INNER JOIN:
```
SELECT c.customer_name, a.account_balance
FROM customers c
INNER JOIN accounts a
ON c.customer_id = a.customer_id;
```

The query will return only the customers who have a matching account in the accounts table.
c and a are table aliases used to shorten the query for readability.

Joining More than Two Tables
You can also perform INNER JOIN with more than two tables. For example, if you want to join the customers, accounts, and transactions tables to get the customer name, account balance, and recent transactions, you can extend the INNER JOIN like this:
```
SELECT c.customer_name, a.account_balance, t.transaction_amount
FROM customers c
INNER JOIN accounts a ON c.customer_id = a.customer_id
INNER JOIN transactions t ON a.account_id = t.account_id;
```

This query combines data from all three tables and will return only the rows where there is a match across all the tables.

Filtering Results with WHERE Clause
You can also add a WHERE clause to filter the results of the join. For example, to get customers with an account balance above 10,000:
```
SELECT c.customer_name, a.account_balance
FROM customers c
INNER JOIN accounts a
ON c.customer_id = a.customer_id
WHERE a.account_balance > 10000;
```

This will return the names of customers who have more than $10,000 in their accounts.

Ordering the Results

If you want to order the results, for example, by account balance in descending order, you can add an ORDER BY clause:

SELECT c.customer_name, a.account_balance
FROM customers c
INNER JOIN accounts a
ON c.customer_id = a.customer_id
ORDER BY a.account_balance DESC;

This will display the customer names along with their account balances, sorted from highest to lowest balance.

Summary

INNER JOIN is used to retrieve rows from multiple tables where the join condition is met.
It returns only the rows with matching data in both tables.
You can use additional clauses like WHERE and ORDER BY to filter and sort the results as needed.

This guide introduces you to the basics of INNER JOIN in PostgreSQL, giving you a foundation to start querying data from multiple related tables.

Tansy PostgreSQL Course | INNER JOIN | Chapter 7 | Lesson 24 - Video Thumbnail

TEST CODE

To perform a basic INNER JOIN to obtain the product name, product code, and product type by combining the product table with the product type table, use the following query:

SELECT prd_product.product_code,
prd_product.product_name,
prd_product_type.product_type
FROM prd_product
INNER JOIN prd_product_type
ON prd_product_type.product_type_id = prd_product.product_type_id;

In PostgreSQL, to calculate the total salaries of all employees within each department using an INNER JOIN in conjunction with GROUP BY, you can use the following query:

SELECT org_department.department,
SUM(org_employee.salary) AS total_salary
FROM org_department
INNER JOIN org_employee ON org_employee.department_id = org_department.department_id
GROUP BY org_department.department;

In PostgreSQL, to retrieve information from 3 tables by employing multiple INNER JOIN operations, and list the order number, product names, and the quantity ordered, you can use the following query:

SELECT act_order.order_number,
prd_product.product_name,
act_order_detail.quantity AS product_quantity
FROM act_order
INNER JOIN act_order_detail ON act_order_detail.order_id = act_order.order_id
INNER JOIN prd_product ON prd_product.product_id = act_order_detail.product_id
ORDER BY act_order.order_number, prd_product.product_name;

In PostgreSQL, to retrieve data from 4 tables using multiple INNER JOIN operations and present details such as the order number, sales agent, client name, and the quantity of products ordered within a specific order, you can use the following query:

SELECT a.order_number,
d.first_name AS sales_agent,
c.first_name AS client_name,
COUNT(b.product_id) AS products_ordered
FROM act_order a
INNER JOIN act_order_detail b ON b.order_id = a.order_id
INNER JOIN org_client c ON c.client_id = a.client_id
INNER JOIN org_employee d ON d.employee_id = a.sales_agent_employee_id
GROUP BY a.order_number, d.first_name, c.first_name
ORDER BY COUNT(b.product_id) DESC;

In PostgreSQL, to retrieve information from 5 tables by implementing multiple INNER JOIN operations and obtain details such as the order number, sales agent, product type, product name, and the quantity of product units ordered, you can use the following query:

SELECT act_order.order_number,
org_employee.first_name AS sales_agent,
prd_product_type.product_type,
prd_product.product_name,
act_order_detail.quantity AS product_quantity
FROM act_order
INNER JOIN act_order_detail ON act_order_detail.order_id = act_order.order_id
INNER JOIN prd_product ON prd_product.product_id = act_order_detail.product_id
INNER JOIN prd_product_type ON prd_product_type.product_type_id = prd_product.product_type_id
INNER JOIN org_employee ON org_employee.employee_id = act_order.sales_agent_employee_id
ORDER BY act_order.order_number, prd_product.product_name;

In PostgreSQL, to retrieve detailed information from five different tables by implementing multiple INNER JOIN operations and to obtain details such as the order number, sales agent, client name, product type, product name, and the quantity of product units ordered, you can use the following SQL query.

SELECT act_order.order_number,
org_employee.first_name AS sales_agent,
org_client.first_name AS client,
prd_product_type.product_type,
prd_product.product_name,
SUM(act_order_detail.unit_rate * act_order_detail.quantity) AS product_sub_total
FROM act_order
INNER JOIN act_order_detail ON act_order_detail.order_id = act_order.order_id
INNER JOIN prd_product ON prd_product.product_id = act_order_detail.product_id
INNER JOIN prd_product_type ON prd_product_type.product_type_id = prd_product.product_type_id
INNER JOIN org_employee ON org_employee.employee_id = act_order.sales_agent_employee_id
INNER JOIN org_client ON org_client.client_id = act_order.client_id
WHERE org_client.gender = 'F'
GROUP BY act_order.order_number, org_employee.first_name, org_client.first_name, prd_product_type.product_type, prd_product.product_name
HAVING SUM(act_order_detail.unit_rate * act_order_detail.quantity) >= 5
ORDER BY act_order.order_number, prd_product.product_name;

In PostgreSQL, to retrieve data from 6 tables using the recommended INNER JOIN syntax with each JOIN on its own line, and include multiple WHERE conditions, GROUP BY, HAVING clause, ORDER BY, SUM, and arithmetic multiplication operations, you can use the following query:

SELECT a.order_number,
e.first_name AS sales_agent,
f.first_name AS client,
d.product_type,
c.product_name,
SUM(b.unit_rate * b.quantity) AS product_sub_total
FROM act_order a
INNER JOIN act_order_detail b ON b.order_id = a.order_id
INNER JOIN prd_product c ON c.product_id = b.product_id
INNER JOIN prd_product_type d ON d.product_type_id = c.product_type_id
INNER JOIN org_employee e ON e.employee_id = a.sales_agent_employee_id
INNER JOIN org_client f ON f.client_id = a.client_id
WHERE f.gender = 'F'
GROUP BY a.order_number, e.first_name, f.first_name, d.product_type, c.product_name
HAVING SUM(b.unit_rate * b.quantity) >= 5
ORDER BY a.order_number, c.product_name;

EXAMPLE 1 - SQL INNER JOIN

Here is a simple example of an inner join. In this case, the objective is to extract the product code and product name from the product table and the corresponding product type from the product type table. The data model below illustrates these three columns and the tables involved in this JOIN operation.

EXAMPLE 1 - Tansy Academy Data Model

To accomplish this, you must perform a join between the product table and the product type table using the primary key and foreign column, which, in this case, is the product type ID column.

EXAMPLE 1 - INNER JOIN query

SELECT prd_product.product_code,
prd_product.product_name,prd_product_type.product_type
FROM prd_product
INNER JOIN prd_product_type ON prd_product_type.product_type_id = prd_product.product_type_id;

EXAMPLE 1 - Query Data Mapping

In this image, a yellow background signifies a match between the primary key and foreign key, while a red background indicates non-matching product type IDs. The green-colored background denotes the data selected for the final output. Please note that in the case of an INNER JOIN, the value in the join column must exist in BOTH TABLES.

EXAMPLE 1 - Final OUTput

EXAMPLE 2 - COMPLEX INNER JOIN

Extract data from 6 tables using multiple WHERE conditions, INNER JOINs, GROUP BY, HAVING clause, ORDER BY, SUM, and arithmetic multiplication operations. Obtain details including the order number, sales agent, female clients, product type, product name, and the subtotal for each product.

EXAMPLE 2 - Tansy Academy Data Model

To achieve this, you need to execute joins across all six tables, and it's important to note that the join between each pair of tables differs from the others. We will approach this query incrementally, taking one step at a time, with each step involving a join between two tables.

EXAMPLE 2 - STEP 1

Join order and order detail tables. In this image, a yellow background signifies a match between the primary key and foreign key, while a red background indicates non-matching product type IDs. The green-colored background denotes the data selected for the final output.

EXAMPLE 2 - STEP 1 OUTPUT

EXAMPLE 2 - STEP 2

Join with client table, in this image, a yellow background signifies a match between the primary key and foreign key, while a red background indicates non-matching product type IDs. The green-colored background denotes the data selected for the final output.

EXAMPLE 2 - STEP 2 OUTPUT

EXAMPLE 2 - STEP 3

Join with employee table, in this image, a yellow background signifies a match between the primary key and foreign key, while a red background indicates non-matching product type IDs. The green-colored background denotes the data selected for the final output.

EXAMPLE 2 - STEP 3 OUTPUT

EXAMPLE 2 - STEP 4

Join with product table, in this image, a yellow background signifies a match between the primary key and foreign key, while a red background indicates non-matching product type IDs. The green-colored background denotes the data selected for the final output.

EXAMPLE 2 - STEP 4 OUTPUT

EXAMPLE 2 - STEP 5

Join with product type table, in this image, a yellow background signifies a match between the primary key and foreign key, while a red background indicates non-matching product type IDs. The green-colored background denotes the data selected for the final output.