An INNER JOIN returns only the rows where there is a match in both tables.
Example:
SELECT a.EmployeeID, a.Name, b.DepartmentName
FROM Employees a
INNER JOIN Departments b ON a.DepartmentID = b.DepartmentID;We can use JOIN instead of INNER JOIN.
Also, there are more types of joins, such as LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), FULL JOIN (or FULL OUTER JOIN), SELF JOIN, and CROSS JOIN (returns the Cartesian product of both tables. Every row from the first table is combined with every row from the second table).
JOIN with Aggregate Functions COUNT
Retrieve the number of employees in each department.
SELECT b.DepartmentName, COUNT(a.EmployeeID) AS NumberOfEmployees
FROM Employees a
INNER JOIN Departments b ON a.DepartmentID = b.DepartmentID
GROUP BY b.DepartmentName;