How to Use INNER JOIN in SQL
The INNER JOIN clause in SQL is used to combine rows from two or more tables based on a related column between them. It retrieves records that have matching values in both tables. When there is no match, the row is excluded from the result set. This makes INNER JOIN one of the most commonly used join operations in SQL queries.
Syntax of INNER JOIN
SELECT column1, column2, ... FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column;
- table1: The first table in the join operation.
- table2: The second table in the join operation.
- common_column: The column that exists in both tables and is used to match the rows.
Example of INNER JOIN
Consider two tables: employees and departments.
Table 1: employees
employee_id | first_name | last_name | department_id |
---|---|---|---|
1 | John | Doe | 101 |
2 | Jane | Smith | 102 |
3 | Jim | Brown | 103 |
4 | Jill | White | NULL |
Table 2: departments
department_id | department_name |
---|---|
101 | HR |
102 | IT |
103 | Finance |
SQL Query
SELECT employees.first_name, employees.last_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;
Result:
first_name | last_name | department_name |
---|---|---|
John | Doe | HR |
Jane | Smith | IT |
Jim | Brown | Finance |
Explanation of the Query
The query selects the first_name, last_name, and department_name columns.
It performs an INNER JOIN between the employees table and the departments table.
The ON keyword specifies the condition for the join, which is that the department_id column in employees must match the department_id in departments.
Since Jill White has no department_id (i.e., NULL), she is excluded from the result set because the INNER JOIN only returns rows with matching data.
Key Points:
- An INNER JOIN is the default type of join, so you can simply write JOIN without the INNER keyword.
- The result set only includes rows where there is a match in both tables.
- If there are rows in one table with no matching rows in the other table, those rows will not be included in the result.
Use Cases for INNER JOIN:
- Combining Related Data: You can use INNER JOIN to combine data from multiple tables in cases where the tables are related through a common field, like customer IDs, product IDs, or department IDs.
- Filtering Data: Use INNER JOIN to filter records based on the relationship between the two tables.
Conclusion
The INNER JOIN is an essential tool for querying relational databases, allowing you to extract data from multiple tables by matching related columns. It helps in displaying only the relevant data from related tables, making it a core SQL operation.