SQL · Theory

Natural Join

Theory

68/439

Natural Join

NATURAL JOIN matches columns that have the same name on both tables and you do not write ON. If both have roll, it joins on roll. If they also both have created_at, it joins on that too — surprise.

Don’t use NATURAL JOIN in production. Name the columns: JOIN … ON s.roll = m.roll. Interviews: know it, then say you avoid it.

Trap — NATURAL JOIN as the default in answers. Hidden extra same-name columns.

On the example next to this theory — Natural Join — jOIN combines rows using a match condition between tables.

Diagram

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

Exam tip

Know NATURAL JOIN. Prefer JOIN … ON s.roll = m.roll.

Natural Join — sample query

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO orders VALUES (10, 1, 500), (11, 1, 200);
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Natural Join — jOIN combines rows using a match condition between tables.

Short notes

DefNATURAL JOIN uses same column names, no ON.
RuleAvoid in real SQL. Write ON explicitly.
TrapExtra same-name columns joining by accident.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER); INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'); INSERT INTO orders VALUES (10, 1, 500), (11, 1, 200); SELECT c.name, o.total FROM customers c JOIN orders o ON o.customer_id = c.id;

Natural Join

Natural Join — sample query

Short notes

Questions

Natural Join

Natural Join — sample query

Short notes

Questions