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.Nameis the Name column of the left table, representing the employee
B.Nameis the Name column of the right table, representing the employee’s managerresult 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.Name B.Name Lisa Ellison Jiho Chen Sam Snead Jiho Chen Maria Rodriguez Jiho Chen
Cross-Joins
A cross-join combines two tables without comparing columns.
- uses a
CROSS JOINclause without anONclause - As a result, all possible combinations of rows from both tables appear in the result
Example
IPhone
Model Price X 1100 XR 800 Storage
Gigabytes Price 64 0 128 100 256 200 SELECT Model, Gigabytes, IPhone.Price + Storage.Price FROM IPhone CROSS JOIN Storage;Result
Model Gigabytes IPhone.Price + Storage.Price X 64 1100 XR 64 800 X 128 1200 XR 128 900 X 256 1300 XR 256 1000
