SQL · Theory

Inner Join

Theory

60/439

Inner Join

INNER JOIN is the default JOIN. Only rows with a match on both sides. A student with no marks row disappears from the result. A marks row with a bad roll (no student) also disappears.

SELECT s.name, m.score FROM students s INNER JOIN marks m ON s.roll = m.roll; Same as JOIN without the word INNER.

Use INNER when you only want complete pairs. Use LEFT when you still need students with no marks.

Trap — INNER JOIN then wondering why 2 of 30 students vanished — they had no child rows.

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

Diagram

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

Exam tip

INNER JOIN = only matches. LEFT JOIN if you need unmatched left rows.

Inner 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;

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

Short notes

DefINNER JOIN = matching rows only.
RuleSame as JOIN. Unmatched rows drop out.
TrapMissing students who have no marks yet.

SQL · Theory

Inner Join

← All stacks

Theory

60/439

Inner Join

INNER JOIN is the default JOIN. Only rows with a match on both sides. A student with no marks row disappears from the result. A marks row with a bad roll (no student) also disappears.

SELECT s.name, m.score FROM students s INNER JOIN marks m ON s.roll = m.roll; Same as JOIN without the word INNER.

Use INNER when you only want complete pairs. Use LEFT when you still need students with no marks.

Trap — INNER JOIN then wondering why 2 of 30 students vanished — they had no child rows.

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

Diagram

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

Exam tip

INNER JOIN = only matches. LEFT JOIN if you need unmatched left rows.

Inner 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;

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

Short notes

DefINNER JOIN = matching rows only.
RuleSame as JOIN. Unmatched rows drop out.
TrapMissing students who have no marks yet.

Inner Join

Inner Join — sample query

Short notes

Questions

Inner Join

Inner Join — sample query

Short notes

Questions