Joe Celko's SQL for Smarties, 3rd Edition

33.13. Know Your Optimizer

One of the best tricks is to know what your optimizer favors. It is often the case that one query construction will have special code written for it that an equivalent query construction does not. Consider this simple adjacency list model of a tree:

CREATE TABLE Tree
(node_id CHAR(2) NOT NULL,
 parent_node_id CHAR(2), -- null is root node
 creation_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);

Let’s try to group all the parent_node_ids and display the creation_date of the most recent subordinate under that parent_node_id. This is a straightforward query that can be done with the following statement:

SELECT node_id, T1.parent_node_id, T1.creation_date FROM Tree AS T1 WHERE NOT EXSTS (SELECT * FROM Tree AS T2 WHERE T2.parent_node_id ...