Oracle

Chapter 7 - DQL (Data Query Language)

INNER JOIN

The INNER JOIN clause in SQL is used to combine rows from two or more tables based on a related column between them. This operation is essential for querying data across different tables while ensuring only the matching records are returned. For beginners, understanding how to use INNER JOIN effectively is crucial for database management and querying.

Here’s a basic example using tables related to authors and books:

SELECT a.author_id, a.name, b.title
FROM authors a
INNER JOIN books b ON a.author_id = b.author_id;

Key Points about INNER JOIN

Definition and Purpose

Combines rows from two or more tables.
Returns only the rows with matching values in the specified columns.

Syntax

The basic syntax of an INNER JOIN is as follows:

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

Example Explained

In the example above:

a and b are aliases for the authors and books tables, respectively.
The join condition is defined using ON, specifying the related columns (author_id).

Multiple Joins

You can join multiple tables:

SELECT a.name, b.title, m.membership_type
FROM authors a
INNER JOIN books b ON a.author_id = b.author_id
INNER JOIN rentals r ON b.book_id = r.book_id
INNER JOIN membership m ON r.member_id = m.member_id;

Best Practices

Always use table aliases for better readability.
Ensure the join conditions are correct to avoid Cartesian products.
Select only the necessary columns to optimize performance.

By understanding and practicing INNER JOIN, beginners can efficiently retrieve related data from multiple tables, enhancing their SQL querying skills.

TEST CODE

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.

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;

Utilize an INNER JOIN in conjunction with GROUP BY to calculate the total salaries of all employees within each department.

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;

Retrieve information from 3 tables by employing multiple INNER JOIN operations. List the order number, product names, and the quantity ordered.

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;

Retrieve data from 4 tables through the use of multiple INNER JOIN operations. Present details such as the order number, sales agent, client name, and the quantity of products ordered within a specific order.

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;

Retrieve information from 5 tables by implementing multiple INNER JOIN operations. Obtain details such as the order number, sales agent, product type, product name, and the quantity of product units ordered.

SELECT 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;

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 client, product type, product name, and the subtotal for each product.

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;

Avoid employing this coding style, as it may produce the desired result but will make the code challenging to comprehend and troubleshoot for bugs. Follow the recommended INNER JOIN syntax from this lesson, where each INNER JOIN is placed on its own line.

SELECT a.order_number,
e.first_name AS sales_agent,
f.first_name AS client,
d.product_type,
c.product_name,
b.quantity AS product_quantity
FROM act_order a, act_order_detail b, prd_product c, prd_product_type d, org_employee e, org_client f
WHERE b.order_id = a.order_id
AND c.product_id = b.product_id
AND d.product_type_id = c.product_type_id
AND e.employee_id = a.sales_agent_employee_id
AND f.client_id = a.client_id
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.