Introduction.

Have you ever found yourself lost in the vast world of databases, wondering how to seamlessly combine information from different tables? Don't worry! SQL InnerSQL Inner Join, a feature that can significantly enhance the way we retrieve and manipulate data. In this article, we'll explain SQL Inner Join in a simple way, with step-by-step instructions and real-world examples.

What is SQL Inner Join?

At its core, SQL Inner Join is a mechanism that allows us to combine information from two or more tables based on shared values in specific columns. It's like a digital matchmaker, pairing data that complements each other seamlessly. The SQL Inner Join is really good at getting information from tables when the values in those tables match each other.

Why Inner Join Matters:

This is the default join, ensuring that tables are joined based on records with matching values in common columns. This straightforward process makes it a go-to choice for developers.

Syntax Simplified

Let's break down the syntax for performing a Inner Join in SQL:

SELECT ExampleTableOne.columnName1, ExampleTableTwo.columnName2
FROM ExampleTableOne
INNER  JOIN ExampleTableTwo ON ExampleTableOne.ColumnName = ExampleTableTwo.ColumnName;

How this Works: A Step-by-Step Guide

• Selecting Columns:

Specify the columns you want to retrieve from the tables involved in the join.

• Using the INNER JOIN Keyword:

Use the INNER JOIN keyword to show that you want to perform an Inner Join.

• Defining the Join Condition:

Clearly state the condition that the join should be based on, typically involving matching columns in the participating tables.

• The Result:

Once the join condition is satisfied, a new table is formed by combining the selected columns from both tables.

Now, let's look at real-world examples to understand this.

Example 1: Employee and Department Integration

Table 1: employee_detail

CREATE TABLE employee_detail (
emp_id INT NOT NULL,
emp_name VARCHAR (20) NOT NULL,
emp_salary INT NOT NULL,
dept_id CHAR (25),       
PRIMARY KEY (emp_id)
);

emp_id	emp_name	emp_salary	dept_id
001	John Doe	60000	001
002	Jane Smith	55000	002
003	Mike Johnson	62000	001
004	Emily Davis	58000	NULL

Table 2: department_detail

CREATE TABLE department_detail (
dept_id INT NOT NULL,
dept_name VARCHAR (20) NOT NULL,    
PRIMARY KEY (dept_id)
);

dept_id	dept_name
001	HR
002	IT
003	Finance

Imagine we have two tables, employee_details and department_details. By performing an Inner Join on the common column EmpID, we can obtain a consolidated table with information such as Employee ID, Name, Salary, dept_name, and dept_id Status. Here's the SQL query:

SELECT e.emp_id, e.emp_name, e.emp_salary, d.dept_id, d.dept_name
FROM employee_details e
LEFT JOIN department_details d ON e.dept_id = d.dept_id;

emp_id	emp_name	emp_salary	dept_id	dept_name
001	John Doe	60000	001	HR
002	Jane Smith	55000	002	IT
003	Mike Johnson	62000	001	HR
004	Emily Davis	58000	NULL	NULL

In this example, all rows from the employee_detail table are included in the result, and matching rows from the department_detail table are included based on the department id. If there is no match, the columns from the department_detail table will contain NULL values.