SQL Server: What is the difference between CROSS JOIN and FULL OUTER JOIN?

Viewed 279020

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?

10 Answers

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.

Related