JOINS

If you’re reading this, it’s likely that you’ve acquired an understanding of basic SELECT and FROM clauses. It’s also likely upon doing so, you have uncovered a hard truth: to solve many real-world problems, sourcing of data from multiple tables is required. This is where the JOIN clause comes into play.

The JOIN clause allows you to combine rows from two or more tables based on one or more related columns within the tables. Within this clause, instructions for combining the rows from the respective tables is required. These instructions will be specified within an ON clause by providing the table names, related column, and condition(s) required. It’s important to note there are six join types: CROSS, FULL, LEFT, INNER, RIGHT, and SELF. When the type is not explicitly mentioned, it is understood to be an INNER JOIN. For the purposes of this discussion and to first examine the most commonly used join type, we’ll consider the default join type, INNER and use the JOIN keyword alone. I’ve created separate pages dedicated to discussing each join type. To jump to those dedicated pages, use the links below or expand the JOINs section of the SQL Fundamentals sub-menu and choose the desired page.

CROSS JOIN
FULL JOIN
LEFT JOIN
INNER JOIN
RIGHT JOIN
SELF JOIN

JOINs can be confusing without a concrete example including data, visuals, and a real-world problem, so let’s introduce those before continuing further.

Example Data
In our example, there exists two tables, employees and jobs. The employees table contains one row per employee and within each row, data associated with the employee is stored. Similarly, the jobs table contains one row per job and within each row, data associated with the job is stored. For clarity, I’ve provided each table’s structure and example data below. The schema, corresponding tables, and data can be found at livesql.oracle.com.

Employees

Jobs

Real-world Problem
Build a report containing each employee’s ID, job ID, job title, salary, and the minimum and maximum salary associated with the employee’s job.

Let’s now return to developing an understanding on JOINs and how this concept will help you solve the real-world problem above.

JOINs Continued
Based on the example data above, the columns required in the report are spread out across two tables, employees and jobs. These tables have a related column, job ID. This is not a coincidence. The tables have been purposely designed this way to minimize redundancy and permit the lookup of each employee’s job ID and data associated with each job within the jobs table. This is known as a primary key–foreign key relationship. The job ID serves as a primary key in the jobs table and as a foreign key in the employees table. Additionally, each job ID within the jobs table can be found within zero or more rows in the employees table and each employee’s job ID can be found within exactly one row in the jobs table. The plain English translation is an employee is assigned one and only one job and a job is assigned to zero or more employees. We’ll take advantage of this relationship in solving the example problem.

Solving the Problem Using JOINs
We’ll combine the two tables’ rows using the JOIN and ON clauses. The basic syntax for the JOIN clause is below.

schema_1.table_1 JOIN schema_2.table_2

By replacing the tables on the left and right side of the JOIN keyword, we have the following:

hr.employees JOIN hr.jobs

The next step is to specify the condition or criteria for combining the rows from each table in the ON clause. The basic syntax for the ON clause is below.

ON schema_1.table_1.column_1 = schema_2.table_2.column_2

For our problem, we are interested in retrieving the details associated with each employee’s job ID value. Therefore, we’ll combine the rows from the employees and jobs tables based on matching job ID values by specifying so in the ON clause.

ON hr.employees.job_id = hr.jobs.job_id

As a result of the joining operation, each employee’s associated job details from the jobs table are accessible within the rows. The code and corresponding output is below.

SELECT
    hr.employees.*,
    hr.jobs.*
FROM
    hr.employees
    JOIN
    hr.jobs
    	ON hr.employees.job_id = hr.jobs.job_id;

You’ll notice that the output includes more columns than are required to meet the reporting requirements. To adhere to the best practice of retrieving the minimal amount of data to solve a problem or address a task, I’ve adjusted the code slightly to retrieve only the data we need. The updated code and output follow.

SELECT
    hr.employees.employee_id,
    hr.employees.job_id,
    hr.jobs.job_title,
    hr.employees.salary,
    hr.jobs.min_salary,
    hr.jobs.max_salary
FROM
    hr.employees
    JOIN
    hr.jobs
    	ON hr.employees.job_id = hr.jobs.job_id;

Breaking Down the Solution
In the SELECT clause, the columns required to meet the reporting requirements are specified. Each of these columns are fully qualified by including the schema and table names (e.g., hr.employees.job_id). This is important as the job ID column is available in both tables. An error will result if the job ID column is included in the SELECT clause without including the name of the table it’s being sourced from. When parsing the query, the RDBMS doesn’t know which job ID column is being referenced without additional information such as the table name and possibly even the schema name in instances where this table and column exist across multiple schemas. The JOIN clause includes the tables of interest. The ON clause specifies to only combine rows from each table where the job ID value in the employee table row matches the job ID value in the jobs table row. In other words, if the employee’s job ID value can be found in the jobs table, then combine the employee’s row with the row in the jobs table where the matching job ID is found.

If you are having trouble following the explanation above and are familiar with the VLOOKUP (vertical lookup) function in excel, then please see the “Microsoft Excel Equivalent Operation” section at the end of this page.

Non-matching Job ID Values
Previously, I mentioned each job ID value in the employees table matches exactly one value in the jobs table, resulting in a guaranteed successful match and combining of employee and job rows. However, if this was not the case and it was possible for an employee to have a job ID value not existing in the jobs table, then the result of the join operation would effectively filter out those employees with non-matching job ID values. The same goes for those jobs stored in rows in the jobs table where the job ID value doesn’t exist in the employees table. That is, no employees are assigned that specific job. This is the intended result of the default join type, INNER JOIN. To modify the behavior of the join operation, you’ll need to become familiar with the other join types. Those join types and links to a discussion on each of them can be found at the beginning of this page.

Microsoft Excel Equivalent Operation
In my experience, teaching users to combine rows from multiple tables using SQL is easier when they’ve performed a similar operation using other programming languages or software. This has been especially true for users familiar with the Microsoft Excel function, VLOOKUP. Learners familiar with this function can largely focus on how to perform the task rather than on understanding the task itself.

In our example task, the employees and jobs tables can be thought of as merely separate worksheets within an excel workbook. To combine the rows in the employees worksheet with those found in the jobs worksheet, a vertical lookup can be performed on the common column, job ID. The table array used in the lookup will be a data range in the jobs worksheet, starting with the job ID and extending to the column of interest, say job title. Provided the correct column index is provided for the job title column in the jobs table, the value returned in each row will be the job title corresponding to the employee’s job ID value. Like the JOIN operation covered above, the job ID value in each row within the employees worksheet is searched for within the jobs worksheet. If the job ID is found, then the values associated with each job ID can be accessed. Otherwise, #N/A is returned, indicating a match could not be found.