WikiGalaxy
SQL Inner Joins
Understanding Inner Joins:
An SQL INNER JOIN is used to combine rows from two or more tables, based on a related column between them. It returns records that have matching values in both tables.
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
This query retrieves employees' names along with their respective department names by matching department IDs.
Console Output:
John Doe | Sales
Using Aliases in Inner Joins
Aliases can make your queries more readable, especially when dealing with multiple tables.
SELECT e.name, d.department_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id;
Here, 'e' and 'd' are aliases for the 'employees' and 'departments' tables respectively.
Console Output:
Jane Smith | Marketing
Inner Join with Multiple Tables
You can join more than two tables using INNER JOIN to fetch comprehensive data from multiple sources.
SELECT e.name, d.department_name, p.project_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id
INNER JOIN projects AS p ON e.project_id = p.id;
This example joins employees, departments, and projects tables to show which employee is working on which project and in which department.
Console Output:
Alice Brown | HR | Project Apollo
Filtering Results with WHERE Clause
The WHERE clause can be used with INNER JOIN to filter the results based on specific conditions.
SELECT e.name, d.department_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id
WHERE d.department_name = 'Finance';
This query filters the results to show only employees working in the Finance department.
Console Output:
Michael Scott | Finance
Inner Join with Aggregate Functions
Aggregate functions like COUNT, SUM, AVG, etc., can be used with INNER JOIN to perform calculations on the joined data.
SELECT d.department_name, COUNT(e.id) AS number_of_employees
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id
GROUP BY d.department_name;
This query counts the number of employees in each department.
Console Output:
IT | 5
Joining Tables with Different Column Names
Inner joins can also be performed on tables with different column names by specifying the correct matching columns.
SELECT e.name, p.project_name
FROM employees AS e
INNER JOIN projects AS p ON e.current_project = p.project_code;
This example joins the employees and projects tables using different column names.
Console Output:
Emma Wilson | Project Beta
Inner Join with Self Join
A self join is a regular join but the table is joined with itself. It can be used to compare rows within the same table.
SELECT a.name AS Employee, b.name AS Manager
FROM employees AS a
INNER JOIN employees AS b ON a.manager_id = b.id;
This query lists employees along with their managers by joining the employees table with itself.
Console Output:
Tom Hanks | Robert Downey
Combining Inner Join with ORDER BY
The ORDER BY clause can be used to sort the results of an INNER JOIN query.
SELECT e.name, d.department_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id
ORDER BY e.name ASC;
This query sorts the employee names in ascending order while displaying their department names.
Console Output:
Aiden Turner | Engineering
