Subqueries (EXISTS, IN, ANY, ALL )

A subquery is a select statement used inside another select statement. As an example, this statement selects records from tblA that have a value that is in tblB: SELECT . . . FROM tblA WHERE

v IN (SELECT w FROM tblB) You can use subqueries in this way:

EXISTS (SELECT * FROM . . . )

True if the subquery returns one or more records. You can also write NOT EXISTS (. . .)

v IN (SELECT w FROM . . . )

True if the set of w's returned by the query contains the value v. You can also write v NOT IN (. . .)

v IN (3, 7, a+b)

True if v is in the list of values 3, 7, a+b. This is not really a subquery, because we write the list of values explicitly.

v > ANY (SELECT w FROM . . . )

True if the value v is larger than one or more of the w's returned by the query. You can also write v=, v>, v>=, etc. You can write SOME instead of ANY. The meaning to the computer is the same.

v > ALL (SELECT w FROM . . . )

True if the value v is larger than all of the w's returned by the query. You can also write v=, v>, v>=, etc.

An alternative to v > ANY is:

v > (SELECT Min(w) FROM . . . )

Similarly an alternative to v > ALL is:

v > (SELECT Max(w) FROM . . . )

EXISTS (SELECT . . . ) AND v > ANY (SELECT . . .)

You can combine subqueries in the same way as other logical expressions.

In summary, you can use a subquery in a logical expression. You cannot join a subquery with other subqueries, group it, etc. In order to do such things, you have to store the subquery as a named query (we show an example in section 7.4).

Example: Used and free rooms

The first example in Figure 7.3 shows a list of rooms with an indication of those used in the period 23/10 to 26/10.

In principle the query is quite simple. We query tblRoom and select the roomID and a computed value. The computed value makes the trick. It tests whether the room has a roomState record in the period 23/10 to 26/10. The result will be True or False. We give the result the name Used.

In this case we show the result as Yes/No. We have set Property -> Format to Yes/No for the Used field.

Notice the comparison operator BETWEEN. It is part of standard SQL, but not of VBA. This is one of the few exceptions where an expression in SQL doesn't work in VBA. The other way around, lots of VBA expressions don't work in standard SQL although they work in Access SQL.

Example 2: List of free rooms using NOT EXISTS The second example lists only the free rooms. Again we select roomID from tblRoom, but in the WHERE clause we check that the room has no roomStates in the period we consider.

Example 3: List of free rooms using NOT IN The third example also lists the free rooms, but selects them in a different way. In the WHERE clause the subquery lists all roomStates in the period we consider. The WHERE clause checks that the roomID from tblRoom is not in this list.

Correlated queries

SQL specialists talk about correlated queries. The subquery is correlated with the main query if it references fields in the main query. In the figure, the first two queries have correlated subqueries while qryTest3 has not.

Example 4: DISTINCT values only

A query may produce a list of records with duplicates. QryTest 4 shows an example. It extracts all roomState records in the period we consider, and selects the roomID. Since many rooms are occupied multiple times in this period, the same roomID will appear many times.

QryTest 4A has added the word DISTINCT. This causes SQL to remove all duplicates.

Example 5: Self-correlation

The fifth example shows how we can select records based on other records in the same table.

The example is from the hotel system. A guest may have booked several rooms for different periods. When guests arrive, we want the receptionist to see a list of the rooms booked for the arrival date.

The query looks at each roomState record to see whether it must be included in the list. It must be if the date of this roomState is the arrival date for the corresponding stay. The subquery finds the arrival date by looking at all roomStates that belong to this stay. The arrival date is the first date among these roomStates.

Note how the subquery selects tblRoomState. It gives the table an alias-name (T2) in order to compare these room states with those from the outermost query. This is called a self-correlation.

qryTest2:

SELECT roomID FROM tblRoom WHERE

NOT EXISTS (SELECT * FROM tblRoomState

WHERE tblRoomState.roomID = tblRoom.roomID AND (tblRoomState.date BETWEEN #10/23/02# AND #10/26/02#));

qryTest:

SELECT roomID,

EXISTS (SELECT * FROM tblRoomState

WHERE tblRoomState.roomID = tblRoom.roomID AND (tblRoomState.date BETWEEN #10/23/02# AND #10/26/02#)) AS Used

FROM tblRoom;

Fig 7.3 Subqueries (Exists, In, etc.)

qryTest3:

SELECT roomID FROM tblRoom WHERE

roomID NOT IN (SELECT roomID FROM tblRoomState WHERE tblRoomState.date BETWEEN #10/23/02# AND #10/26/02#);

Room list with indication whether the room was used between 23/10 and 26/10.

Rooms free between 23/10 and 26/10 Rooms free between 23/10 and 26/10

qryTest5:

SELECT stayID, roomID, [date] FROM tblRoomState WHERE [date] =

(SELECT Min([date]) FROM tblRoomState AS T2 WHERE tblRoomState.stayID = T2.stayID)

ORDER BY stayID;

qryTest4:

SELECT roomID FROM tblRoomState

WHERE tblRoomState.date BETWEEN #10/23/02# AND #10/26/02#;

qryTest4A:

SELECT DISTINCT roomID FROM tblRoomState WHERE tblRoomState.date BETWEEN #10/23/02# AND #10/26/02#;

Room states in the period. Same roomID occurs multiple times Duplicates removed

Rooms for the arrival date, i.e. the first date of the stay.