exists的用法

-- Create table
create table CUSTOMERS
(
  CID    NUMBER not null,
  CNAME  VARCHAR2(50) not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

alter table CUSTOMERS
  add constraint CUSTOMERS_PK primary key (CID)
  using index
  tablespace USERS

-- Create sequence
create sequence SEQ_CUSTOMERS
minvalue 1
maxvalue 999999
start with 1
increment by 1
cache 20;

select * from customers;
insert into customers values(seq_customers.nextval,'张三');
insert into customers values(seq_customers.nextval,'李四');
insert into customers values(seq_customers.nextval,'王五');

--------------------------------------------------------------------------
-- Create table
create table ORDERS
(
  OID   NUMBER not null,
  ONAME VARCHAR2(50) not null,
  CID   NUMBER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints
alter table ORDERS
  add constraint ORDERS_PK primary key (OID)
  using index
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

-- Create sequence
create sequence SEQ_ORDERS
minvalue 1
maxvalue 999999
start with 1
increment by 1
cache 20;

insert into orders values(seq_orders.nextval,'订单1','1');
insert into orders values(seq_orders.nextval,'订单2','2');
insert into orders values(seq_orders.nextval,'订单3','2');

---------------------------------------------------------------------
/* 查询图书的标题，这些图书是有出版商的，换句话 查询的是 有出版商的图书 的图书标题*/

1.使用exists 关键字

select title from books where exists (select 1 from publishers where pub_id = books.pub_id);

2. 使用in

select * from books where pub_id in (select pub_id from publishers);

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

not exists 和exists

/*  查询没有出版过书的 出版商的名称 */
select name from publishers where not exists ( select 1 from books where pub_id = publishers.pub_id)

查询 这些 出版商的名称                          是在图书表中没有对应的记录            这些出版商的id
/*  查询有出版过书的 出版商的名称 */
select name from publishers where exists ( select 1 from books where pub_id = publishers.pub_id)