pivotronics

Use case of SQL CROSS JOIN: Although CROSS JOIN is available in SQL, you rarely find any instance where you can use it. Here is one use case for CROSS JOIN in SQL:

Example with fictitious data: Assuming a tournament of Soccer to happen among four teams, what are the all possible matches between each of these teams, such that each team gets a chance to play with the remaining other three teams..?

You can use the CREATE and INSERT statements in the below screenshot 1 to create this table. Then run the SQL SELECT statement as in the screenshot 2 to get the desired results:

Screenshots:

Here is the SQL script containing all the statements that can be copied: SQL Cross Joins Use Case

Caveat: The CROSS JOIN here actually returns all permutation of the matches which includes the repetition of the combinations giving significance to the order of the teams in a match. i.e., a match between Brazil Vs Italy and Italy Vs Brazil will be treated as separate.

Solution: The predicate in the WHERE clause takes care of this and avoids any such repeated combinations and lets you disregard any significance attached to the order of the teams in a match.

Happy learning..!! Don’t forget you leave your comments friends..!!