SQLite CROSS JOIN

How cross join works in SQLite?

When the two tables have no relation in any way, SELECT would produce a more fundamental kind of join , which is called a cross join or Cartesian join. The Cartesian join is one of the fundamental relational operations. It is a kind of join that results in the combination of all rows from the first table with all rows in the second.

In SQLite, the CROSS JOIN produced a result set which is the product of rows of two associated tables when no WHERE clause is used with CROSS JOIN.

In this join, the result set appeared by multiplying each row of the first table with all rows in the second table if no condition introduced with CROSS JOIN.

This kind of result is called as Cartesian Product.

In SQLite, the CROSS JOIN behaves like JOIN and INNER JOIN of without using any condition.

In standard SQL the difference between INNER JOIN and CROSS JOIN is ON clause can be used with INNER JOIN on the other hand ON clause can't be used with CROSS JOIN.

Syntax:

SELECT [ * | table1.col1,table1.col2,...,
table2.col1,table2.col2,...]
FROm table1
[CROSS JOIN ] table2
condition;

Example:

Here is the sample table.

table - table_a
id          des1        des2
----------  ----------  --------
100         desc11      desc12
101         desc21      desc22
102         desc31      desc32

table - table_b
id          des3        des4
----------  ----------  --------
101         desc41      desc42
103         desc51      desc52
105         desc61      desc62

Here is the example.

SELECT *
FROM table_a
CROSS JOIN table_b;

Her is the output.

id          des1        des2        id          des3        des4
----------  ----------  ----------  ----------  ----------  -------
100         desc11      desc12      101         desc41      desc42
100         desc11      desc12      103         desc51      desc52
100         desc11      desc12      105         desc61      desc62
101         desc21      desc22      101         desc41      desc42
101         desc21      desc22      103         desc51      desc52
101         desc21      desc22      105         desc61      desc62
102         desc31      desc32      101         desc41      desc42
102         desc31      desc32      103         desc51      desc52
102         desc31      desc32      105         desc61      desc62

Pictorial Presentation:

Example: SQLite CROSS JOIN with WHERE clause

In the following example, CROSS JOIN have been executed with WHERE clause and it is similar to the INNER JOIN with ON clause.

SELECT *
FROM table_a
CROSS JOIN table_b
WHERE table_a.id=table_b.id;

Here is the output.

id          des1        des2        id          des3        des4
----------  ----------  ----------  ----------  ----------  -------
101         desc21      desc22      101         desc41      desc42

CROSS JOINS: SQL and other Relational Databases

• SQL CROSS JOIN
• MySQL CROSS JOIN
• PostgreSQL CROSS JOIN
• Oracle CROSS JOIN

Previous: LEFT OUTER JOIN
Next: NATURAL JOIN