INTERSECT operation
The INTERSECT operation combines the results of two queries into a single result that comprises all the rows common to both queries. Whereas a UNION operation is a logical OR, INTERSECT is a logical AND.
INTERSECT
In an INTERSECT
[DISTINCT] operation, if a tuple t appears m (> 0) times in the first
input table, and the same tuple t appears n (> 0) times in the second
input table, then that tuple t appears only once in the output table.
{0,1,2,2,2,2,3,N,N} INTERSECT {1,2,2,3,5,5,N,N,N}
Ë{1,2,3,N}INTERSECT ALL
In an INTERSECT
ALL operation, if a tuple t appears m (> 0) times in the first input
table, and the same tuple t appears n (> 0) times in the second input
table, then that tuple appears the lesser of m and n times in the
output table.
{0,1,2,2,2,2,3,N,N} INTERSECT ALL {1,2,2,3,5,5,N,N,N}
Ë{1,2,2,3,N,N}