Relations (Joined, Derived)

DSA, Database System & Operating System — Data Models, Normalization, and SQL, NEC licence examination syllabus (Nepal Engineering Council).

Relations: Joined and Derived

Combining tables — inner, outer, cross, natural and self joins — plus derived tables and CTEs.

🌍 Where this lives: joins are the price of normalization and the reason it is worth paying. You split data across tables so each fact is stored once; joins put it back together on demand. Get one wrong and the failure is silent: an INNER JOIN where you needed LEFT quietly drops every customer with no orders, so your "total customers" report is wrong and nothing errors. That specific bug — a filter in WHERE after a LEFT JOIN, silently converting it to an inner join — is common enough that it has a name in code reviews. Search "left join where clause gotcha".

The join family

CROSS JOIN (Cartesian product) every row of A paired with every row of B result size = |A| × |B| SELECT * FROM a CROSS JOIN b; SELECT * FROM a, b; -- old comma syntax INNER JOIN only rows where the condition matches, on BOTH sides SELECT * FROM a JOIN b ON a.k = b.k; LEFT OUTER JOIN all rows of the LEFT table; NULLs where the right has no match SELECT * FROM a LEFT JOIN b ON a.k = b.k; RIGHT OUTER JOIN all rows of the RIGHT table; NULLs on the left (equivalent to swapping the tables and using LEFT) FULL OUTER JOIN all rows of BOTH; NULLs on whichever side is unmatched NATURAL JOIN joins automatically on ALL columns with the same NAME, and collapses them into one → convenient and DANGEROUS (see below) SELF JOIN a table joined to itself, using aliases used for hierarchies and comparisons within a table SEMI JOIN / ANTI JOIN not keywords, but patterns: semi: WHERE EXISTS (...) "has a match" anti: WHERE NOT EXISTS (...) "has no match" EQUI-JOIN vs THETA-JOIN equi-join : the condition uses = only theta-join : any comparison ON a.salary > b.salary

Worked example — every join on the same data

joins.sql

CREATE TABLE student (roll INT PRIMARY KEY, name TEXT,
                      dept CHAR(6));
CREATE TABLE department (code CHAR(6) PRIMARY KEY, dname TEXT);

INSERT INTO student VALUES
    (101,'Ram','ACtE07'), (102,'Sita','ACtE07'),
    (103,'Hari','AExE01'), (104,'Gita', NULL);
                             -- ^ no department
INSERT INTO department VALUES
    ('ACtE07','Computer'), ('AExE01','Electrical'),
    ('ACtE09','AI');
                             -- ^ no students

-- ============ CROSS JOIN: 4 x 3 = 12 ============
SELECT COUNT(*) AS cross_rows FROM student CROSS JOIN department;
--  cross_rows
--  ------------
--          12

-- ============ INNER JOIN: 3 rows ============
-- Gita is dropped (NULL dept), 'AI' is dropped (no students)
SELECT s.roll, s.name, d.dname
FROM   student s JOIN department d ON d.code = s.dept
ORDER  BY s.roll;
--  roll | name |   dname
--  -----+------+------------
--   101 | Ram  | Computer
--   102 | Sita | Computer
--   103 | Hari | Electrical
--  (3 rows)

-- ============ LEFT OUTER: 4 rows ============
-- every student kept; Gita gets NULL for dname
SELECT s.roll, s.name, d.dname
FROM   student s LEFT JOIN department d ON d.code = s.dept
ORDER  BY s.roll;
--  roll | name |   dname
--  -----+------+------------
--   101 | Ram  | Computer
--   102 | Sita | Computer
--   103 | Hari | Electrical
--   104 | Gita |               <- NULL, row PRESERVED
--  (4 rows)

-- ============ RIGHT OUTER: 4 rows ============
-- every department kept; 'AI' gets NULL for name
SELECT s.name, d.code, d.dname
FROM   student s RIGHT JOIN department d ON d.code = s.dept
ORDER  BY d.code, s.name;
--  name |  code  |   dname
--  ------+--------+------------
--  Ram  | ACtE07 | Computer
--  Sita | ACtE07 | Computer
--       | ACtE09 | AI          <- department with no student
--  Hari | AExE01 | Electrical
--  (4 rows)

-- ============ FULL OUTER: 5 rows ============
-- unmatched rows from BOTH sides appear
SELECT s.name, d.code
FROM   student s FULL JOIN department d ON d.code = s.dept
ORDER  BY d.code NULLS LAST, s.name;
--  name |  code
--  ------+--------
--  Ram  | ACtE07
--  Sita | ACtE07
--       | ACtE09    <- unmatched RIGHT
--  Hari | AExE01
--  Gita |           <- unmatched LEFT
--  (5 rows)

ROW COUNTS, all from the same 4-row and 3-row tables: CROSS 12 = 4 × 3 INNER 3 only matching pairs LEFT 4 = 3 matches + 1 unmatched student RIGHT 4 = 3 matches + 1 unmatched department FULL 5 = 3 matches + 1 left + 1 right The counts alone tell you which join you used. If a report's row count changes when you "just fix the join", you have changed its meaning. WHY GITA IS DROPPED BY THE INNER JOIN: her dept is NULL, and NULL = 'ACtE07' evaluates to UNKNOWN, not TRUE. The join condition must be TRUE for a row to appear. That is the same three-valued logic from the keys topic, now deciding which rows survive a join.

The `WHERE`-after-`LEFT JOIN` trap

left_join_trap.sql

-- INTENT: list all students, showing their department if
-- they have one, but only where the department is 'Computer'.

-- ATTEMPT 1 — filter in WHERE. This SILENTLY becomes an
-- inner join.
SELECT s.name, d.dname
FROM   student s LEFT JOIN department d ON d.code = s.dept
WHERE  d.dname IS NOT NULL
ORDER  BY s.roll;
--  name |   dname
--  ------+------------
--  Ram  | Computer
--  Sita | Computer
--  Hari | Electrical
--  (3 rows)   <- Gita is GONE. The LEFT JOIN was wasted.

-- WHY: the LEFT JOIN produces Gita with dname = NULL, and
-- then WHERE dname IS NOT NULL removes exactly that row.
-- Any WHERE predicate on the right table's columns discards
-- the NULL-extended rows, converting LEFT to INNER.

-- ATTEMPT 2 — put the filter in the ON clause instead.
SELECT s.name, d.dname
FROM   student s LEFT JOIN department d
       ON d.code = s.dept AND d.dname = 'Computer'
ORDER  BY s.roll;
--  name |  dname
--  ------+----------
--  Ram  | Computer
--  Sita | Computer
--  Hari |            <- kept, dname NULL because Electrical
--  Gita |               did not satisfy the ON condition
--  (4 rows)   <- all four students preserved ✔

-- THE RULE:
--   ON    decides which rows MATCH  (evaluated during the join)
--   WHERE decides which rows SURVIVE (evaluated after)
--
-- For an INNER join the two are equivalent.
-- For an OUTER join they are completely different.
--
-- Diagnostic: if a query has a LEFT JOIN and also a WHERE
-- mentioning the right table's columns, it is almost
-- certainly a bug — unless the predicate is deliberately
-- "IS NULL" to find non-matches (the anti-join below).

-- ============ THE ANTI-JOIN — the one legitimate case
-- of WHERE ... IS NULL after a LEFT JOIN ============
SELECT s.roll, s.name
FROM   student s LEFT JOIN department d ON d.code = s.dept
WHERE  d.code IS NULL;
--  roll | name
--  -----+------
--   104 | Gita     <- students with NO department
--
-- Here the IS NULL is the POINT: it selects exactly the
-- NULL-extended rows, i.e. the non-matches. Equivalent and
-- clearer:
--   WHERE NOT EXISTS (SELECT 1 FROM department d
--                     WHERE d.code = s.dept)

The distinction is worth stating as a rule you can apply mechanically: ON runs during the join, WHERE runs after it. An outer join adds NULL-extended rows, and a WHERE on the outer side's columns then deletes them. That is why the two clauses are interchangeable for inner joins and never for outer ones.

NATURAL JOIN and why to avoid it

NATURAL JOIN joins on every column the two tables share BY NAME, and merges those columns in the output. SELECT * FROM student NATURAL JOIN department; In our schema student has (roll, name, dept) and department has (code, dname). NO column names are shared, so the natural join degenerates into a CROSS JOIN: SELECT COUNT(*) FROM student NATURAL JOIN department; → 12 (the full Cartesian product!) That is the danger in one line: NATURAL JOIN with no common columns silently produces a Cartesian product instead of an error. THE THREE FAILURE MODES: 1. NO common columns → silent CROSS JOIN, as above. 2. TOO MANY common columns. If both tables happen to have a `name` column, the natural join also requires student.name = department.name — almost certainly not intended. 3. FRAGILE UNDER SCHEMA CHANGE. Add a `created_at` column to both tables and every NATURAL JOIN between them silently changes meaning. The query still compiles; the results are now wrong. USING (col, ...) is the safer middle ground — explicit about which columns, and still merges them: SELECT * FROM enrolment JOIN course USING (course_code); → joins on course_code, and outputs it once BEST PRACTICE: write the condition explicitly. ON d.code = s.dept It is a few more characters and it cannot be broken by someone adding a column.

Self join — hierarchies within one table

self_join.sql

CREATE TABLE emp (
    id   INT PRIMARY KEY,
    name TEXT NOT NULL,
    mgr  INT REFERENCES emp(id)      -- self-reference
);
INSERT INTO emp VALUES
    (1,'Sharma',NULL),               -- top of hierarchy
    (2,'Karki',1), (3,'Thapa',1), (4,'Devi',2);

-- LEFT self join: every employee with their manager's name.
-- LEFT is essential — Sharma has no manager and an inner
-- join would drop the top of the hierarchy.
SELECT e.name AS employee, m.name AS manager
FROM   emp e LEFT JOIN emp m ON m.id = e.mgr
ORDER  BY e.id;
--  employee | manager
--  ---------+---------
--  Sharma   |            <- the root, preserved by LEFT
--  Karki    | Sharma
--  Thapa    | Sharma
--  Devi     | Karki
--  (4 rows)

-- a theta self join: pairs of employees under the same
-- manager, without duplicating each pair
SELECT a.name AS one, b.name AS other
FROM   emp a JOIN emp b
       ON a.mgr = b.mgr AND a.id < b.id;
--  one   | other
--  ------+-------
--  Karki | Thapa
--
-- The a.id < b.id condition is what prevents both
-- (Karki,Thapa) and (Thapa,Karki), and also stops each row
-- pairing with itself.

-- WALKING THE WHOLE HIERARCHY needs recursion, not a join.
-- A self join reaches ONE level per join; an arbitrary-depth
-- tree needs a recursive CTE:
WITH RECURSIVE chain AS (
    SELECT id, name, mgr, 1 AS level, name AS path
    FROM   emp WHERE mgr IS NULL          -- anchor
  UNION ALL
    SELECT e.id, e.name, e.mgr, c.level + 1,
           c.path || ' > ' || e.name
    FROM   emp e JOIN chain c ON c.id = e.mgr  -- recurse
)
SELECT level, path FROM chain ORDER BY path;
--  level |          path
--  ------+-------------------------
--      1 | Sharma
--      2 | Sharma > Karki
--      3 | Sharma > Karki > Devi
--      2 | Sharma > Thapa

Derived relations — subqueries, CTEs and views

A DERIVED RELATION is a relation computed from others rather than stored. Four forms, in increasing reusability: 1. DERIVED TABLE (inline subquery in FROM) SELECT * FROM (SELECT ...) AS t · exists only within this query · must be aliased 2. COMMON TABLE EXPRESSION (CTE / WITH clause) WITH t AS (SELECT ...) SELECT * FROM t · named, readable, can be referenced several times · can be RECURSIVE · scope is the single statement 3. VIEW CREATE VIEW v AS SELECT ... · stored in the catalog, reusable across queries · NOT stored as data — recomputed on every access 4. MATERIALIZED VIEW CREATE MATERIALIZED VIEW mv AS SELECT ... · the RESULT is stored on disk · fast to read, but STALE until REFRESHED · trades freshness for speed SUBQUERY TYPES by where they appear: scalar returns one row, one column — usable as a value row returns one row, several columns table returns many rows — used in FROM, IN, EXISTS correlated references the outer query, so it is re-evaluated per outer row CORRELATED vs UNCORRELATED — a real performance difference: -- uncorrelated: the inner query runs ONCE WHERE marks > (SELECT AVG(marks) FROM student) -- correlated: the inner query runs PER OUTER ROW WHERE marks > (SELECT AVG(marks) FROM student s2 WHERE s2.dept = s1.dept) Modern optimisers often rewrite a correlated subquery into a join, but not always. When a query is unexpectedly slow, a correlated subquery in the SELECT list is the first thing to check.

derived.sql

CREATE TABLE marks (roll INT, course CHAR(8), score NUMERIC(5,2));
INSERT INTO marks VALUES
    (101,'ACtE0703',87.5), (101,'AExE0101',72.0),
    (102,'ACtE0703',91.0), (102,'AExE0101',68.5),
    (103,'ACtE0703',55.0), (103,'AExE0101',79.5);

-- ===== 1. DERIVED TABLE in FROM =====
SELECT roll, total
FROM   (SELECT roll, SUM(score) AS total
        FROM   marks GROUP BY roll) AS t
WHERE  total > 150
ORDER  BY total DESC;
--  roll | total
--  -----+--------
--   102 | 159.50
--   101 | 159.50
--  (2 rows)
-- Note: the alias AS t is MANDATORY for a derived table.

-- ===== 2. CTE — same logic, more readable, reusable =====
WITH totals AS (
    SELECT roll, SUM(score) AS total, AVG(score) AS avg_score
    FROM   marks GROUP BY roll
), ranked AS (
    SELECT *, RANK() OVER (ORDER BY total DESC) AS position
    FROM   totals
)
SELECT position, roll, total, ROUND(avg_score,2) AS average
FROM   ranked ORDER BY position, roll;
--  position | roll | total  | average
--  ---------+------+--------+---------
--         1 |  101 | 159.50 |   79.75
--         1 |  102 | 159.50 |   79.75
--         3 |  103 | 134.50 |   67.25
--
-- RANK() gives both 159.50 rows position 1, then SKIPS to 3.
-- DENSE_RANK() would give 1, 1, 2 instead. That difference
-- is a common exam question.

-- ===== 3. correlated subquery: above one's OWN course
-- average =====
SELECT m.roll, m.course, m.score
FROM   marks m
WHERE  m.score > (SELECT AVG(m2.score) FROM marks m2
                  WHERE m2.course = m.course)
ORDER  BY m.course, m.score DESC;
--  roll |  course  | score
--  -----+----------+-------
--   102 | ACtE0703 | 91.00
--   101 | ACtE0703 | 87.50
--   103 | AExE0101 | 79.50
--  (3 rows)
--
-- ACtE0703 avg = 233.5/3 = 77.833  -> 87.5 and 91.0 qualify
-- AExE0101 avg = 220.0/3 = 73.333  -> only 79.5 qualifies

-- ===== 4. the same, rewritten as a JOIN to a derived
-- table — usually FASTER, because the average is computed
-- once per course instead of once per row =====
SELECT m.roll, m.course, m.score
FROM   marks m
JOIN   (SELECT course, AVG(score) AS avg_score
        FROM   marks GROUP BY course) a
       ON a.course = m.course
WHERE  m.score > a.avg_score
ORDER  BY m.course, m.score DESC;
-- identical result, one pass over the aggregate

Verifying the arithmetic: roll 101: 87.5 + 72.0 = 159.5, avg = 79.75 roll 102: 91.0 + 68.5 = 159.5, avg = 79.75 roll 103: 55.0 + 79.5 = 134.5, avg = 67.25 ✔ ACtE0703 average = (87.5 + 91.0 + 55.0) / 3 = 233.5 / 3 = 77.833 above it: 87.5 ✔ and 91.0 ✔ (55.0 is below) AExE0101 average = (72.0 + 68.5 + 79.5) / 3 = 220.0 / 3 = 73.333 above it: 79.5 ✔ (72.0 and 68.5 are below) → 3 rows, matching the output. ✔ RANK vs DENSE_RANK vs ROW_NUMBER on the tie: totals: 159.5, 159.5, 134.5 RANK() → 1, 1, 3 (skips 2) DENSE_RANK() → 1, 1, 2 (no gap) ROW_NUMBER() → 1, 2, 3 (arbitrary tie-break) Choose deliberately: RANK for "joint first, then third", DENSE_RANK for "joint first, then second".

🌍 Go further: knowing the join types is only half of it — the engine also chooses a join algorithm, and that choice dominates performance. Nested loop is O(n×m) but great when one side is tiny; hash join builds a hash table on the smaller side and is the workhorse for large equi-joins; merge join exploits pre-sorted inputs, which is why an index can eliminate a sort. Run EXPLAIN ANALYZE on any join and you will see which was picked and why. Search "nested loop vs hash vs merge join"; the query-cost topic later in this section builds directly on it.

💡 Exam angle: know all join types with their row counts on a small example — that is the fastest way to demonstrate understanding. Be able to state that LEFT JOIN preserves unmatched left rows with NULLs, and explain the WHERE-after-LEFT JOIN trap: a predicate on the right table's columns silently converts it to an inner join, because ON is evaluated during the join and WHERE after. Know why NATURAL JOIN is dangerous (no shared columns → silent Cartesian product) and write a self join for a manager hierarchy, noting that LEFT is needed to keep the root.

Syllabus points

Join types; derived relations

Create a free account to tick topics off, take notes as you read, watch the video lessons and get a day-by-day study plan built around your exam date.