Explain the syntax and differences between exists and in in SQL

Time:2021-9-4

The difference between exists and in is very small and almost equivalent, but efficiency is often paid attention to in SQL optimization. Today, let’s talk about the difference between exists and in.
Exists syntax:
Select… From table where exists
Put the results of the primary query into the sub query results for verification. If there is data in the sub query, the verification is successful, the verification is met, and the data is retained.


create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);
insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

For example:


select tname from teacher exists(select * from teacher);

This SQL statement is equivalent to select tname from teacher
(if the primary query data exists in the sub query, the query is successful (verification is successful))

This SQL returns null because such data does not exist in the subquery.
In Syntax:
Select… From table where field in (subquery)


select ..from table where tid in (1,3,5) ;

select * from A where id in (select id from B);

difference:
If the data set of the main query is large, use in;
If the data set of the sub query is large, exists is used;
For example:


select tname from teacher where exists (select * from teacher);

It is obvious that the sub query has a large data set, and the use of exists is efficient.


select * from teacher where tname in (select tname from teacher where tid = 3);

It is obvious here that the main query has a large data set, uses in, and is highly efficient.

This is the end of this article about the syntax and differences between exists and in in SQL. For more information about the differences between exists and in syntax in SQL, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!

Recommended Today

Swift advanced (XV) extension

The extension in swift is somewhat similar to the category in OC Extension can beenumeration、structural morphology、class、agreementAdd new features□ you can add methods, calculation attributes, subscripts, (convenient) initializers, nested types, protocols, etc What extensions can’t do:□ original functions cannot be overwritten□ you cannot add storage attributes or add attribute observers to existing attributes□ cannot add parent […]