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;


Popular posts from this blog

How to get Active Transactions and Locks from MySQL

Example of a PAC file