INNER JOIN

In practice, the INNER JOIN is the most common join type used. Luckily, it’s also the most straightforward join type, returning records with matching values in both left and right, tables. Like other join types, the ON clause is used to specify what qualifies as “matching values.” It’s responsible for evaluating whether or not a value for an attribute or particular set of attributes can be found within a record or set of records within both tables. If this seems confusing, please consider checking out my introduction to JOINS page before continuing onto the examples below.

The data used throughout the examples on this page and captured in the two tables below can be found at livesql.oracle.com. The same is true for the customers and orders tables used later in this discussion.

Employees
The employees table contains one row per employee.

Departments
The departments table contains one row per department.

Real-world Problem 1
Human resources wishes to determine the number of employees within each department. They’ve reached out to you to generate a report containing this information. Because memorizing the department IDs is not the best use of their time, they’ve asked the report to contain the actual department name instead of the ID associated with the department. Additionally, the human resources department and associated employee count should be excluded from the report. For ease of use the report should be sorted by the number of employees, descending.

Below is the code required to meet this problem’s reporting requirements and the corresponding result set returned. Notice the INNER JOIN criteria specified in the ON clause requires the department ID value represented in an employee record to match a department ID value in a department record. If a match in both tables is found, then the record is returned and the data from each table represented within a single row. Additionally, take note that the condition in the WHERE clause to exclude the human resources department from the result set could instead be placed as an additional predicate/filter in the ON clause. Use caution when doing so as results can differ when other join types are used.

-- Retrieve the number of employees per department, excluding the HR department.
SELECT
	COUNT(hr.employees.department_id) AS employee_count,
	hr.departments.department_name
FROM
	hr.employees
	INNER JOIN
	hr.departments
		ON hr.employees.department_id = hr.departments.department_id
WHERE
    hr.departments.department_name != 'Human Resources'
GROUP BY
	hr.departments.department_name
ORDER BY
	employee_count DESC;

Before moving onto the next real-world problem, first consider the two tables below.

Customers
The customers table contains one row per customer.

Orders
The orders table contains one row per order submitted by a customer.

Real-world Problem 2
Management is attempting to understand and identify the company’s best customers. As an initial and very straightforward way of thinking about and identifying the best customers, they wish to see a list of the company’s customers and the total sales made to each. An additional requirement is for the list to be sorted by total sales in descending order (i.e., greatest amount of sales to least).

Below is the code required to meet this problem’s reporting requirements and the corresponding result set returned. Like the ON clause used in the first problem, here the goal is to match up the customer and order records based on customer ID values in each table. Using an INNER JOIN with this ON clause not only ensures each order is associated with the correct customer, but also acts as a filtering mechanism by which only customers having placed at least one order are included in the result set. Again, this is because the customer ID must exist in both tables for the ON clause’s condition to ever evaluate to true.

SELECT
	(oe.customers.cust_last_name
    	|| ', '
		|| oe.customers.cust_first_name
    ) AS customer_name,
    SUM(oe.orders.order_total) AS total_sales
FROM
	oe.customers
	INNER JOIN
	oe.orders
		ON oe.customers.customer_id = oe.orders.customer_id
GROUP BY
	oe.customers.cust_last_name,
    oe.customers.cust_first_name
ORDER BY
	total_sales DESC;

Knowledge Check
If you’d like to confirm your understanding on JOINS and other fundamental concepts, consider solving a handful of my practice problems here.