A cross join is nearly always a bad thing, because the result-set is massively larger than the joined tables themselves. Unless the tables being joined are small, you get performance and memory problems. I've never used a cross join except when playing around.
I've rarely had to use a full outer join; I think that if you encounter that need, it bespeaks a problem with your schema. But sometimes DBAs are extremely resistant to schema changes in production, so you have to work with the schema you have.
FWIW, SQL isn't the core of my trade; I'm not a data analyst, I'm just a normal dev with SQL as one of the things in my toolkit.