SQL INNER JOIN – Complete Guide with Examples (2026-27)

✅ What is SQL INNER JOIN?

✅ SQL INNER JOIN returns only the rows where there is a matching value in BOTH tables based on the specified ON condition.

✅ Rows that have no match in either table are excluded from the result — no NULLs for unmatched rows.

✅ INNER JOIN and JOIN (without a qualifier) are identical — most databases default to INNER JOIN when you write just JOIN.

✅ It is the most commonly used JOIN type in real-world SQL queries.

✅ SQL INNER JOIN Syntax

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

✅ INNER JOIN:- Keyword that triggers the join. Can be written as just JOIN — both are identical.

✅ ON:- Specifies the condition that connects the two tables — usually a foreign key = primary key.

✅ table1.column:- Always prefix column names with the table name or alias to avoid ambiguity.

✅ With Table Aliases (recommended):-

SELECT E.column1, D.column2, ...
FROM table1 AS E
INNER JOIN table2 AS D
ON E.common_column = D.common_column;

✅ Sample Tables Used in All Examples

✅ Example 1 – Basic INNER JOIN (Two Tables)

Join Employees with Department to show each employee's department name:

✅ SELECT Query:-

SELECT E.EmpID, E.Name, D.DeptName
FROM Employees E
INNER JOIN Department D
ON E.DeptID = D.DeptID;

✅ Result Show:-

✅ Example 2 – INNER JOIN with Selected Columns and Aliases

Select specific columns and use descriptive aliases for cleaner output:

✅ SELECT Query:-

SELECT E.EmpID          AS EmployeeID,
       E.Name           AS EmployeeName,
       D.DeptName       AS Department,
       D.Location       AS Office,
       E.Salary         AS MonthlySalary
FROM Employees E
INNER JOIN Department D
ON E.DeptID = D.DeptID;

✅ Result Show:-

✅ Example 3 – INNER JOIN with WHERE Clause

Join the tables, then filter the joined result to show only IT department employees:

✅ SELECT Query:-

SELECT E.Name AS EmployeeName,
       D.DeptName AS Department,
       E.Salary AS MonthlySalary
FROM Employees E
INNER JOIN Department D
ON E.DeptID = D.DeptID
WHERE D.DeptName = 'IT';

✅ Result Show:-

✅ Another example – Filter by salary after joining:-

SELECT E.Name, D.DeptName, E.Salary
FROM Employees E
INNER JOIN Department D
ON E.DeptID = D.DeptID
WHERE E.Salary > 8000;

✅ Result Show:-

✅ Example 4 – INNER JOIN with ORDER BY

Join the tables and sort the results by salary from highest to lowest:

✅ SELECT Query:-

SELECT E.Name AS EmployeeName,
       D.DeptName AS Department,
       E.Salary AS MonthlySalary
FROM Employees E
INNER JOIN Department D
ON E.DeptID = D.DeptID
ORDER BY E.Salary DESC;

✅ Result Show:-

✅ Example 5 – INNER JOIN Three Tables

Chain two INNER JOINs to combine three tables. Adding a third table (Location) linked to Department:

✅ Third Table: Location

✅ SELECT Query – Three-Table INNER JOIN:-

SELECT E.Name      AS EmployeeName,
       D.DeptName  AS Department,
       L.City      AS City,
       L.Country   AS Country,
       E.Salary    AS MonthlySalary
FROM Employees E
INNER JOIN Department D  ON E.DeptID   = D.DeptID
INNER JOIN Location   L  ON D.Location = L.City;

✅ Result Show:-

✅ Example 6 – INNER JOIN with GROUP BY and COUNT

Count the number of employees in each department using INNER JOIN and GROUP BY:

✅ SELECT Query:-

SELECT D.DeptName AS Department,
       COUNT(E.EmpID) AS TotalEmployees
FROM Employees E
INNER JOIN Department D
ON E.DeptID = D.DeptID
GROUP BY D.DeptName
ORDER BY TotalEmployees DESC;

✅ Result Show:-

✅ Example 7 – INNER JOIN with GROUP BY and Multiple Aggregates

Generate a complete salary report per department using INNER JOIN combined with COUNT, MIN, MAX, and AVG:

✅ SELECT Query:-

SELECT D.DeptName              AS Department,
       COUNT(E.EmpID)           AS TotalEmployees,
       MIN(E.Salary)            AS LowestSalary,
       MAX(E.Salary)            AS HighestSalary,
       ROUND(AVG(E.Salary), 2)  AS AvgSalary,
       SUM(E.Salary)            AS TotalSalaryBill
FROM Employees E
INNER JOIN Department D
ON E.DeptID = D.DeptID
GROUP BY D.DeptName
ORDER BY AvgSalary DESC;

✅ Result Show:-

✅ Example 8 – INNER JOIN with HAVING Clause

Use HAVING to filter grouped results after INNER JOIN — show only departments where the average salary is above 8000:

✅ SELECT Query:-

SELECT D.DeptName              AS Department,
       COUNT(E.EmpID)           AS TotalEmployees,
       ROUND(AVG(E.Salary), 2)  AS AvgSalary
FROM Employees E
INNER JOIN Department D
ON E.DeptID = D.DeptID
GROUP BY D.DeptName
HAVING AVG(E.Salary) > 8000
ORDER BY AvgSalary DESC;

✅ Result Show:-

Sales (AvgSalary = 5000) is excluded because 5000 is not greater than 8000.

✅ INNER JOIN vs LEFT JOIN – Side by Side Comparison

The same query using INNER JOIN vs LEFT JOIN produces very different results — understanding the difference is essential:

✅ Key Points to Remember

✅ INNER JOIN returns only matching rows — rows with no match in either table are excluded.

✅ JOIN = INNER JOIN — writing just JOIN without a qualifier defaults to INNER JOIN in all major databases.

✅ Always use the ON condition — without ON, the query becomes a CROSS JOIN producing millions of rows.

✅ Always use table aliases — E.Name, D.DeptName is clearer than Name, DeptName in multi-table queries.

✅ Chain multiple INNER JOINs — add as many INNER JOINs as needed to combine three or more tables.

✅ WHERE filters after the JOIN — the join runs first, then WHERE filters the intermediate result.

✅ Use HAVING to filter grouped JOIN results — not WHERE — when using GROUP BY with aggregate conditions.

✅ NULL values never match — a NULL in the join column (like MEENA's DeptID) never satisfies the ON condition.

✅ Specify columns, not SELECT * — in JOIN queries, always list the columns you need with their table prefix.

✅ Common SQL INNER JOIN Mistakes to Avoid