阿里巴巴面里三個與開發有關的試題。
example 1:(not in/not exists + null)
Create Table usertable(
userid number,
username varchar2(20)
)
/
insert into usertable values(1,'user1');
insert into usertable values(2,null);
insert into usertable values(3,'user3');
insert into usertable values(4,null);
insert into usertable values(5,'user5');
insert into usertable values(6,'user6');
commit;
Create Table usergrade (
userid number,
username varchar2(20),
grade number)
/
insert into usergrade values(1,'user1',90);
insert into usergrade values(2,null,80);
insert into usergrade values(7,'user7',80);
insert into usergrade values(8,'user8',90);
commit;
語句1﹕ select count(*) from usergrade where username not in (select username from usertable );
語句2﹕select count(*) from usergrade g where not exists
(select null from usertable t where t.userid=g.userid and t.username=g.username);
結果為: 語句1( ) 語句2( )
答案
A:0 B:1 C:2 D:3 E: null
example 2: (in/exists+rownum）
drop table usertable;
drop table usergrade;
Create table usertable (
userid number,
username varchar2(20))
/
insert into usertable values(1,'user1');
insert into usertable values(2,'user2');
insert into usertable values(3,'user3');
insert into usertable values(4,'user4');
insert into usertable values(5,'user5');
commit;
Create table usergrade (
username varchar2(20),
usergrade number)
/
insert into usergrade values('user9',90);
insert into usergrade values('user8',80);
insert into usergrade values('user7',80);
insert into usergrade values('user2',90);
insert into usergrade values('user1',100);
insert into usergrade values('user1',80);
commit;
語句1﹕Select count(*) from usertable t1 where username in
(select username from usergrade t2 where rownum <=1);
語句2﹕Select count(*) from usertable t1 where exists
(select 'x' from usergrade t2 where t1.username=t2.username and rownum <=1);
結果為: 語句1( ) 語句2( )
答案
A:0 B:1 C:2 D:3
example 3:(關聯更新)
Create table student_grade(
id number,
grade number)
/
insert into student_grade values(1,50);
insert into student_grade values(2,40);
insert into student_grade values(3,70);
insert into student_grade values(4,80);
insert into student_grade values(5,30);
insert into student_grade values(6,90);
commit;
Create table student_makeup(
id number,
grade number)
/
insert into student_makeup values(1,60);
insert into student_makeup values(2,80);
insert into student_makeup values(5,60);
commit;
update student_grade s set s.grade=
( select t.grade from student_makeup t
where s.id=t.id)
commit;
語句﹕select trade from student_grade where id=3;
結果為()
答案為﹕
A:0 B:70 C:null D:以上全不對。
--------------------------------------------------------------------
1.知識點︰not in/not exists+null
關於where條件︰
X AND Y: 只要X或者Y有一個是FALSE或者null的話，X AND Y返回FALSE。
關於null :
一個NULL值意味著未知，因此，對一個NULL值的任何比較或操作也都是無效的，而任何返回NULL的測試也都被忽略。
所以︰
select 'true' from dual where 1 = null;
select 'true' from dual where 1 != null;
select 'true' from dual where 1 is null;
以上比較不返回任何值。
select 'true' from dual where null is null;
null is null不是比較，而是說null 是個null，所以返回一行。
IN︰
in 后裡面的列表可以包括null，但是因為相當于用=依次比較，然後去or，true or null =true，所以他們是被忽視的(不理睬)。
not in 實際上是用!=依次比較列表，然後去and，TRUE AND NULL = NULL。只要列表包括null值，就會返回FALSE(此where條件為FALSE)。
所以，當not in后面是子查詢時， 只有保證select后面的字段有not null約束或暗示的時候才能使用。
為了防止這種情況發生，可以在列表中阻止返回null︰
not in (select a from t where a is not null);
not in (select nvl(a,0) from t);
對于in 和 exists的性能區別:
如果子查詢得出的結果集記錄較少，主查詢中的表較大且又有索引時應該用in,反之如果外層的主查詢記錄較少，子查詢中的表大，
又有索引時使用exists。其實區分in和exists主要是造成了驅動順序的改變（這是性能變化的關鍵），如果是exists，那么以外層表為驅動表，
先被訪問，如果是IN，那么先執行子查詢，所以我們會以驅動表的快速返回為目標，那么就會考慮到索引及結果集的關係了
對于not in 和 not exists的性能區別︰
not in 只有當子查詢中，select 關鍵字后的字段有not null約束或者有這種暗示時用not in,另外如果主查詢中表大，子查詢中的表小但是記錄多，
則應當使用not in,並使用anti hash join.如果主查詢表中記錄少，子查詢表中記錄多，並有索引，可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外連接+is null
2.知識點︰關聯更新
Update student_grade s set s.grade =
(select t.grade from student_makeup t where s.id=t.id)
Where Exists ( Select 1 From student_makeup t Where s.id=t.id)
update (select s.grade sgrade,t.grade tgrade from student_grade s,student_makeup t where s.id=t.id)
set sgrade=tgrade;
--ORA-01779: 無法修改對應非保留索引鍵表格的資料欄
alter table student_makeup add primary key (id);
