Databases| SQL| MySQL| Questions? | Software Development

Mysql Cross Join

Cross Join is also called Cartesian Product Join. The Cross Join in SQL return you a result table in which each row from the first table is combined with each rows from the second table.

Mysql Cross Join

Cross Join is also called Cartesian Product Join. The Cross Join in SQL return you a result table in which each row from the first table is combined with each rows from the second table. In other words, you can say it is the cross multiplication of number of rows in each table. The Syntax used to represent Cross Join in Mysql :

select * from table1 cross join table 2;

Understand with Example

The Tutorial illustrate an example that describe you a cross join in Mysql. To understand cross join, we create a table rose india with required field attribute and data type respectively.

Query to Create Table named roseindia:-

mysql> CREATE TABLE roseindia ( 
            -> Empid int(11),          
             ->firstname varchar(30),  
            -> city varchar(30)        
          -> );
Query OK, 0 rows affected (0.05 sec)

Query to insert data into Table named roseindia:-

The Query insert into added the rows or records into table roseindia.

mysql>  insert into roseindia values(01,'Girish','Nainital');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into roseindia values(02,'Komal','Merrut');
Query OK, 1 row affected (0.00 sec)

mysql>  insert into roseindia values(03,'Amit','Lucknow');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into roseindia values(04,'Sandeep','Lucknow');
Query OK, 1 row affected (0.03 sec)

Query to view data of Table named roseindia:-

To view the detail of records, we use select keyword that show you the records from table roseindia.

mysql> select * from roseindia;

Output:-

The output show the number of records are 4.

+-------+-----------+----------+
| Empid | firstname | city     |
+-------+-----------+----------+
|     1 | Girish    | Nainital |
|     2 | Komal     | Merrut   |
|     3 | Amit      | Lucknow  |
|     4 | Sandeep   | Lucknow  |
+-------+-----------+----------+
4 rows in set (0.00 sec)

Query to Create Table newstrack:-

Now we create a table newstrack, the create table construct a table newstrack with required field attribute and data type as shown in query.

mysql> CREATE TABLE newstrack ( 
        ->     Empid int(11),          
         ->    firstname varchar(10),  
         ->    email varchar(30) 
          -> );
Query OK, 0 rows affected (0.03 sec)

Query to insert data into Table named newstrack:-

The insert into insert the records or rows into table newstrack.

mysql>  insert into newstrack values(01,'Suman','girish@gmail.com');
Query OK, 1 row affected (0.02 sec)

mysql>  insert into newstrack values(02,'Ravi','komal@gmail.com');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into newstrack values(03,'Santosh','Amit@gmail.com');
Query OK, 1 row affected (0.01 sec)

Query to view data of Table named newstrack:-

To view the output from table newstrack, the select keywords return the selective records from table newstrack.

mysql> select * from newstrack;

Output:-

+-------+-----------+------------------+
| Empid | firstname | email            |
+-------+-----------+------------------+
|     1 | Suman     | girish.gmail.com |
|     2 | Ravi      | komal.gmail.com  |
|     3 | Santosh   | Amit.gmail.com   |
+-------+-----------+------------------+
3 rows in set (0.00 sec)

Query to join data of Table's created above using cross join:-

The Query cross join return you the product of rows or records from table 1 and table 2. The cross join return you the entire records specified in roseindia and newstrack.

mysql> select  * from roseindia
    -> cross  join newstrack;

Output:-

+-------+-----------+----------+-------+-----------+------------------+
| Empid | firstname | city     | Empid | firstname | email            |
+-------+-----------+----------+-------+-----------+------------------+
|     1 | Girish    | Nainital |     1 | Suman     | girish.gmail.com |
|     1 | Girish    | Nainital |     2 | Ravi      | komal.gmail.com  |
|     1 | Girish    | Nainital |     3 | Santosh   | Amit.gmail.com   |
|     2 | Komal     | Merrut   |     1 | Suman     | girish.gmail.com |
|     2 | Komal     | Merrut   |     2 | Ravi      | komal.gmail.com  |
|     2 | Komal     | Merrut   |     3 | Santosh   | Amit.gmail.com   |
|     3 | Amit      | Lucknow  |     1 | Suman     | girish.gmail.com |
|     3 | Amit      | Lucknow  |     2 | Ravi      | komal.gmail.com  |
|     3 | Amit      | Lucknow  |     3 | Santosh   | Amit.gmail.com   |
|     4 | Sandeep   | Lucknow  |     1 | Suman     | girish.gmail.com |
|     4 | Sandeep   | Lucknow  |     2 | Ravi      | komal.gmail.com  |
|     4 | Sandeep   | Lucknow  |     3 | Santosh   | Amit.gmail.com   |
+-------+-----------+----------+-------+-----------+------------------+
12 rows in set (0.00 sec)

� View all related tutorials

Related Tags: c query table data join tables sed return tab case row cas rows match to ast ws bot e use

Leave your comment:
Name:
Email:
URL:
Title:
Comments:
Enter Code:
	Note: Emails will not be visible or used in any way, and are not required. Please keep comments relevant. Any content deemed inappropriate or offensive may be edited and/or deleted. No HTML code is allowed. Line breaks will be converted automatically. URLs will be auto-linked. Please use BBCode to format your text.