# **Subqueries with ANY or SOME**

## **Grammar Description**

Since the result set returned by the column subquery is one column with multiple rows, these comparison operators (=, >, <, >=, <=, <>) cannot be used directly. In column subqueries, you can use the `ANY`, `SOME` operators and comparison operators in combination:

- `ANY`: Used in conjunction with the comparison operator to indicate that if compared to any value returned by the subquery to `TRUE`, the return result is `true`.
- `SOME`: The alias for `ANY`, which means the same as `ANY`, but is less used.

## **Grammar Structure**

```sql
SELECT column_name(s) FROM table_name WHERE column_name ANY (subquery);
```

## **Example**

```text
create table t1 (a int);
create table t2 (a int, b int);
create table t3 (a int);
create table t4 (a int not null, b int not null);
create table t5 (a int);
create table t6 (a int, b int);
insert into t1 values (2);
insert into t2 values (1,7),(2,7);
insert into t4 values (4,8),(3,8),(5,9);
insert into t5 values (null);
insert into t3 values (6),(7),(3);
insert into t6 values (10,7),(null,7);

mysql> select * from t3 where a <> any (select b from t2);
+------+
| a |
+------+
| 6 |
| 3 |
+------+
2 rows in set (0.00 sec)

mysql> select * from t3 where a <> some (select b from t2);
+------+
| a |
+------+
| 6 |
| 3 |
+------+
2 rows in set (0.00 sec)

mysql> select * from t3 where a = some (select b from t2);
+------+
| a |
+------+
| 7 |
+------+
1 row in set (0.00 sec)

mysql> select * from t3 where a = any (select b from t2);
+------+
| a |
+------+
| 7 |
+------+
1 row in set (0.00 sec)

mysql> select a,b from t6 where a > any ( select a ,b from t4 where a>3);
ERROR 1105 (HY000): subquery should return 1 column
```