CROSS

MySQL

Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
CROSS JOIN Customers
LIMIT 5;

Output

| OrderID | CustomerName | OrderDate |
|---------|--------------|-----------|
| 1       | Antonio      | 2020-07-07|
| 2       | Maria        | 2020-08-10|
| 3       | Carlos       | 2020-07-15|
| 4       | Juan         | 2020-08-20|
| 5       | Ana          | 2020-07-22|

Explanation

The given SQL query demonstrates the use of the CROSS JOIN clause. CROSS JOIN returns the Cartesian product of rows from the tables involved in the join. The resulting table will have rows that combine each row from the first table with each row from the second table. The LIMIT 5 clause limits the result to the first five records.

PostgreSQL

Example

CREATE TEMPORARY TABLE IF NOT EXISTS table1 ( column1 INT );
CREATE TEMPORARY TABLE IF NOT EXISTS table2 ( column2 INT );

INSERT INTO table1 (column1)
VALUES (1), (2), (3);

INSERT INTO table2 (column2)
VALUES (4), (5);

SELECT * FROM table1
CROSS JOIN table2;

Output

 column1 | column2
---------+---------
       1 |       4
       1 |       5
       2 |       4
       2 |       5
       3 |       4
       3 |       5

Explanation

CROSS JOIN returns the Cartesian product of rows from the tables in the join. In this example, every row from table1 is combined with every row from table2. There are 3 rows in table1 and 2 rows in table2, so the result is a table with 6 rows.

SQL Server

Example

SELECT *
FROM TableA
CROSS JOIN TableB

Output

ID    Name    Value
----------------------
 1     A       X
 1     B       Y
 2     A       X
 2     B       Y
 3     A       X
 3     B       Y

Explanation

The CROSS JOIN in SQL Server returns the cartesian product of the sets of records from the two or more joined tables. This means that it will return all the possible combinations of rows from the joined tables. In this example, each row from TableA is combined with each row from TableB.

Oracle

Example

SELECT *
FROM Orders
CROSS JOIN Customers;

Output

OrderID | CustomerID | OrderDate   | CustomerID |   ContactName    |    Country
  10308 |          2 | 1996-09-18  |          1 | Maria Anders     | Germany
  10309 |         37 | 1996-09-19  |          1 | Maria Anders     | Germany
  10310 |         77 | 1996-09-20  |          1 | Maria Anders     | Germany

Explanation

CROSS JOIN returns the Cartesian product of rows from tables in the join. In this example, it returns every combination of records from Orders and Customers.