# Scalar-Valued, Existential Testing and Quantified Comparison Subquery

+ Scalar-valued subquery

```
SELECT name, street, city, (SELECT COUNT(*) FROM log WHERE log.userID = users.ID)  LoginCnt FROM users WHERE state = 'New Mexico';
```

+ Existential testing subquery

```
SELECT name, street, city FROM users WHERE state = 'New Mexico' AND userID IN (SELECT userID FROM allowlist WHERE allowlist.userID = users.userID);
```

+ Quantified comparison subquery

```
SELECT name, street, city FROM users WHERE users.state = 'New Mexico' AND '2024-06-18' <ALL (SELECT logDate FROM log WHERE log.userID = users.userID);
```

------------------------------------------------------------------------------
Subqueries can be categorized based on the type of data they return. There are three common types of subqueries:

1. Scalar-Valued Subqueries: These are relational queries that produce a single-column table. At runtime, they can return zero, one, or more rows. If zero rows are returned, the corresponding scalar value is NULL. For a single row, the scalar value is the column value. If more than one row is returned, a runtime error is generated. A typical scalar-valued query computes a scalar aggregate and is thus guaranteed to return a single row. For example:

```
SELECT name, street, city, (SELECT COUNT(*) FROM log WHERE log.userID = users.ID)  LoginCnt FROM users WHERE state = 'New Mexico';
```

2. Existential Tests: In these queries, an arbitrary query is encapsulated using EXISTS(Q), NOT EXISTS(Q), IN(Q), or NOT IN(Q). The result is a boolean value indicating whether the query result is non-empty. For example:

```
SELECT name, street, city FROM users WHERE state = 'New Mexico' AND userID IN (SELECT userID FROM allowlist WHERE allowlist.userID = users.userID);
```

3. Quantified Comparisons: These involve checking a scalar expression against a set of values returned by a single-column table. The structure is (expr) (cmp) ALL/ANY Q. The comparison can be either existential (ANY) or universal (ALL), and the result is a boolean value indicating the outcome of the quantified comparison. For example:

```
SELECT name, street, city FROM users WHERE users.state = 'New Mexico' AND '2024-06-18' <ALL (SELECT logDate FROM log WHERE log.userID = users.userID);
```