Given two tables, A and B, inner join between A and B return a list of rows that satisfy the "ON" clause.
INNER JOIN is both commutative and associative. (This is not true for other types of joins.)
Example of commutativity: (a INNER JOIN b) = (b INNER JOIN a)
Example of associativity: ((a INNER JOIN b) INNER JOIN c) = (a INNER JOIN (b INNER JOIN c))
Syntax:
SELECT a.columnX, b.columnY
FROM TableA a
INNER JOIN TableB b
ON a.columnZ = b.columnT
columnX and columnZ are any columns from
TableA.
columnY and columnT are any columns from
TableB.
INNER JOIN syntax is the same as
JOIN syntax.
Example: Suppose we have a table called Employees (with
columns EmployeeId and Name) and a table called
Tasks (with columns TaskId,
EmployeeId, and Name). We want to join
Employees with Tasks so that all employees who
have tasks and all tasks that are assigned to an employee are included.
Table: Employees
| EmployeeId | Name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
Table: Tasks
| TaskId | EmployeeId | Name |
|---|---|---|
| 101 | 1 | Task A |
| 102 | 2 | Task B |
| 103 | 4 | Task C |
SELECT e.Name, t.Name
FROM Employees e
JOIN Tasks t
ON e.EmployeeId = t.EmployeeId
Result of INNER JOIN:
| EmployeeName | TaskName |
|---|---|
| Alice | Task A |
| Bob | Task B |
If using LEFT JOIN, RIGHT JOIN we will have result table with 3 rows. Using FULL JOIN will have table with 4 rows. Only INNER JOIN will result 2 rows table.