MySQL

In MySQL, the INNER JOIN is one of the most common types of joins used to retrieve data from two or more tables based on a related column between them. When you perform an INNER JOIN, it returns only the rows where there is a match in both tables. If a row in one table does not have a matching row in the other table, that row will not be included in the result set. This is useful when you want to combine data from multiple tables and only focus on records that have relationships.

Here’s a detailed explanation of how to use INNER JOIN, along with examples for new students:

1. Basic Syntax of INNER JOIN:

   • The INNER JOIN combines rows from two tables where there is a match in the columns being joined.
   • Syntax:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees e 
   INNER JOIN departments d 
   ON e.department_id = d.department_id;

   • This query retrieves the first name, last name of employees, and the corresponding department name where the department_id matches in both the employees and departments tables.

2. Using Aliases with INNER JOIN:

   • Table aliases (e for employees and d for departments) are often used to simplify queries and make them easier to read.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees AS e 
   INNER JOIN departments AS d 
   ON e.department_id = d.department_id;

   • This query is identical to the previous one but uses the AS keyword for aliasing.

3. Filtering Results with INNER JOIN and WHERE:

   • You can combine INNER JOIN with a WHERE clause to filter the result set further.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees e 
   INNER JOIN departments d 
   ON e.department_id = d.department_id 
   WHERE d.department_name = 'HR';

   • This query retrieves employees who belong to the "HR" department.

4. Joining Multiple Tables with INNER JOIN:

   • You can perform INNER JOIN on more than two tables to retrieve data from multiple related tables.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name, b.branch_name 
   FROM employees e 
   INNER JOIN departments d 
   ON e.department_id = d.department_id 
   INNER JOIN branches b 
   ON d.branch_id = b.branch_id;

   • This query joins three tables: employees, departments, and branches to retrieve employee names, department names, and branch names.

5. Using INNER JOIN with Aggregate Functions:

   • You can use INNER JOIN with aggregate functions such as COUNT(), SUM(), and AVG() to analyze data across related tables.
   • Example:

   SELECT d.department_name, COUNT(e.employee_id) AS employee_count 
   FROM employees e 
   INNER JOIN departments d 
   ON e.department_id = d.department_id 
   GROUP BY d.department_name;

   • This query returns the number of employees in each department.

6. Handling NULL Values with INNER JOIN:

   • The INNER JOIN excludes rows with NULL values in the join columns since there is no match. If you need to include rows with NULL values, consider using a different type of join, such as LEFT JOIN.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees e 
   INNER JOIN departments d 
   ON e.department_id = d.department_id 
   WHERE e.manager_id IS NOT NULL;

   • This query returns employees with a non-NULLmanager_id who belong to departments.

7. Performance Considerations:

   • Using INNER JOIN on large datasets can affect performance, especially if the join columns are not indexed. Indexing the columns used in the ON clause can significantly improve query performance.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name 
   FROM employees e 
   INNER JOIN departments d 
   ON e.department_id = d.department_id 
   WHERE e.salary > 50000;

   • If the department_id column is indexed, this query will run more efficiently.

8. Combining INNER JOIN with Other Joins:

   • You can combine INNER JOIN with other types of joins, such as LEFT JOIN or RIGHT JOIN, to retrieve more complex datasets.
   • Example:

   SELECT e.first_name, e.last_name, d.department_name, b.branch_name 
   FROM employees e 
   INNER JOIN departments d 
   ON e.department_id = d.department_id 
   LEFT JOIN branches b 
   ON d.branch_id = b.branch_id;

   • This query retrieves all employees and departments, and for each department, the corresponding branch name is included if available.

INNER JOIN is a powerful tool in SQL for combining data from related tables based on matching values in specified columns. It’s essential for querying relational databases and helps ensure that only matching rows from each table are included in the result set.

To gain complete access, login with gmail or outlook, no need of signup, click here

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;

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;

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
;

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;

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
;

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;

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.

EXAMPLE 2 - STEP 5 OUTPUT

EXAMPLE 2 - STEP 6

Apply WHERE condition filter, pick female clients only.

EXAMPLE 2 - STEP 6 OUTPUT

EXAMPLE 2 - STEP 7

Multiply unit rate and quantity to arrive at sub total

EXAMPLE 2 - STEP 7 OUTPUT

EXAMPLE 2 - STEP 8

Apply HAVING clause condition, here in the explanation we are avoiding GROUP BY step as it would yeild same results

EXAMPLE 2 - STEP 8 OUTPUT