Lesson 3	Types of joins: 1) inner join
Objective	Define an inner join.

SQL Inner Joins

Inner Join

The inner join is the most commonly used type of join in SQL. It returns only the rows that have matching values in both tables being joined. Let's look at an example to understand it better:
Suppose we have two tables employees and departments as shown below:

employees
+------+----------+-------------+
| id   | name     | department  |
+------+----------+-------------+
| 1    | John     | HR          |
| 2    | Alice    | IT          |
| 3    | Bob      | HR          |
| 4    | Sarah    | Sales       |
+------+----------+-------------+

departments
+----+------------+
| id | name       |
+----+------------+
| 1  | HR         |
| 2  | IT         |
| 3  | Marketing  |
+----+------------+

To get the name of the employees and their respective departments, we can use an inner join as follows:

SELECT employees.name, departments.name
FROM employees
JOIN departments
ON employees.department = departments.name;

This query returns the following result:

+--------+-----------+
| name   | name      |
+--------+-----------+
| John   | HR        |
| Alice  | IT        |
| Bob    | HR        |
+--------+-----------+

As you can see, only the rows with matching values in both tables (employees and departments) are returned in the result set. The JOIN statement in this query is an INNER JOIN by default.

You should consider creating a join between tables whenever the records in the tables will be combined frequently to produce meaningful information. In the previous lesson you saw the result of a join between two tables (CD and Distributor) with records having the same value in a common field. That type of join is referred to as an equi-join, because the common field contains equivalent values in both records. Equi-joins also are referred to as natural joins. In addition to the equi-join, there are several other types of joins you can perform, including:
The remainder of this lesson and the following lesson discuss these joins in detail.

Inner Joins

Below is the equi-join you saw in the previous lesson

ER diagram — The virtual table consists of CD and DISTRIBUTOR

This is also an example of an inner join^[1] . The best way to define an inner join is to show the Structured Query Language (SQL) command used to create the records shown in the preceding figure. That command is:

SELECT CDNo, Title, DistID, DistName
FROM CD JOIN Distributor 
ON CD.DistID = Distributor.DistID

This course only touches on the aspects of SQL that are absolutely essential to explaining joins.
See the Sitemap for a listing of courses offering in-depth instruction on writing effective SQL statements.

This join is called an inner join because the join only produces records when there is a match between the value in the fields in the ON statement (in this case, CD.DistID and Distributor.DistID). It is as if the RDBMS pulled the fields for the join from the "inside" of the tables involved and wrote the new records to a virtual table.
You should create an inner join whenever you need to combine records from more than one table, with the provision that the records have matching values in equivalent fields. In the example above, you would create records that listed every

CD's number,
title,
the id. code of the CD's distributor, and
the distributor's name.

The next lesson discusses same-table^[2] , outer^[3] , and Cartesian product^[4] joins.

[1]inner join: A link between tables in a database that displays only the rows with a match (true value of the join condition) for both join tables. An inner join does not guarantee the return of every row of data that you expect will be returned.

[2]same-table join: A type of inner join, so named because it creates two copies of a table and then joins records from the tables where the tables have equivalent values in designated fields.

[3]outer join: A link between tables in a database. When two tables are combined by an outer join, any records from the base table with no matching records from the other table are included in the results and any columns where no values are available are filled with nulls.

[4]Cartesian product: A type of table join: the Cartesian product of two tables consists of the combination of every record in one table with every record in another table.

Database Analysis for Design