Comment by pdubs

I've only used them while using certain frameworks to dynamically generate queries referencing multiple tables. They end up getting turned into an implicit inner join though. The following queries are equivalent.

  SELECT a.*, b.*
  FROM a CROSS JOIN b
  WHERE a.id = b.a_id
  
  SELECT a.*, b.*
  FROM a, b
  WHERE a.id = b.a_id

  SELECT a.*, b.*
  FROM a INNER JOIN b ON a.id = b.a_id

IIRC .NET's Entity Framework will (or at least used to) generate queries similar to this as well.