Gbase8s database intersect operator

When combining two queries with this set operator ,INTERSECT Calculates the number of rows returned by two queries for its operand

intersection .

INTERSECT The returned row shows the left and right SELECT The result set of the statement .INTERSECT The result is usually

Different or unique lines , because INTERSECT Eliminate any duplicate lines .

Consider the following example , One of the tables t1 There are the following lines ：

create table t1 (col1 int);

insert into t1 values (1);

insert into t1 values (2);

insert into t1 values (2);

insert into t1 values (2);

insert into t1 values (3);

insert into t1 values (4);

insert into t1 values (4);

insert into t1 values (NULL);

insert into t1 values (NULL);

insert into t1 values (NULL);

In the same example , surface t2 There are these lines ：

create table t2 (col1 int);

insert into t2 values (1);

insert into t2 values (3);

insert into t2 values (4);

insert into t2 values (4);

insert into t2 values (NULL);

The following queries are from INTERSECT The two queries on the left and right of the operation object return different rows . Important questions to note here

The problem is that the result has NULL value . Because when the table t2 And watch t1 When comparing , Considering the table t2 Medium NULL The value is phase

Waiting , So from this intersection NULL Value is returned in the combined result set ：

SELECT col1 FROM t1 INTERSECT SELECT col1 FROM t2;

col1

1

3

4

4 row(s) retrieved

INTERSECT Operators have some （ But not all ） And UNION Operators have the same restrictions , but INTERSECT No

Hold to make UNION Capable of returning duplicate values ALL keyword . See also the topic For combined SELECT The limitation of .