Equijoins, Self-Joins, and Cross-Joins


Equijoins

An equijoin compares columns of two tables with the = operator.

  • most joins are equijoins
  • non-equijoin compares columns with an operator other than =

Self-Join

A self-join joins a table to itself.

  • can compare any columns of a table,
    • as long as the columns have comparable data types
  • aliases are necessary to distinguish left and right tables

Example

  • A is the left table’s alias, and B is the right table’s alias

  • A.Name is the Name column of the left table, representing the employee

  • B.Name is the Name column of the right table, representing the employee’s manager

  • result shows employees along with each employee’s manager

SELECT A.Name, B.Name
FROM Employee A
INNER JOIN Employee B
ON B.ID = A.Manager;

Result

A.NameB.Name
Lisa EllisonJiho Chen
Sam SneadJiho Chen
Maria RodriguezJiho Chen

Cross-Joins

A cross-join combines two tables without comparing columns.

  • uses a CROSS JOIN clause without an ON clause
  • As a result, all possible combinations of rows from both tables appear in the result

Example

IPhone

ModelPrice
X1100
XR800

Storage

GigabytesPrice
640
128100
256200
SELECT Model, Gigabytes, IPhone.Price + Storage.Price
FROM IPhone
CROSS JOIN Storage;

Result

ModelGigabytesIPhone.Price + Storage.Price
X641100
XR64800
X1281200
XR128900
X2561300
XR2561000