A Use Case of CROSS JOINS in SQL

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:

image

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.

Mathematical formula for Combinations

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

Advertisements

Your valuable comments are highly honoured..!!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s