SQL · Theory

Derived Table

Theory

279/439

Derived Table

A subquery is a SELECT inside another SQL. WHERE marks = (SELECT MAX(marks) FROM students); the topper. The inner query runs as a value or as a table.

Scalar subquery returns one value. IN (SELECT roll FROM …) returns a list. FROM (SELECT …) AS t is a derived table and needs an alias.

Correlated subquery uses the outer row (WHERE m.roll = s.roll) and can be slow. A JOIN is often clearer and faster.

Trap: subquery that returns two rows where one value was expected. Trap: correlated subquery in a huge table without an index.

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

Exam tip

WHERE marks = (SELECT MAX(marks) FROM students);

Derived Table — sample query

-- Derived Table
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;

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

Short notes

DefSELECT inside SELECT. Example: MAX in WHERE.
RuleOne value vs a list (IN). Derived tables need aliases.
TrapMulti-row subquery where one value is required.

SQL · Theory

Derived Table

← All stacks

Theory

279/439

Derived Table

A subquery is a SELECT inside another SQL. WHERE marks = (SELECT MAX(marks) FROM students); the topper. The inner query runs as a value or as a table.

Scalar subquery returns one value. IN (SELECT roll FROM …) returns a list. FROM (SELECT …) AS t is a derived table and needs an alias.

Correlated subquery uses the outer row (WHERE m.roll = s.roll) and can be slow. A JOIN is often clearer and faster.

Trap: subquery that returns two rows where one value was expected. Trap: correlated subquery in a huge table without an index.

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

Exam tip

WHERE marks = (SELECT MAX(marks) FROM students);

Derived Table — sample query

-- Derived Table
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;

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

Short notes

DefSELECT inside SELECT. Example: MAX in WHERE.
RuleOne value vs a list (IN). Derived tables need aliases.
TrapMulti-row subquery where one value is required.

Derived Table

Derived Table — sample query

Short notes

Questions

Derived Table

Derived Table — sample query

Short notes

Questions