I noticed that there is a sql quiz of oracle database on the place where I seem like to not remember it a few days ago. But the stuff of this SQL Quiz should look like as below.
Which statement is true regarding the INTERSECT operator in the oracle database?
(1) By reversing the order of the intersected tables alter the result.
(2) It ignores the NULL values.
(3) The number of columns and the data types must be identical for all of the
SELECT statements in the query.
(4) The names of all columns in the SELECT statements must be identical.
(5) None.
When ended up reading the prior SQL topic and each choice I know the topic has certainly only a correct answer. For being able to pick up the correct answer I built some demos to verify the description of each choice of this SQL Quiz is whether right or not. By the way my all of demos is separately tested on oracle 18.3.0.0 and 19.8.0.0 (LiveSQL).
Firstly, let me take a look at the choice 1 and my demo is as follows:
PROMPT ==================================================================
PROMPT Building a demo for verifying Choice 1:
PROMPT ==================================================================
PROMPT By reversing the order of the intersected tables alter the result.
PROMPT ==================================================================
DROP TABLE t1 PURGE;
CREATE table t1(a VARCHAR2(1), b VARCHAR2(1));
INSERT INTO t1 VALUES ('a', 'b');
INSERT INTO t1 VALUES ('a', 'c');
INSERT INTO t1 VALUES ('b', 'c');
COMMIT;
DROP TABLE t2 PURGE;
CREATE TABLE t2(a VARCHAR2(1), b VARCHAR2(1));
INSERT INTO t2 VALUES ('a', 'b');
INSERT INTO t2 VALUES ('c', 'd');
INSERT INTO t2 VALUES ('b', 'c');
COMMIT;
SELECT * FROM t1
INTERSECT
SELECT * FROM t2
;
SELECT * FROM t2
INTERSECT
SELECT * FROM t1
;
When performing the previous SQL demo, you’ll get the same query result whatever the first SELECT table t1 OR the first SELECT table t2.
-- on 18.3 and LiveSQL: A B - - a b b c
As you can see the stuff of choice 1 is incorrect – “By reversing the order of the intersected tables DON’T alter the result” (note: only showing the first SELECT table’s column names).
Next, it’s my second demo for the choice 2.
PROMPT =======================================
PROMPT Building a demo for verifying Choice 2:
PROMPT =======================================
PROMPT It ignores the NULL values.
PROMPT =======================================
DROP TABLE t1 PURGE;
CREATE table t1(a CHAR(1), b CHAR(1), c CHAR(1));
INSERT INTO t1 VALUES ('a', 'b', null);
INSERT INTO t1 VALUES ('a', null, 'c');
INSERT INTO t1 VALUES ('a', 'b', 'c');
COMMIT;
DROP TABLE t2 PURGE;
CREATE TABLE t2(a CHAR(1), b CHAR(1), c CHAR(1));
INSERT INTO t2 VALUES ('a', 'b', null);
INSERT INTO t2 VALUES ('a', null, 'c');
INSERT INTO t2 VALUES ('d', 'e', 'f');
COMMIT;
SELECT * FROM t1
INTERSECT
SELECT * FROM t2
;
Then the preceding last SQL QUERY result is as below respectively on 18.3 and LiveSQL:
-- on 18.3: A B C - - - a b a c -- on LiveSQL: A B C - - - a b - a - c
As you can see from the above outcome we found that the INTERSECT operation of the SELECT of two tables hasn’t ignored the NULL values (the choice 2 is absolutely wrong). For better showing the NULL values we can also use the SQL expression CASE WHEN … THEN … ELSE … END or the SQL function NVL() to return a pretty friendly result. Let me show you about them in the following two number of SQL snippets.
Case when … then … else … end
SELECT
CASE WHEN a IS NULL THEN 'null'
ELSE a
END "A",
CASE WHEN b IS NULL THEN 'null'
else b
end "B",
CASE WHEN c IS NULL THEN 'null'
else c
end "C"
FROM
t1
INTERSECT
SELECT
CASE WHEN a IS NULL THEN 'null'
ELSE a
END "A",
CASE WHEN b IS NULL THEN 'null'
else b
end "B",
CASE WHEN c IS NULL THEN 'null'
else c
end "C"
FROM
t2
;
Of course the prevous SQL statement shows the same query result on 18.3 and LiveSQL.
-- on 18.3: A B C ---- ---- ---- a b null a null c -- on LiveSQL: A B C - ---- ---- a b null a null c
NVL()
SELECT
NVL(a, 'null') a,
NVL(b, 'null') b,
NVL(c, 'null') c
FROM
t1
INTERSECT
SELECT
NVL(a, 'null') a,
NVL(b, 'null') b,
NVL(c, 'null') c
FROM
t2
;
Due to using NVL() still shows the identical outcome with the CASE WHEN for the intersect operation hence I deliberately omit it.
Now it’s time to verify the choice 3 is whether correct or not. We should carefully read the stuff of this choice.
The number of columns and the data types must be identical for all of the
SELECT statements in the query.
As you can see from the previous info there are two number of key points in that choice – the number of columns AND the data types. So I’ll illustrate it from those two aspects.
The different number of columns
PROMPT =========================================================================
PROMPT Building a demo for verifying Choice 3:
PROMPT =========================================================================
PROMPT The number of columns and the data types must be identical for all of the
PROMPT SELECT statements in the query.
PROMPT =========================================================================
-- The number of columns of the SQL query (for the INTERSECTION in two tables) is different.
DROP TABLE t1 PURGE;
CREATE table t1(a NUMBER(1), b NUMBER(1));
INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (2, 3);
INSERT INTO t1 VALUES (1, 3);
COMMIT;
DROP TABLE t2 PURGE;
CREATE TABLE t2(a NUMBER(1), b NUMBER(1));
INSERT INTO t2 VALUES (1, 2);
INSERT INTO t2 VALUES (3, 4);
INSERT INTO t2 VALUES (2, 3);
COMMIT;
SELECT
a, b
FROM t1
INTERSECT
SELECT a
FROM t2
;
We can get the same error ORA-01789 both on oracle 18.3 and LiveSQL.
-- on 18.3 and LiveSQL: ORA-01789: query block has incorrect number of result columns
The different data types
-- The data type of columns of the SQL query (for the INTERSECTION in two tables) is different.
DROP TABLE t1 PURGE;
CREATE table t1(a NUMBER(1), b NUMBER(1));
INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (2, 3);
INSERT INTO t1 VALUES (1, 3);
COMMIT;
DROP TABLE t2 PURGE;
CREATE TABLE t2(a CHAR(1), b CHAR(1));
INSERT INTO t2 VALUES ('1', '2');
INSERT INTO t2 VALUES ('3', '4');
INSERT INTO t2 VALUES ('2', '3');
COMMIT;
SELECT * FROM t1
INTERSECT
SELECT * FROM t2
;
Now we return the same error of ORA-01790 separately on 18.3 and LiveSQL.
-- on 18.3 and LiveSQL: ORA-01790: expression must have same datatype as corresponding expression
By contrary the description of the choice 3 is correct – must have the identical number of columns and the data types in the two number of SELECT statements for the intersect operation.
P.S. – We can also deem that the NUMBER and FLOAT are the same data type because the FLOAT data type is a subtype of NUMBER as you can see from this link of oracle official docs on 19c and the following screenshot.
Next let me verify the choice 4, here’s my fourth demo.
PROMPT ==================================================================== PROMPT Building a demo for verifying Choice 4: PROMPT ==================================================================== PROMPT The names of all columns in the SELECT statements must be identical. PROMPT ==================================================================== DROP TABLE t1 PURGE; CREATE table t1(a INTEGER); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (3); COMMIT; DROP TABLE t2 PURGE; CREATE TABLE t2(b INTEGER); INSERT INTO t2 VALUES (1); INSERT INTO t2 VALUES (3); INSERT INTO t2 VALUES (4); COMMIT; SELECT a FROM t1 INTERSECT SELECT b FROM t2 ;
Certainly the returned result is all okay on 18.3 and LiveSQL even if those two tables’ column name is different.
-- on 18.3:
A
----------
1
3
-- on LiveSQL:
A
-
1
3
So, the choice 4 is wrong as well.
As s whole the SQL Quiz’s correct answer is the choice 3 (Of course it makes no sense that we read the choice 5). You can also see the entire demo codes from my Github repository.
