O'Reilly logo

Discovering SQL: A Hands-On Guide for Beginners by Alex Kriegel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

A SUBQUERY OR A JOIN?

Subqueries in general (and nested subqueries in particular) can be alternatively formulated as JOIN statements. Other questions can only be answered with subqueries. Normally, there won't be any performance difference between a statement that includes a subquery and an equivalent JOIN version that does not. Nevertheless, in some cases, especially where an EXISTS operator is used, a JOIN yields better performance. A correlated subquery will almost always perform worse than a semantically equivalent JOIN, even though modern RDBMS's optimizers would likely create identical execution plans (see Chapter 9 for more information on query optimization) for either query type.

There is another important distinction. Unlike when using a JOIN, you cannot return a value retrieved in a subquery to your client; it's for internal consumption only.

The next chapter is dedicated to a detailed discussion of JOIN. While the syntax of a JOIN query is no less verbose, it can better optimize performance and is more readable than a nested subquery (though some RDBMSs internally convert subqueries into JOINs).

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required