SQL statement Optimization--Query two tables different rows not in, not EXISTS, connection query left join

In the actual development, we often need to compare two or more table data differences, compare those data the same data is not the same, then we have three ways to use: 1. In or not in,2. exist or notexist,3. Use a connection query (inner Join,left join or right join).

Looking at the data below, we are ready to select the Depart_ information that does not exist in the Depart_info pid in User_info.

There are table 1:depart_info

Table 2:user_info

Method One: Use Not in

In and not is followed by a set, in is the appearance and the inner table as a hash connection.

   Select d.* from Depart_info D where is not EXISTS (SELECT * from User_info u WHERE d.pid = u.pid);

The test time is about 0.002s.

Method Two: Adopt not EXISTS

EXISTS and not EXISTS are loop loops on the outside, each loop is then queried on the internal table,

  Select d.* from Depart_info D where is not EXISTS (SELECT * from User_info u WHERE d.pid = u.pid);

The test time is about 0.002s.

Method Three: Using connection query

Connection queries include:

1. Self-connect (join equals inner JOIN): Query result is data that exists on both sides

2. Left join to the left join: return all data on the right, return to the back, null not present

3. Right join: Return all data on the right, return on left, no null

4. Fully connected full join: Returns if one of the tables exists and the other does not exist as Nul

   SELECT d.* from Depart_info D left joins User_info u on d.pid = U.pid WHERE u.pid is NULL;

Test time is around 0.001s

Summarize:

1, for a small amount of data exists and in the same, if the data is more (in millions of lines) is recommended to use exists, better use association query.

2, the number is small, if a small two tables, one is a large table, then the subquery table large with exists, sub-query table small with in.

3. Note that if any of the records returned in the subquery contain null values, the in query will not return any records .

4, the return data is two tables of multiple field data, we recommend the use of associated queries. Not only is the speed fast, but the return data can be customized.

SQL statement Optimization--Query two tables different rows not in, not EXISTS, connection query left join