The idea: let the constraints rewrite the query

Semantic query optimization uses the constraints declared on the schema (primary and unique keys, NOT NULL, foreign keys, other rules) to turn a query into a cheaper one that is guaranteed to give the same answer on every database that obeys them. The book (Elmasri & Navathe, section 19.10) gives two examples: a WHERE clause that contradicts a constraint, so the result is empty, and a join made unnecessary by a foreign key. MariaDB does a few such rewrites, the cheap and common ones. The canvas shows the two you can see in EXPLAIN: table elimination and Impossible WHERE.

Left, the query as written: EMPLOYEE E LEFT JOIN DEPARTMENT D on E.Dno = D.Dnumber, selecting only E.Lname and E.Salary, and EXPLAIN with two rows, E ALL and D eq_ref PRIMARY. Right, after table elimination: only EMPLOYEE E, and EXPLAIN with one row, E ALL. Between them the three conditions: outer join, ON covers a unique key of D, no column of D used elsewhere.
When the three conditions hold, MariaDB removes DEPARTMENT from the plan and EXPLAIN lists only EMPLOYEE.

The data on the canvas

The book's eight employees and three departments, plus Kim (added), whose Dno is NULL. The schema has Dnumber as the PRIMARY KEY of DEPARTMENT, Salary INT NOT NULL, and FOREIGN KEY fk_dno (Dno) REFERENCES DEPARTMENT(Dnumber). The book uses Salary > 100000, which matches nobody here, so the page uses Salary > 30000: Kim, Wong, Narayan, Borg and Wallace. Every EXPLAIN row, rewritten query and result on the canvas comes from MariaDB 13.1 run on exactly this data. The id, select_type and key_len columns are left out, and ref is shown without the database name.

Table elimination

MariaDB removes a table from a join when all three hold:

  1. It is the inner table of an outer join (the right side of a LEFT JOIN). The outer rows are kept whether they match or not, so the table can't remove a row.
  2. The ON clause compares every column of one of its unique keys (here the PRIMARY KEY Dnumber) with values from the other tables. Each outer row then matches at most one of its rows, so the table can't duplicate a row.
  3. No column of it is used outside the ON clause: not in the SELECT list, WHERE, GROUP BY, HAVING or ORDER BY.

The table then disappears from EXPLAIN, and SHOW WARNINGS after EXPLAIN EXTENDED shows the rewritten query without the join: select E.Lname, E.Salary from employee E where E.Salary > 30000 (Demo: LEFT JOIN, DEPARTMENT eliminated). It is on by default and controlled by the optimizer_switch flag table_elimination. With SET optimizer_switch='table_elimination=off' the same query gives the same 5 rows, but the plan does an eq_ref lookup in DEPARTMENT for 4 of them, and nothing from those rows is used (Demo: table_elimination=off). Kim needs no lookup either way: MariaDB adds E.Dno is not null to the ON clause, since a NULL can't match a key.

Who writes such a query? Mostly nobody by hand. It comes from views and ORMs that always join a lookup table "in case" a column is wanted, and from wide schemas split into one table per attribute group.

Why the result is the same

Take any employee row. Because Dnumber is unique, it matches zero or one department. With one match the LEFT JOIN outputs the row once; with zero matches (Kim) it outputs the row once with NULLs for D's columns. Either way the employee appears exactly once, and since no D column is shown, the output row is just the employee's own columns. So the join is a no-op, on this data and on any data the key allows. Check it on the canvas: 5 rows with elimination on, the same 5 rows with it off.

Left, EMPLOYEE LEFT JOIN DEPARTMENT on the unique Dnumber: Wong with Dno 5 matches one department, Kim with Dno NULL matches none, and each employee gives exactly one output row, so 9 employees give 9 rows. Right, DEPARTMENT LEFT JOIN EMPLOYEE on the non-unique Dno: Research matches 4 employees and gives 4 rows, so 3 departments give 8 rows instead of 3.
A unique key means 0 or 1 matches, so the row count can't change; a non-unique column can multiply rows, so the table must stay.

When the table stays

QueryCondition that failsPlan
2: also selects D.Dnamea column of D is usedE ALL, D eq_ref PRIMARY
3: DEPARTMENT D LEFT JOIN EMPLOYEE EDno is not unique (8 rows, not 3)D ALL, E ref fk_dno
4: the book's inner joinnot an outer joinE ALL, D eq_ref PRIMARY

(Demo: Dname is used, Demo: Dno is not unique.) The uniqueness does not have to come from a declared key. A derived table grouped by the join column, such as LEFT JOIN (SELECT Dno, COUNT(*) FROM EMPLOYEE GROUP BY Dno) T ON T.Dno = D.Dnumber, is unique on Dno and MariaDB eliminates it too.

The book's examples in MariaDB

Inner join plus foreign key. The book drops DEPARTMENT from EMPLOYEE, DEPARTMENT WHERE Dno = Dnumber because Dno is a foreign key to DEPARTMENT's primary key, and adds Dno IS NOT NULL. MariaDB does not do this: its optimizer does not read foreign keys (InnoDB only checks them when rows are written), and table elimination covers only outer joins. EXPLAIN keeps D with eq_ref, and the result is 4 rows, because the inner join drops Kim. Kim is also why the book's rewrite needs IS NOT NULL. If you want the one-table plan, write the book's rewritten query yourself (Demo: the book's inner join + foreign key).

"No employee earns more than the supervisor". This rule compares two rows, so it would need a CHECK with a subquery, and MariaDB refuses it (ERROR 1970: CHECK does not support subqueries or stored functions). The query runs in full and returns 0 rows.

Impossible WHERE from NOT NULL

Salary is declared NOT NULL, so WHERE Salary IS NULL is false for every row. MariaDB replaces the condition with 0 (SHOW WARNINGS: … from employee where 0), and EXPLAIN shows one row with table NULL and Extra Impossible WHERE: no table is opened (Demo: NOT NULL makes the WHERE impossible). The same message comes from plain contradictions like Salary = 50000 AND Salary = 60000. Ranges found empty, or a primary key value that does not exist, show "Impossible WHERE noticed after reading const tables".

MariaDB does not use CHECK constraints to optimize: with CHECK (Salary BETWEEN 20000 AND 60000), WHERE Salary > 60000 still scans the table and tests every row.

See also How a Query Planner Picks a Plan for cost-based choices, Join Algorithms for how the remaining joins run, and MySQL Index Access for the eq_ref, ref and ALL access types.

See also ER Model to Tables for where the foreign keys and unique keys used here come from in the schema design.

What the page leaves out

MariaDB also eliminates tables inside nested outer joins and whole groups of tables at once, and checks a unique key made of several columns column by column; the page shows one table and a one-column key. Other uses of keys, such as const tables read at optimize time, are not shown. The book's foreign-key join removal and the supervisor rule are not done by MariaDB, and CHECK constraints and triggers are never used by the optimizer. The real EXPLAIN also has id, select_type, key_len and, with EXTENDED, filtered.