Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Sql Sqljoin Mysql Cross Join

Related Tutorials


 
 

Share on Google+Share on Google+

Mysql Cross Join

Advertisement
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)
Advertisements

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: January 16, 2009

Related Tutorials

Discuss: Mysql Cross Join   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:1
sachin
March 21, 2012
nice example

great way to learn
DMCA.com