Subqueries
A subquery is a SELECT statement within another statement. In most cases, subqueries can be used in place of a join, and have the advantage of being able to query the same table twice, which is not possible with a join.
Note
Subqueries are often easier to read over joins, but joins get better performance.
SELECT book_title, book_pages
FROM books
WHERE book_pages <
(SELECT avg(book_pages) FROM books);