The most common use of a subquery is in the form:

<non-subquery operand> <comparison operator> (<subquery>)

Where <comparison operator> is one of:

= > < >= <= <>

For example:

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

At one time the only legal place for a subquery was on the right side of a comparison, and you might still find some old DBMSs which insist on that.

Here is an example of a common-form subquery comparison which you can't do with a join: find all the values in table t1 which are equal to a maximum value in table t2.

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

Here is another example, which again is impossible with a join because it involves aggregating for one of the tables: find all rows in table t1 which contain a value which occurs twice.

SELECT * FROM t1
       WHERE 2 = (SELECT COUNT(column1) FROM t1);