Mysql Cross Join Statement
Mysql Cross Join Statement is used to return the product of rows or records
from both table.
Understand with Example
The Tutorial illustrate an example from 'Mysql Cross Join Statement '. To
understand this example we create a table roseindia. The create table construct
a table 'roseindia' with required fieldname and data type.
Query to Create Table named roseindia:-
mysql> create table roseindia(Empid int,firstname varchar(30));
Query OK, 0 rows affected (0.05 sec)
|
Query to insert data into Table named roseindia:-
The insert into is used to add the records or
rows into the table 'roseindia'.
mysql> insert into roseindia values(01,'Girish');
Query OK, 1 row affected (0.02 sec)
mysql> insert into roseindia values(02,'Komal');
Query OK, 1 row affected (0.00 sec)
mysql> insert into roseindia values(03,'Amit');
Query OK, 1 row affected (0.02 sec)
mysql> insert into roseindia values(04,'Sandeep');
Query OK, 1 row affected (0.03 sec)
|
Query to view data of Table named roseindia:-
mysql> select * from roseindia;
|
Output:-
+-------+-----------+
| Empid | firstname |
+-------+-----------+
| 1 | Girish |
| 2 | Komal |
| 3 | Amit |
| 4 | Sandeep |
+-------+-----------+
4 rows in set (0.00 sec)
|
Query to Create Table newstrack:-
Now we create another table newstrack .The create
table is used to create a table newstrack with field name and datatypes.
mysql> create table newstrack(Empid int, lastname varchar(30));
Query OK, 0 rows affected (0.03 sec)
|
Query to insert data into Table named newstrack:-
Insert into add the records or rows into the
table 'newstrack'.
mysql> insert into newstrack values(01,'Suman');
Query OK, 1 row affected (0.02 sec)
mysql> insert into newstrack values(02,'Ravi');
Query OK, 1 row affected (0.01 sec)
mysql> insert into newstrack values(03,'Santosh');
Query OK, 1 row affected (0.01 sec)
mysql> insert into newstrack values(04,'Vinod');
Query OK, 1 row affected (0.03 sec)
|
Query to view data of Table named newstrack:-
mysql> select * from newstrack;
|
Output:-
+-------+-----------+
| Empid | firstname |
+-------+-----------+
| 1 | Suman |
| 2 | Ravi |
| 3 | Santosh |
| 4 | Vinod |
+-------+-----------+
4 rows in set (0.00 sec)
|
Query to join the data of the above two tables named
roseindia and newstrack using crossjoin:-
The below query return you all the records or rows from
both table name roseindia and newstrack.
mysql> select * from roseindia cross join newstrack order by roseindia.empid;
|
Output:-
+-------+-----------+-------+-----------+
| Empid | firstname | Empid | firstname |
+-------+-----------+-------+-----------+
| 1 | Girish | 1 | Suman |
| 1 | Girish | 4 | Vinod |
| 1 | Girish | 3 | Santosh |
| 1 | Girish | 2 | Ravi |
| 2 | Komal | 2 | Ravi |
| 2 | Komal | 1 | Suman |
| 2 | Komal | 4 | Vinod |
| 2 | Komal | 3 | Santosh |
| 3 | Amit | 3 | Santosh |
| 3 | Amit | 2 | Ravi |
| 3 | Amit | 1 | Suman |
| 3 | Amit | 4 | Vinod |
| 4 | Sandeep | 4 | Vinod |
| 4 | Sandeep | 3 | Santosh |
| 4 | Sandeep | 2 | Ravi |
| 4 | Sandeep | 1 | Suman |
+-------+-----------+-------+-----------+
16 rows in set (0.00 sec)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
