What is the difference between CROSS JOIN and FULL OUTER JOIN in SQL Server?
Are they the same, or not? Please explain. When would one use either of these?
What is the difference between CROSS JOIN and FULL OUTER JOIN in SQL Server?
Are they the same, or not? Please explain. When would one use either of these?
Want to add intuition on the common types of joins. Think of parametrized join pseudo-operator:
function generalJoin(
forceIncludeLeft: boolean, forceIncludeRight: boolean, condition: Expression,
tableA: Table, tableB: Table
) {
for (leftRow in tableA) {
for (rightRow in tableB) {
// Output combined row if condition is true
if (condition(leftRow, rightRow)) {
markRowHasMatch(leftRow);
markRowHasMatch(rightRow);
output(leftRow.append(rightRow));
}
}
}
if (forceIncludeLeft) {
for (leftRow in tableA) {
if (!rowHasMatch(leftRow)) {
output(leftRow.append(createArrayOfNulls(tableB.columnsCount)));
}
}
}
if (forceIncludeRight) {
for (rightRow in tableB) {
if (!rowHasMatch(rightRow)) {
output(createArrayOfNulls(tableA.columnsCount).append(rightRow));
}
}
}
}
// We may think of Expression as function taking rowA and rowB and
// returning boolean true or false
function alwaysTrue(rowA, rowB) {
return true;
}
which is equivalent to
[SELECT ...] from tableA SOMEJOIN tableB ON condition
Then this is the equivalence table:
a [INNER] JOIN b ON condition <=> generalJoin(false, false, condition, a, b)
a LEFT [OUTER] JOIN b ON condition <=> generalJoin(true, false, condition, a, b)
a RIGHT [OUTER] JOIN b ON condition <=> generalJoin(false, true, condition, a, b)
a FULL [OUTER] JOIN b ON condition <=> generalJoin(true, true, condition, a, b)
a CROSS JOIN b <=> generalJoin(false, false, alwaysTrue, a, b)
Effectively, CROSS JOIN is equivalent to INNER JOIN with no condition, i.e. every left row does match every right row.