Suppose table A has 5 records of data and table B has 6 records. You perform a cross join on these two tables. How many records will the result set consist of?
Explanation
Cross join (also called cartesian join) of two (or more) tables returns the so-called cartesian product of the sets of records from all tables.
It means that cross join matches every record in every table with every record in all other tables.
Therefore, total number of records in the result set is equal to multiplication of numbers of records in all tables over which the query is performed (table1_count * table2_count * ... * tableN_count).

Cross join example query: SELECT * FROM table1, table2, table3
It will return the result set consisting of all possible combinations of columns from all source tables.
Theory
  • In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
  • 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 CARTESIAN JOIN or CROSS JOIN is as follows:
    SELECT table1.column1, table2.column2...
    FROM  table1, table2 [, table3 ]
    

    Read more: SQL - CARTESIAN or CROSS JOINS
  • Example:

    Consider the following two tables,
    (a) CUSTOMERS table is as follows:
    | ID | NAME     |
    +----+----------+
    |  1 | Ramesh   |
    |  2 | Khilan   |
    
    (b) Another table is ORDERS as follows:
    |OID  | DATE  | C_ID | AMOUNT |
    +-----+-------+------+--------+
    | 102 | 10-08 |    3 |   3000 |
    | 100 | 10-08 |    3 |   1500 |
    | 101 | 11-20 |    2 |   1560 |
    
    Now, let us join these two tables using INNER JOIN as follows:
    SQL> SELECT  ID, NAME, AMOUNT, DATE
         FROM CUSTOMERS, ORDERS;
    
    This would produce the following result:
    | ID | NAME     | AMOUNT | DATE  |
    +----+----------+--------+-------+
    |  1 | Ramesh   |   3000 | 10-08 |
    |  1 | Ramesh   |   1500 | 10-08 |
    |  1 | Ramesh   |   1560 | 11-20 |
    |  2 | Khilan   |   3000 | 10-08 |
    |  2 | Khilan   |   1500 | 10-08 |
    |  2 | Khilan   |   1560 | 11-20 |
    
SQL
SQL Quiz
Start Quiz

or Read more about SQL Quiz

Follow CodeGalaxy

Mobile Beta

Get it on Google Play
Send Feedback