SQL INNER JOIN

SQL INNER JOIN Statement

The SQL INNER JOIN statement is used to return records that have matching values in both tables. This join retrieves rows from both tables where there is a match on the joined columns.

Syntax

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

The INNER JOIN statement has the following components:

  • column1, column2, ...: The columns to be retrieved.
  • table1: The name of the first table.
  • table2: The name of the second table.
  • table1.column = table2.column: The condition to join the tables.

Example SQL INNER JOIN Statement Queries

Let's look at some examples of SQL INNER JOIN statement queries:

1. Basic INNER JOIN Example

SELECT employees.id, employees.first_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

This query retrieves the id and first_name columns from the employees table and the department_name column from the departments table. It returns only the records where there is a match between the department_id in the employees table and the id in the departments table.

2. INNER JOIN with WHERE Clause

SELECT employees.id, employees.first_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id
WHERE departments.department_name = 'Engineering';

This query retrieves the id and first_name columns from the employees table and the department_name column from the departments table. It returns only the records where there is a match between the department_id in the employees table and the id in the departments table, and where the department_name is 'Engineering'.

Full Example

Let's go through a complete example that includes creating tables, inserting data, and querying the tables with the INNER JOIN statement.

Step 1: Creating Tables

This step involves creating new tables named employees and departments to store employee and department data.

CREATE TABLE departments (
    id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    department_id INT
);

In this example, we create two tables: departments with columns for id and department_name, and employees with columns for id, first_name, and department_id.

Step 2: Inserting Data into the Tables

This step involves inserting some sample data into the employees and departments tables.

INSERT INTO departments (id, department_name)
VALUES (1, 'Engineering'), (2, 'HR');

INSERT INTO employees (id, first_name, department_id)
VALUES (1, 'John', 1), (2, 'Jane', 2), (3, 'Jim', 1);

Here, we insert data into the employees and departments tables.

Step 3: Querying the Tables with INNER JOIN

This step involves selecting the data from the employees and departments tables and joining them using the INNER JOIN statement.

SELECT employees.id, employees.first_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

This query retrieves all the rows from the employees and departments tables where there is a match on the department_id in the employees table and the id in the departments table. The result will be:

id  first_name  department_name
--- ----------- ---------------
1   John        Engineering
2   Jane        HR
3   Jim         Engineering

Conclusion

The SQL INNER JOIN statement is a powerful tool for returning records that have matching values in both tables. Understanding how to use the INNER JOIN statement and its syntax is essential for effective data retrieval and analysis in SQL databases.