Retrieving the Data - Database Design Manual: Using MySQL for Windows - page 69

Figure 6.20 Selecting the Home pages.

Figure 6.20 shows the above script in action. It also shows the three different IDs that
begin with Home ; 1, 2 and 7. We will now use those IDs to select the rows:

SELECT id, title
FROM webpage

WHERE id IN (1,2,7)

Running the above query will produce the same results as shown in Figure 6.20 but they
have been selected in a completely different way. We can select the rows that are not in that
ID set by adding a NOT to the clause as follows:

SELECT id, title
FROM

webpage

WHERE id NOT IN (1,2,7)

This will return the complementary set of rows. Note where the NOT keyword appears in
the clause. As different positions cause different result sets, it is always a good idea to run
the query through an SQL client before you use it in a website so that you can ensure that
you are going to get the results that you expect.

Subqueries

Version 4.1 of MySQL now includes the ability to perform subqueries. A subquery is a query
that is run to derive a value that is fed into the main query. For instance, the following query
was used to extract the three rows shown in Figure 6.20:

SELECT id, title
FROM webpage

WHERE title LIKE “Home%”