<p>A cross join performs a cartesian product on the tuples of the two sets.</p>

<pre><code>SELECT *
FROM Table1
CROSS JOIN Table2
</code></pre>

<p>Which circumstances render such an SQL operation particularly useful?</p>

You're typically not going to want a full Cartesian product for most database queries.  The whole power of relational databases is that you can apply whatever restrictions you might be interested in to allow you to avoid pulling unnecessary rows from the db.
I suppose one contrived example where you might want that is if you have a table of employees and a table of jobs that need doing and want to see all possible assignments of one employee to one job.