Наиболее частое использование подзапроса имеет вид:

non_subquery_operand comparison_operator (subquery)

Где comparison_operator — один из этих операторов:

=  >  <  >=  <=  <>  !=  <=>

Например:

... WHERE 'a' = (SELECT column1 FROM t1)

MySQL также допускает такой конструкт:

non_subquery_operand LIKE (subquery)

В своё время единственным допустимым местом для подзапроса была правая часть сравнения, и вы, возможно, до сих пор встретите некоторые старые СУБД, которые настаивают на этом.

Вот пример сравнения с подзапросом в общем виде, который невозможно выполнить с помощью объединения. Он находит все строки в таблице t1, для которых значение column1 равно максимальному значению в таблице t2:

SELECT * FROM t1
  WHERE column1 = (SELECT MAX(column2) FROM t2);

Вот ещё один пример, который также невозможно выполнить с помощью объединения, так как он включает агрегацию для одной из таблиц. Он находит все строки в таблице t1, содержащие значение, которое встречается дважды в заданном столбце:

SELECT * FROM t1 AS t
  WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

Для сравнения подзапроса со скалярным значением подзапрос должен возвращать скалярное значение. Для сравнения подзапроса со строковым конструктором подзапрос должен быть строковым подзапросом, возвращающим строку с тем же количеством значений, что и строковый конструктор. См. Раздел 15.2.15.5, «Подзапросы со строками».