Table Joins (1) — INNER JOIN and Aliases

The data used in this article — department and employee

So far you've worked with a single table at a time, but real-world data is split across multiple tables. Employee names and salaries live in the employee table, department names and locations live in the department table — the usual design splits data by role and links them together with employee.dept_id pointing at department (this "column that points to a row in another table" is called a foreign key).

In this article you'll learn INNER JOIN, the most basic form of a JOIN that combines two separate tables into one result. The material is the department table (6 departments) and the employee table (30 employees). Through an exercise that builds a list showing each employee's department name, you'll try table aliases, ON, and USING in turn.

Before diving into the exercises, take a look at the column definitions and sample data of the two tables this article uses — department and employee.

① Run PRAGMA table_info(department); and PRAGMA table_info(employee); to check the column definitions of both tables.

② Run SELECT * FROM department LIMIT 5; and SELECT * FROM employee LIMIT 5; to preview the first 5 rows. Notice that some rows in employee have NULL in the dept_id column.

INNER JOIN — connecting two tables with a join condition

The employee table only holds dept_id (the department number). When you want the employee list to also show the department name, you take the rows where employee.dept_id and department.dept_id match, line them up side by side, and merge them into one row. This is a JOIN, and the most basic one is INNER JOIN (INNER = inner join).

The form is SELECT cols FROM left_table JOIN right_table ON join_condition. JOIN is short for INNER JOIN — both behave identically. In ON you write the join condition, which says "if which column matches which column, treat these as the same row." INNER JOIN keeps only the rows that matched in both tables; rows that have no counterpart on one side drop out of the result.

-- Show dept_name alongside each employee (INNER JOIN)
SELECT employee.name, employee.salary, department.dept_name
FROM employee
JOIN department
  ON employee.dept_id = department.dept_id;

Imagine a requirement: "build a table for the employee list with employee number, name, and the name of the department they belong to." (Run it correctly and the explanation will appear.)

① INNER JOIN the employee table and the department table. The join condition is that dept_id matches in both tables.

② Pull three columns: emp_id and name from employee, and dept_name from department.

③ Order the result by emp_id ascending.

Table aliases — writing long table names short

Writing the table name every time, as in employee.name or department.dept_name, makes queries long. If you put an alias after the table name, as in FROM employee e, you can refer to it with a short name like e.name from then on. Writing it with AS, as FROM employee AS e, means the same thing.

Aliases also become necessary for a self-join (using the same table twice, covered in the next article) and for naming a result in a subquery. In this article we'll write employee as e and department as d.

-- Alias employee as e and department as d
SELECT e.name, e.city, d.dept_name, d.location
FROM employee e
JOIN department d
  ON e.dept_id = d.dept_id;

Imagine a requirement: "display only the names of employees in the Engineering department, along with that department's location."

① INNER JOIN employee with the alias e and department with the alias d. The join condition is that dept_id matches.

② Narrow to rows where dept_name is Engineering.

③ Pull two columns — name from e and location from d — ordered by name ascending.

USING — a shorthand when the join column names match

employee and department are joined on the same column name, `dept_id`, in both tables. When the name of the column used for the join is exactly the same in both tables like this, you can write USING (dept_id) instead of ON e.dept_id = d.dept_id.

USING (column_name) means the same as ON left.column_name = right.column_name, and it's shorter because you only write the join column once. On top of that, a column joined with USING appears only once in the result, and you can reference it directly with SELECT dept_id (no table name). When the column names differ (for example, e.dept_id and d.id), you can't use USING — you use ON.

-- Write the same meaning as ON e.dept_id = d.dept_id with USING
SELECT e.name, dept_id, d.dept_name
FROM employee e
JOIN department d
  USING (dept_id);

Imagine a requirement: "out of all employees, pull the top 5 by salary and list their name, department name, and salary."

① Join employee (alias e) and department (alias d) with the `USING` clause. The join column is dept_id, which is common to both tables.

② Pull three columns: name from e, dept_name from d, and salary from e.

③ Order by salary descending and narrow to the first 5 rows.

INNER JOIN drops rows that exist on only one side

INNER JOIN returns only the rows where the join condition matched in both tables. The 4 people whose dept_id is NULL (Ivan, Quinn, Xander, Brian) and the Legal department with no employees at all have no join partner, so they don't appear in the result. "I want all employees but only 26 rows come back" is, in most cases, caused by this behavior. When you want to keep rows that exist on only one side too, use the OUTER JOIN covered in the next article.