SQL - Cross Join / Cartesian Product / Cross Product

Data System Architecture

About

Relational Operator - Cross Product in SQL

Example

Data

The data below is used in the explicit and implicit example

table1
(inner set)
table2
(outer set)
Column ID Column ID Column ID_2
A A
B B A
D C C

And we want:

ID_table1  ID_table2
---------- ----------
A          A
A          B
A          C
B          A
B          B
B          C
D          A
D          B
D          C

9 rows selected.

We have 3 rows from table1 to multiply by 3 rows in the table2.

Real example of cross-join in this article : OWB - How to load a fact table for a data quality cube ?

Explicit

SELECT
	  table1.id "ID_table1"
	, table2.id "ID_table2"
	FROM
	  table1
	  CROSS JOIN table2;

Implicit

  • There is no join condition
select table1.id "ID_table1", table2.id "ID_table2"
from table1, table2
  • in case of when the application controls that you have set a join condition, you can add the 1=1 predicate.
select table1.id "ID_table1", table2.id "ID_table2"
from table1, table2
where 1=1;





Discover More
Bobj Chasm Trap
BOBJ - Chasm Traps (converging many to one joins)

Chasm Traps is see as a Join Path problem or data modeling issue (convergence type ) which returns more data than expected by returning a cartesian product. A chasm trap is a type of join path...
Star Schema
Dimensional Data Modeling - Dimension (Perspective)

A dimension is a part of a dimensional schema and provide the basis for analyzing data through slicing and dicing A dimension is just a set of descriptif attribute that are generally grouped: in a...
Data System Architecture
Dimensional Data Operation - Data Densification (sparse to dense data) - Preservation of the dimensions

You may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. The densification is also : known as thepreservation...
Obiee Fact Cross Join
OBIEE - Densification with the fact-based fragmentation capabilities

You may want to view the data in dense form, with rows for all combination of dimension values displayed even when no fact data exist for them. The preservation of the dimensions is is also well known...
Obiee Ceim
OBIEE - the Cross/Cartesian Join

To perform a cross join (often between the dimension), you have two ways : in the repository or with the Obiee logical sql To know what sort of join is is a cross join, follow this link : ...
Owb Load Fact Mdm
OWB - How to load a fact table for a data quality cube ?

In one mapping, you can check all the data rules against a table and you can load the results in one fact table. On the check table STG_VENDOR_SAP4 (in the bottom left side), I have set the data rules...
Card Puncher Data Processing
Oracle Database - Cartesian Joins Operation

A Cartesian join is used when one or more of the tables does not have any join conditions to any other tables in the statement. The optimizer joins every row from one data source with every row from the...
Card Puncher Data Processing
Python - List

Lists are used to store a collection of different pieces of information in a sequence order of under a single variable name. A list can be used to implement a . A list is mutable whereas a tuple is not....
Card Puncher Data Processing
Python - Set

Implementation of a set data structure in Python. Sets are mutable. There is a non-mutable version of set called frozenset. The elements of a set arenot mutable. A set then cannot contain a list since...
Card Puncher Data Processing
R - Join Data Frame (Merge)

where: the “by” parameters specifies the join column. Default: join by common variable names The by.x and by.y parameters must be used if the matching variables have different names the “all”...



Share this page:
Follow us:
Task Runner