English
Menu

Inner Join vs Outer Join: Rows Kept, NULLs and SQL Syntax

A join combines rows of two tables under the condition given in ON (most often that a foreign key equals a primary key) to produce one result table. An inner join keeps only the combinations of rows that have a matching partner; rows without a partner disappear from both tables. An outer join keeps the matching combinations and also keeps the rows of the specified table even when they have no partner, filling the other table's columns with NULL. A left outer join (LEFT OUTER JOIN) keeps every row of the left table, a right outer join (RIGHT OUTER JOIN) keeps the right table, and a full outer join (FULL OUTER JOIN) keeps both. Choose by whether rows without a partner should appear in the result.

AspectInner join (INNER JOIN)Outer join (OUTER JOIN)
Rows kept in the resultOnly combinations of rows that meet the join conditionMatching combinations, plus rows of the table chosen to be kept that have no matching partner
Unmatched rowsRemoved from the result, from both tablesRows of the kept table remain as one row each, with the other table's columns set to NULL; unmatched rows of the other table are removed
KindsOne kind; INNER can be omitted and written simply as JOINThree kinds: left (LEFT), right (RIGHT) and full (FULL); OUTER can be omitted
Example SQLSELECT … FROM employee INNER JOIN dept ON employee.dept_id = dept.dept_idSELECT … FROM employee LEFT OUTER JOIN dept ON employee.dept_id = dept.dept_id
Example row count (4 employees, one with a NULL department ID; 3 departments, one with no employees)3 rows: the employee with a NULL department ID and the department with no employees do not appear4 rows with a left outer join that keeps the employee table (the employee with a NULL department ID gets a NULL department name); 5 rows with a full outer join that keeps both
Order of the tablesSwapping left and right gives the same rows (only the column order changes)For left and right outer joins, which table is written on the left changes the result; A LEFT OUTER JOIN B returns the same rows as B RIGHT OUTER JOIN A
Conditions on the other table's columnsSame result whether written in ON or in the WHERE clauseWritten in the WHERE clause, the NULL-filled rows fail the condition and disappear, which can give the same result as an inner join; write it in ON to keep unmatched rows
Typical usesWhen only data present in both tables is wanted (for example, adding customer names to orders)When unmatched rows should also be listed (for example, listing all employees including those with no department), or when looking for rows without a partner

Figures are typical orders of magnitude and vary by process generation and product.

When to use which

Inner join
Use it when only rows with matching data in both tables are needed. For example, when adding customer names to a list of orders and orders with an unknown customer are not needed, use an inner join.
Outer join
Use it when rows without a partner must not be dropped. For example, to add department names to a list of all employees while keeping employees with no department, use a left outer join that keeps the employee table. With an outer join you can also find rows without a partner, such as customers who have never ordered, by selecting rows where the other table's primary key column is NULL.

FAQ

How do left and right outer joins differ?

Only in which table has all of its rows kept. A left outer join keeps the rows of the left table, written first in FROM, even when they have no partner; a right outer join keeps the rows of the right table, written second. A LEFT OUTER JOIN B and B RIGHT OUTER JOIN A return the same rows, so queries are often written consistently with left outer joins for readability.

Why can a join return more rows than the original tables?

In a join, each combination of rows that meets the condition becomes one result row. When the joined column has duplicate values in both tables, the number of combinations multiplies. For example, if one table has 2 rows with the value 1 and the other also has 2, that value alone gives 2 × 2 = 4 rows.

What happens without a join condition?

The result is every combination of rows from the two tables (the Cartesian product). This is called a CROSS JOIN: a 4-row table and a 3-row table give 4 × 3 = 12 rows. Producing a Cartesian product by accident, for example by forgetting ON, makes the row count jump, so take care.

Do NULLs match each other in a join condition?

No. In SQL a comparison with NULL is never true, so rows whose joined columns are NULL in both tables are not combined. As a result, an inner join drops rows whose joined column is NULL.

Check your understanding

An employee table (emp_no, name, dept_id) has four rows whose dept_id values are, in order, 10, 20, 10 and NULL. A department table (dept_id, dept_name) has three rows with dept_id 10, 20 and 30. How many rows does SELECT employee.name, department.dept_name FROM employee INNER JOIN department ON employee.dept_id = department.dept_id return?

  1. A3 rows
  2. B4 rows
  3. C12 rows
Show answer and explanation

Answer: A. 3 rows

An INNER JOIN returns only the combinations of rows that satisfy the ON condition. The two employees with dept_id 10 combine with department 10, and the one employee with dept_id 20 combines with department 20, so the result has three rows. The employee whose dept_id is NULL is not joined to any department, because a comparison with NULL is never true, and disappears from the result. Department 30, which has no employees, does not appear either. Four rows is the count for a left outer join that keeps every row of the employee table (employee LEFT OUTER JOIN department). Twelve rows is the CROSS JOIN (Cartesian product), which forms every combination without a condition: 4 × 3 = 12 rows.

An employee table (emp_no, name, dept_id) has five rows whose dept_id values are, in order, 10, 20, 10, NULL and NULL. A department table (dept_id, dept_name) has three rows with dept_id 10, 20 and 30. How many rows does SELECT department.dept_name, employee.name FROM department LEFT OUTER JOIN employee ON department.dept_id = employee.dept_id return?

  1. A3 rows
  2. B4 rows
  3. C5 rows
Show answer and explanation

Answer: B. 4 rows

A LEFT OUTER JOIN keeps every row of the left table (here the department table); when the right table has no matching row, it produces one row with the right table's columns filled with NULL. Department 10 has two employees, giving two rows; department 20 gives one row; and department 30, which has no employees, gives one row with a NULL name, for a total of four rows. The two employees whose dept_id is NULL match no department and, being rows of the right table, which is not the one kept in full, do not appear in the result. An inner join would also drop the department 30 row, giving three rows. Swapping the tables to employee LEFT OUTER JOIN department keeps all five employees, giving five rows. With outer joins, it is important to check which table keeps all its rows.

Column x of table R has four rows with values 1, 1, 2, 3, and column x of table S has four rows with values 1, 1, 2, 4. How many rows does SELECT * FROM R INNER JOIN S ON R.x = S.x return?

  1. A2 rows
  2. B3 rows
  3. C5 rows
Show answer and explanation

Answer: C. 5 rows

In a join, each combination of rows that satisfies the condition becomes one result row. For x = 1, every combination of R's two rows and S's two rows satisfies the condition, giving 2 × 2 = 4 rows; for x = 2 there is 1 × 1 = 1 row. R's 3 and S's 4 have no partner, so they do not appear in the inner join. The total is 4 + 1 = 5 rows. Two rows is the count if only one row is produced per matching value (1 and 2). Three rows is the count if you only check, for each row of R, whether S contains a matching value (a query such as SELECT * FROM R WHERE x IN (SELECT x FROM S)). The Cartesian product without a condition would be 4 × 4 = 16 rows. Be aware that duplicate values in the join column multiply the number of result rows.

  1. From a customer table (cust_id, name) and an orders table (order_id, cust_id, amount), you want the names of customers who have never placed an order. Which SQL statement is correct?

Practice Database Basics questions

Key terms explained

SQL
The standard language for defining tables, searching, inserting, updating and deleting data, and controlling privileges in relational databases. It is standardized by ISO/IEC. Its statements include SELECT, INSERT, UPDATE, DELETE, CREATE TABLE and GRANT.
Relational database
A database that represents data as tables (relations) of rows and columns and relates tables to each other through shared values. Also called an RDB. Queries and updates are made in SQL, and constraints such as primary keys and foreign keys keep the data consistent.
Primary key
A column, or set of columns, used to uniquely identify one row in a table. It carries the constraints that its values must not be duplicated and must not include NULL. The primary key is chosen from among the minimal sets of columns that can identify a row uniquely (candidate keys).
Foreign key
A column that refers to the primary key (or a unique column) of another table. Through the referential integrity constraint, which allows only values that exist in the referenced table or NULL, it prevents data that points to nonexistent rows. The behavior when a referenced row is deleted or updated (rejection, cascading deletion and so on) can also be specified.
Normalization
A design procedure that splits relational database tables based on functional dependencies between columns so that the same fact is not recorded in several places. It removes duplicated data and prevents update, insertion and deletion anomalies. Its stages include first to third normal form and Boyce-Codd normal form.
Database view
In a relational database, a virtual table defined by giving a name to a SELECT statement. It normally holds no data of its own and builds its result from the underlying tables each time it is referenced. It is used to simplify complex queries and to restrict access by limiting the columns and rows shown.

Related comparisons