Cross joins purpose in SQL

Question

I'm having a hard time understanding when a cross join would be useful in SQL. When learning about it, I have seen example that show what it does but those examples haven't been concrete.

An example would be helpful.

Answer 1

A cross join in SQL also known as the Cartesian join is when you combine every record of one table with every record of the other table. So you get all possible combinations of rows of the two tables.

Consider a scenario where you have 2 tables called meals and drinks and now you want to explore what all choices do you have to have your meal along with a drink. In that case a cross join is useful. A cross join is usually useful for exploratory purposes.

Also, cross join is the most basic form of join in SQL upon which when you apply some conditions, you can derive all the other types of joins. Be it natural join, self join or anything.

Also remember that cross joins are not very efficient in operating, the take the complexity of O(m*n) i.e. quadratic complexity