oracle or和and,oracle中and和or

复习ocp考题的时候有道关于and和or的题目，拿出来看一下and和or的区别：

Choose two

Examine the description of the BOOKS_TRANSACTIONS table

Name Null? Type

----------------------------------------------------------------------------------------------

THANSACTION_ID NOT NULL VARCHAR2(6)

TRANSACTION_TYPE VARCHAR2(3)

BORROWED_DATE DATE

BOOK_ID VARCHAR2(6)

MEMBER_ID VARCHAR2(6)

Examine this partial SQL statement:

SELECT * FROM books_transctions;

Which two WHERE conditions give the same result?

A) WHERE borrowed_date = SYSDATE AND (transaction_type= 'RM' OR member_id IN ('A101','A102'));

B) WHERE borrowed_date = SYSDATE AND transaction_type= 'RM' OR member_id IN ('A101','A102');

C) WHERE borrowed_date = SYSDATE AND (transaction_type= 'RM' AND member_id='A101' OR member_id = 'A102'));

D) WHERE (borrowed_date = SYSDATE AND transaction_type= 'RM' ) OR member_id IN ('A101','A102');

E) WHERE borrowed_date = SYSDATE AND (transaction_type= 'RM' AND ( member_id= 'A101' OR member_id = 'A102' ) );

Correct Answer: BD

那么，and和or有什么区别呢 ？

1.and表示交

2.or表示并

3.and的优先级比or高

然后我们来看下上面这个题目：(谁能一眼看出答案？)

A：表示 orrowed_date = SYSDATE and transaction_type= 'RM'    交集于  orrowed_date = SYSDATE and member_id IN ('A101','A102')

B：表示WHERE borrowed_date = SYSDATE AND transaction_type= 'RM'  交集于  member_id IN ('A101','A102');

C：表示WHERE borrowed_date = SYSDATE AND transaction_type= 'RM' AND member_id='A101'  交集于  WHERE borrowed_date = SYSDATE and member_id = 'A102'

D：这个就很明显了，前面有个括号

表示(borrowed_date = SYSDATE AND transaction_type= 'RM' )交集于  member_id IN ('A101','A102');

E：表示在member_id= 'A101' 交集于 member_id = 'A102'的结果集里面找到 borrowed_date = SYSDATE AND transaction_type= 'RM'

也就是 WHERE borrowed_date = SYSDATE AND transaction_type= 'RM'  and member_id= 'A101'  交集于 WHERE borrowed_date = SYSDATE AND transaction_type= 'RM'  and member_id= 'A102'