What is SQL CROSS JOIN
The SQL CROSS JOIN is the join which results out all the possible combination of the rows of two tables. It means the number of rows in the first table multiplies by the number of rows in the second table. You can call this product as the cartesian product of the rows of both the tables.
All the rows of the first table combine with all the rows of the second table. If there are more rows in both the tables, the Cross join results out a large table.
How to Use SQL CROSS JOIN
Now, if you want to know how to use full outer join in SQL, you have to use the two tables and also the syntax given below. From these two tables, you have to make all the possible combinations for rows of these tables.
Syntax
|
1 2 3 4 |
SELECT column_name FROM table1 CROSS JOIN table2 ON table1.column_name = table2.column_name; |
The above syntax result out all the possibilities rows from both the tables.
Example of SQL RIGHT OUTER JOIN
Now, let us create the two tables for Purchaser and Seller as given below to understand the example of full outer join in SQL server.
Below are the examples that contains the statement and the output of the full outer join of the given tables.
SQL CROSS Join Example Using the Select Statement
There are two tables given below to show the join. The first table is Purchaser table and second is the Seller table.
The first table contains the list of sellers.
Table 1: Seller
| Id | Seller_Name | Seller_Email |
|---|---|---|
| 1001 | Big Show | [email protected] |
| 1002 | Gem | [email protected] |
| 1003 | Matt | [email protected] |
The second table contains the list of the purchasers.
Table 2: Purchaser
| Purchaser_ID | Purchaser_Name | Plot_No | Service_Id |
|---|---|---|---|
| 1 | Sam | 12 | 1001 |
| 2 | Pill | 13 | 1002 |
| 3 | Don | 14 | 1003 |
| 4 | Brock | 15 | 1004 |
Now, let’s combine these two tables using the example given below.
Example
|
1 2 3 |
SELECT Service_Id, Seller_Name, Purchaser_Name, Plot_No FROM Purchaser CROSS JOIN Seller; |
Output
| Service_Id | Seller_Name | Purchaser_Name | Plot_No |
|---|---|---|---|
| 1001 | Big Show | Sam | 12 |
| 1001 | Big Show | Pill | 13 |
| 1001 | Big Show | Don | 14 |
| 1001 | Big Show | Brock | 15 |
| 1002 | Gem | Sam | 12 |
| 1002 | Gem | Pill | 13 |
| 1002 | Gem | Don | 14 |
| 1002 | Gem | Brock | 15 |
| 1003 | Matt | Sam | 12 |
| 1003 | Matt | Pill | 13 |
| 1003 | Matt | Don | 14 |
| 1003 | Matt | Brock | 15 |
You must also read.
- SQL INNER JOIN statement
- SQL LEFT OUTER JOIN statement
- SQL RIGHT OUTER JOIN statement
- SQL CROSS JOIN statement
Reference
Cross Join Doc