Joe Celko's SQL for Smarties, 3rd Edition

33.7. Prefer Joins over Nested Queries

A nested query is hard to optimize. Optimizers try to “flatten” nested queries so they can be expressed as JOINs and the best order of execution can be determined. Consider the database:

CREATE TABLE Authors
(author_nbr INTEGER NOT NULL PRIMARY KEY,
 authorname CHAR(50) NOT NULL);

CREATE TABLE Titles
(isbn CHAR(10)NOT NULL PRIMARY KEY,
 title CHAR(50) NOT NULL
 advance_amt DECIMAL(8,2) NOT NULL);

CREATE TABLE TitleAuthors
(author_nbr INTEGER NOT NULL REFERENCES Authors(author_nbr),
 isbn CHAR(10)NOT NULL REFERENCES Titles(isbn),
 royalty_rate DECIMAL(5,4) NOT NULL,
 PRIMARY KEY (author_nbr, isbn));

This query finds authors who are getting less than 50% royalties:

SELECT author_nbr FROM Authors WHERE author_nbr ...