Cross Joins

A cross join is the Cartesian product of the rows in two tables. It combines each row in the first table with each row in the second table. This type of join is computationally expensive, so you should use this type of join rarely. You can filter the results using the WHERE keyword, which produces an inner join.

An explicit cross join uses the CROSS JOIN keywords. Here's an example of an explicit cross join:

          SELECT *
FROM customer CROSS JOIN employee 

        

This join is equivalent to the following implicit cross join, which doesn't use the CROSS JOIN keywords:

          SELECT *
FROM customer, employee 

        

When using SuiteQL with SuiteAnalytics Connect, the CROSS JOIN keywords aren't supported. To perform a cross join, you can use the implicit notation. You can also use a full outer join to simulate a cross join, as follows:

          SELECT *
FROM customer
FULL OUTER JOIN employee ON 1=1 

        

Using the example database tables, these joins produce 25 result rows:

customer.entityid	customer.email	customer.salesrep	employee.id	employee.entityid
Big Computers	[e-mail]	5	1	Allie Anderson
Big Computers	[e-mail]	5	2	Billy Brown
Big Computers	[e-mail]	5	3	Carol Connors
Big Computers	[e-mail]	5	4	David Davis
Big Computers	[e-mail]	5	5	Eugene Evans
Fantastic Laptops	[e-mail]	3	1	Allie Anderson
Fantastic Laptops	[e-mail]	3	2	Billy Brown
...	...	...	...	...

The first row in the first table is combined with each row in the second table, the second row in the first table is combined with each row in the second table, and so on. Additional result rows have been omitted from this table, but the same pattern continues for the remaining result rows.

Related Topics

• SuiteQL Join Types
• Inner Joins
• Outer Joins

General Notices