A cross join is a join operation that produces the Cartesian product of two or more tables.
In Math, a Cartesian product is a mathematical operation that returns a product set of multiple sets.
For example, with two sets A {x,y,z} and B {1,2,3}, the Cartesian product of A x B is the set of all ordered pairs (x,1), (x,2), (x,3), (y,1) (y,2), (y,3), (z,1), (z,2), (z,3).
Suppose the A table has n rows and the B table has m rows, the result of the cross join of the A and B tables have n x m rows.
Syntax:
The syntax of the CROSS JOIN in SQL will look like the below syntax:
Or we can use the following syntax instead of the previous one. This syntax does not include the CROSS JOIN keyword; only we will place the tables that will be joined after the FROM clause and separated with a comma.
The result set does not change for either of these syntaxes. In addition, we must notice one point about the CROSS JOIN. Unlike the INNER JOIN, LEFT JOIN and FULL OUTER JOIN, the CROSS JOIN does not require a joining condition
Example:
Let's consider the below table.
SQL Code:
Output:
MealName |
MenuName |
Non Veg |
Chicken Tikka |
Starter |
Chicken Tikka |
Fast Food |
Chicken Tikka |
Non Veg |
Egg Omlet |
Starter |
Egg Omlet |
Fast Food |
Egg Omlet |
Non Veg |
Chicken Noodles |
Starter |
Chicken Noodles |
Fast Food |
Chicken Noodles |
No comments:
Post a Comment