SQL · Theory

EquiJoin

Theory

67/439

EquiJoin

Equijoin means the ON uses equality: ON s.roll = m.roll. Almost every student JOIN is an equijoin. The word shows up in MCQs versus theta join (>, <) which is rare.

Inner + equal keys = equijoin. You already know it if you can write JOIN … ON a = b.

Trap — treating ‘equijoin’ as a separate MySQL keyword. It is not. It is a description.

On the example next to this theory: EquiJoin: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Diagram

  students.id
       =
  marks.student_id
        │
        ▼
     one result row

Exam tip

Equijoin = ON left.col = right.col. No special keyword.

EquiJoin — sample query

-- EquiJoin
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

EquiJoin: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefEquijoin = JOIN with ON a = b.
RuleNormal PK/FK joins are equijoins.
TrapLooking for an EQUIJOIN keyword.

SQL · Theory

EquiJoin

← All stacks

Theory

67/439

EquiJoin

Equijoin means the ON uses equality: ON s.roll = m.roll. Almost every student JOIN is an equijoin. The word shows up in MCQs versus theta join (>, <) which is rare.

Inner + equal keys = equijoin. You already know it if you can write JOIN … ON a = b.

Trap — treating ‘equijoin’ as a separate MySQL keyword. It is not. It is a description.

On the example next to this theory: EquiJoin: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Diagram

  students.id
       =
  marks.student_id
        │
        ▼
     one result row

Exam tip

Equijoin = ON left.col = right.col. No special keyword.

EquiJoin — sample query

-- EquiJoin
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

EquiJoin: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

Short notes

DefEquijoin = JOIN with ON a = b.
RuleNormal PK/FK joins are equijoins.
TrapLooking for an EQUIJOIN keyword.

EquiJoin

EquiJoin — sample query

Short notes

Questions

EquiJoin

EquiJoin — sample query

Short notes

Questions