Tutorials Point


  Learning Basic SQL
  Advanced SQL
  SQL Functions
  SQL Useful Resources
  Selected Reading

© 2011 TutorialsPoint.COM


  Home     References     Discussion Forums     About TP  

SQL CARTESIAN or CROSS JOINS


previous next AddThis Social Bookmark Button


The CARTESIAN JOIN or CROSS JOIN returns the cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.

Syntax:

The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM  table1, table2 [, table3 ]

Example:

Consider following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

+-----+---------------------+-------------+--------+
| OID | DATE                |          ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Now let us join these two tables using INNER JOIN as follows:

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS, ORDERS;

This would produce following result:

+----+---------+--------+---------------------+
| ID | NAME    | AMOUNT | DATE                |
+----+---------+--------+---------------------+
|  1 | Ramesh  |   3000 | 2009-10-08 00:00:00 |
|  1 | Ramesh  |   1500 | 2009-10-08 00:00:00 |
|  1 | Ramesh  |   1560 | 2009-11-20 00:00:00 |
|  1 | Ramesh  |   2060 | 2008-05-20 00:00:00 |
|  2 | Ramesh  |   3000 | 2009-10-08 00:00:00 |
|  2 | Ramesh  |   1500 | 2009-10-08 00:00:00 |
|  2 | Ramesh  |   1560 | 2009-11-20 00:00:00 |
|  2 | Ramesh  |   2060 | 2008-05-20 00:00:00 |
|  3 | kaushik |   3000 | 2009-10-08 00:00:00 |
|  3 | kaushik |   1500 | 2009-10-08 00:00:00 |
|  3 | kaushik |   1560 | 2009-11-20 00:00:00 |
|  3 | kaushik |   2060 | 2008-05-20 00:00:00 |
|  4 | kaushik |   3000 | 2009-10-08 00:00:00 |
|  4 | kaushik |   1500 | 2009-10-08 00:00:00 |
|  4 | kaushik |   1560 | 2009-11-20 00:00:00 |
|  4 | kaushik |   2060 | 2008-05-20 00:00:00 |
|  5 | Hardik  |   3000 | 2009-10-08 00:00:00 |
|  5 | Hardik  |   1500 | 2009-10-08 00:00:00 |
|  5 | Hardik  |   1560 | 2009-11-20 00:00:00 |
|  5 | Hardik  |   2060 | 2008-05-20 00:00:00 |
|  6 | Komal   |   3000 | 2009-10-08 00:00:00 |
|  6 | Komal   |   1500 | 2009-10-08 00:00:00 |
|  6 | Komal   |   1560 | 2009-11-20 00:00:00 |
|  6 | Komal   |   2060 | 2008-05-20 00:00:00 |
|  7 | Muffy   |   3000 | 2009-10-08 00:00:00 |
|  7 | Muffy   |   1500 | 2009-10-08 00:00:00 |
|  7 | Muffy   |   1560 | 2009-11-20 00:00:00 |
|  7 | Muffy   |   2060 | 2008-05-20 00:00:00 |
+----+---------+--------+---------------------+


previous next Printer Friendly



  

Advertisement

Online Image Processing

Indian Baby Names