The INTERSECT function returns the rows that result from the intersection of two tables, keeping duplicates.
INTERSECT(
table1,
table2
)
- table: Reference to an existing table or expression that returns a table.
The INTERSECT function returns a table.
The columns are combined according to their position in the table.
The names of the columns of the table returned as a result of the function will be the same as the column names of table1.
Rows common to both tables that appear more than once in table1 will be displayed repeated in the table returned by the function. So, for example, if a row appears 3 times in table1 and once in table2, it will appear 3 times in the result (see example below). This means that the order in which the arguments are added influences the result.
The lineage of the result table is based on the columns of table1, regardless of the lineage of the columns of the second table.
If we start from the following two tables, Countries1 and Countries2:
...and we create a calculated table with the intersection of both:
Intersection = INTERSECT(Countries1, Countries2)
...we can see how the rows common to both tables are shown and how the row corresponding to {United Kingdom, 1} is shown repeated twice, since this row appears in both tables and twice in the first one. It is because of this that the function does not return the same result if the order of the tables is swapped. So, for the expression:
Intersection = INTERSECT(Countries2, Countries1)
...the result is the one shown in the following image:
