in 和 exists的区别 用数据说话

• in和exists的区别，用数据说话
• Oracle中的in和exists区别
• SQL优化之子查询-IN和EXISTS哪个快点
• 语音和噪声相关数据集(持续更新)
• about云大数据面试宝典 大公司面试一般用不到

drop table base_customer;

create table base_customer
(
   uuid                 number(10) not null,
   customerId           varchar(20),
   showName             varchar(30),
   trueName             varchar(30),
   image                varchar(100),
   pwd                  varchar(50),
   registerTime         timestamp,
   securityKey          varchar(10),
   primary key (uuid),
   unique(customerId)
);

create index idx_customer_registerTime on base_customer(registerTime);

drop table base_customer_sign;
create table base_customer_sign
(
   uuid                  number(10) not null,
   signCustomerUuid       number(10) not null,
   signTime              timestamp not null,
   signCount             number(10) not null,
   signSequenceCount     number(10) not null,
   primary key (uuid)
);
create index idx_sign on base_customer_sign(signCustomerUuid);

base_customer      100w条
  uuid主键
  customerId 唯一索引
base_customer_sign 100条
  uuid主键
  signCustomerUuid 非唯一索引

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.Date;

import oracle.jdbc.OracleDriver;

public class Test {
	
	
	public static void main(String[] args) throws Exception {
		initData();
	}
	
	public static void initData() throws Exception {
		DriverManager.registerDriver(new OracleDriver());
		Connection conn = null;
		try {
			String url = "jdbc:oracle:thin:@localhost:1521:orcl2";
			String username = "test";
			String password = "test";
			conn = DriverManager.getConnection(url, username, password);
			conn.setAutoCommit(false);
			
			conn.createStatement().execute("truncate table base_customer");
			
			PreparedStatement psst = conn.prepareStatement("insert into base_customer values(?,?,?,?,?,?,?,?)");

			for(int i=1; i<=1000000;i++) {//100w
				int count = 1;
				psst.setInt(count++, i);
				psst.setString(count++, "user" + i);
				psst.setString(count++, "user" + i);
				psst.setString(count++, "user" + i);
				psst.setString(count++, "user" + i);
				psst.setString(count++, "user" + i);
				psst.setTimestamp(count++, new Timestamp(System.currentTimeMillis()));
				psst.setString(count++, "key" + i);
			    psst.addBatch();
			    psst.executeBatch();
			    conn.commit();
			}
			
			PreparedStatement psst2 = conn.prepareStatement("insert into base_customer_sign values(?,?,?,?,?)");

			for(int i=1; i<=100;i++) {//100
				int count = 1;
				psst2.setInt(count++, i);
				psst2.setInt(count++, i);
				psst2.setTimestamp(count++, new Timestamp(System.currentTimeMillis()));
				psst2.setInt(count++, 1);
				psst2.setInt(count++, 1);
				psst2.addBatch();
				psst2.executeBatch();
			    conn.commit();
			}
		    
			psst.close();
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			conn.rollback();
			
		} finally {
			conn.close();
		}
	}

}

select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where trueName like 'user%')

select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where trueName like 'user%' group by uuid)

select count(*) from base_customer_sign
where exists (select 1 from base_customer where trueName like 'user%' and base_customer.uuid = base_customer_sign.signCustomerUuid)
 

select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where customerId like 'user%')

select count(*) from base_customer_sign
where exists (select 1 from base_customer where base_customer.customerId like 'user%' and base_customer.uuid = base_customer_sign.signCustomerUuid)

select * from base_customer where uuid in (select signCustomerUuid from base_customer_sign where trueName like 'user%')

select count(*) from base_customer
where exists (select 1 from base_customer_sign where trueName like 'user%' and base_customer.uuid = base_customer_sign.signCustomerUuid)
 

select count(*) from base_customer where uuid in (select signCustomerUuid from base_customer_sign where uuid>1 and signSequenceCount < 1)

select count(*) from base_customer
where exists (select 1 from base_customer_sign where base_customer_sign.uuid>1 and base_customer.uuid = base_customer_sign.signCustomerUuid)