Nov 10, 2018

Inner join and Cross join in SQL

The result of the Inner join can be defined as the outcome of first taking the Cartesian product (or Cross join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which satisfy the join predicate. Actual SQL implementations normally use other approaches like a hash join or a sort-merge join where possible, since computing the Cartesian product is very inefficient.

inner join:

SELECT * FROM employee INNER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

cross join:

SELECT * FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;

which is the same as:

SELECT * FROM employee CROSS JOIN department WHERE employee.DepartmentID = department.DepartmentID;