Inner Joins with SQL

Inner Joins

Concept of Inner Joins

An Inner Join is used to combine rows from two or more tables based on a related column between them. It returns only the rows where there is a match in both tables. If no match is found, those rows are excluded from the result.

General Syntax 

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Explanation of Terms

• table1 and table2: The names of the tables you want to join.
• INNER JOIN: The type of join you are performing.
• ON table1.column = table2.column: The join condition. Rows are combined only when this condition is true.

Practical Examples

Basic Example

Let’s consider two simple tables:

• Table Employees:
  • ID (INT)
  • Name (VARCHAR)
  • DepartmentID (INT)
• Table Departments:
  • ID (INT)
  • DepartmentName (VARCHAR)

To get a list of employees along with their department names: 

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.ID;

Explanation:

• This query joins the Employees table with the Departments table.
• Rows from Employees are combined with rows from Departments where Employees.DepartmentID matches Departments.ID.
• Only rows with matches in both tables will be included in the result.

Example with Multiple Conditions

Suppose we have an additional table:

• Table Projects:
  • ID (INT)
  • ProjectName (VARCHAR)
  • ManagerID (INT) — the ID of the employee managing the project

To get details of projects with the names of the managers and their departments: 

SELECT Projects.ProjectName, Employees.Name AS Manager, Departments.DepartmentName
FROM Projects
INNER JOIN Employees
ON Projects.ManagerID = Employees.ID
INNER JOIN Departments
ON Employees.DepartmentID = Departments.ID;

Explanation:

• This query performs a join between the three tables: Projects, Employees, and Departments.
• It first joins Projects with Employees on Projects.ManagerID = Employees.ID.
• Then, it joins the result with Departments on Employees.DepartmentID = Departments.ID.

Inner Join with Aliases

Aliases simplify table names for clearer queries. 

SELECT e.Name AS EmployeeName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.ID;

Explanation:

• e is an alias for Employees and d is an alias for Departments.
• The columns are selected with aliases for better readability in the result.

Using Inner Joins with Aggregations

You might need to combine inner joins with aggregation functions to summarize data.

For example, to get the number of employees in each department: 

SELECT Departments.DepartmentName, COUNT(Employees.ID) AS EmployeeCount
FROM Departments
INNER JOIN Employees
ON Departments.ID = Employees.DepartmentID
GROUP BY Departments.DepartmentName;

Explanation:

• This query joins Departments with Employees.
• It counts the number of employees per department using COUNT.
• The results are grouped by the department name.

Inner Join with Additional Conditions

You can add extra conditions in the WHERE clause to refine results.

For example, to get employees from a specific department: 

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.ID
WHERE Departments.DepartmentName = 'Marketing';

Explanation:

• This query returns only employees who are in the ‘Marketing’ department.

Key Points to Remember

• Matching Rows: An inner join returns only rows with matches in both tables.
• Join Conditions: The join condition determines how tables are combined. An incorrect condition can lead to unexpected results.
• Performance: Inner joins can be costly in terms of performance if tables are very large. Use appropriate indexing to improve performance.