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)

Mysql Cross Join Statement

Mysql Cross Join Statement

Mysql Cross Join Statement

Tutorials