SQL INNER JOIN
Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Example
Consider two tables students and departments with the following data:
students table
| id | name | dept_id |
|---|---|---|
| 1 | Alice | 1 |
| 2 | Bob | 2 |
| 3 | Carol | 1 |
departments table
| id | name |
|---|---|
| 1 | Computer Science |
| 2 | Mathematics |
To retrieve the name of the department of each student, we can use an INNER JOIN as follows:
SELECT students.name, departments.name
FROM students
INNER JOIN departments
ON students.dept_id = departments.id;
Output
| name | name |
|---|---|
| Alice | Computer Science |
| Bob | Mathematics |
| Carol | Computer Science |
Explanation
An INNER JOIN retrieves only the rows that have matching values in both tables. In the example above, the ON clause specifies that we want to join the rows where the dept_id column in the students table matches the id column in the departments table.
Use
INNER JOINs are useful when we need to retrieve data that is spread across multiple tables. We can combine data from different sources into a single result set with an INNER JOIN.
Important Points
- The INNER JOIN keyword selects records that have matching values in both tables.
- INNER JOINs are used to combine data from two or more tables based on a related column between them.
Summary
In summary, an INNER JOIN is used to retrieve only the rows that have matching values in both tables. It combines data from different sources into a single result set and is useful when working with data that is spread across multiple tables.