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.

line

Copyrights © 2024 letsupdateskills All rights reserved