Testers often check whether the data in the database is correct due to work reasons. At this time, they need to frequently use multi table queries.

In multi table query, inner join is the most widely used, followed by sub query.

Why are subqueries widely used in work? The reason is that subqueries are very convenient and flexible, so they are often used in testing.

However, it's embarrassing that because the subquery is too flexible, many novice testers can't master it well and give up, so this article is written to share the application rules of subquery. This paper is divided into the following steps:

1. Step 1: prepare the table statement
2. The second step is to input the table creation statement into navicat to create three tables (in fact, only two tables are used)
3. The third step is to understand the three steps of question making
4. Step 4: test the questions
5. Step 5: summary

1, Step 1: prepare the table statement

1. Prepare sql data

-- Employee list employees
-- Payroll salary
-- Departmental table departments
create table departments (
deptid int(10) primary key, 
deptname varchar(20) not null -- Department name
);
insert into departments values ('1001', 'Marketing Department');
insert into departments values ('1002', 'Test department');
insert into departments values ('1003', 'Development Department');

create table employees (
empid int(10) primary key,
empname varchar(20) not null, -- Full name
sex varchar(4) default null, -- Gender
deptid int(20) default null, -- Department number
jobs varchar(20) default null, -- post
politicalstatus varchar(20) default null, -- Political affiliation
leader int(10) default null
);

insert into employees values ('1', 'Wang Zhaojun', 'female', '1003', 'Development', 'Masses', '9');
insert into employees values ('2', 'Zhu Geliang', 'male', '1003', 'Development Manager', 'Masses', null);
insert into employees values ('3', 'Zhang Fei', 'male', '1002', 'test', 'League member', '4');
insert into employees values ('4', 'White', 'male', '1002', 'Test Manager', 'Party member', null);
insert into employees values ('5', 'Big Joe', 'female', '1002', 'test', 'Party member', '4');
insert into employees values ('6', 'Sun Shang Xiang', 'female', '1001', 'market', 'Party member', '12');
insert into employees values ('7', 'Bai Li Xuan strategy', 'male', '1001', 'market', 'League member', '12');
insert into employees values ('8', 'Little Joe', 'female', '1002', 'test', 'Masses', '4');
insert into employees values ('9', 'Baili Convention', 'male', '1003', 'Development', 'Party member', '9');
insert into employees values ('10', 'Da Ji', 'female', '1003', 'Development', 'League member', '9');
insert into employees values ('11', 'Li Bai', 'male', '1002', 'test', 'League member', '4');
insert into employees values ('12', 'Sun Bin', 'male', '1001', 'Marketing Manager', 'Party member', null);

create table salary (
sid int(10) primary key,
empid int(10) not null,
salary int(10) not null -- wages
);

insert into salary values ('1', '7', '2100');
insert into salary values ('2', '6', '2000');
insert into salary values ('3', '12', '5000');
insert into salary values ('4', '9', '1999');
insert into salary values ('5', '10', '1900');
insert into salary values ('6', '1', '3000');
insert into salary values ('7', '2', '5500');
insert into salary values ('8', '5', '2000');
insert into salary values ('9', '3', '1500');
insert into salary values ('10', '8', '4000');
insert into salary values ('11', '11', '2600');
insert into salary values ('12', '4', '5300');

2, Step 2: input the table creation statement into navicat to create three tables

1) New database test

2) Enter the sql statement into the query editor and run

3) Create data table and paste it into excel

3, Step 3: understand the three steps of sub query

1. Clarify the requirements of the topic:

To inquire "Wang Zhaojun's salary"

2. Define the concept of subquery

A typical subquery statement is one with () in the sql statement, as shown in the following sql statement:
select * from scores where studentNo in (select studentNo from students where age=18)
Queries in parentheses are called subqueries, and tables in parentheses are called subtables
 The query outside the brackets is called the main query, and the table outside the brackets is called the main table

3. Schematic diagram of sub query (only more complex multi table sub query is introduced here, important)

4. Text analysis diagram (important)

For example, in the table we have built now, what we need now is "Wang Zhaojun's salary". What should we do with sub query according to the schematic diagram?

1. Determine which table is the main table (make sure the table where the field is located is the main table) and which table is the sub table.
Analysis: the meaning of the question requires "Wang Zhaojun's salary". The required field is salary, so the salary table where the salary field is located is the main table, which should be placed outside the brackets. Accordingly, Wang Zhaojun's employees table is a sub table, which should be placed in brackets.

2. (key 1) judge the connection fields of the two tables, [also find the fields with the same meaning in the two tables]
Analysis: to have a relationship between two tables, you need to find fields with the same meaning. They are employee fields, also called empid fields. This is the key point 1

3. Write out the main table statement "select * from main table where join field in()"
Analysis: because subqueries are generally used for conditions, and where is the key word of the condition query, generally subqueries are placed after where. When you query where, you must have a table in front of you and a field in the back. Why use "in" instead of "=" after the connection field? Because the subquery may return multiple results, it is more appropriate to use "in". () is a subquery placeholder

4. Write out the sub table statement "select connection field from sub table where..." 
Analysis: it is very important to see a piece of text in the diagram: "the target (key point) 2 of the subquery statement is often the field in the title, which can be replaced by the connection field". For example, our title is "Wang Zhaojun's salary", so the subquery is "Wang Zhaojun's two table connection field", which is "Wang Zhaojun's empid", so it is "select connection field from sub table" where... "

5. Combination is to put the sub query inside the query

5. From the text analysis diagram, the sub query 5 steps can be optimized to 3 steps

1. Distinguish the connection fields of the main table and the sub table + two tables (step 1 and 2 are optimized to step 1, which are passed in the brain)
2. Write out the main query statement select * from main table where join field in ()
3. Write out the sub query statement in combination with the main query (sub query Objective: change the field required by the title to the connection field)

4, Step 4 - test step

select * from salary where empid in (select empid from employees where empname = 'Wang Zhaojun')

Add: why is the sub query convenient? Because the main query is short of empid, the sub query will find what it is

Five, summary

The sub query is divided into the following three steps:

Compared with the inner join method to get "Wang Zhaojun's salary", you know how simple the sub query is.

I don't believe it. You use the method of internal connection.