Teaching Ideas SQL entry exercise student achievement 4. Multi-table join relationship Query

There are five types of join relationships between tables: inner join, left Outer Join, right Outer Join reight Outer Join, full outer join, and cross join.

If the connection relationship between the table and the table is used during the query, The on clause should be used as the join condition for the two tables, that is, the result set of the Union rows that meet the conditions should be searched, the where clause is used as the Search Condition for filtering tables. This course teaches you how to use these four connections.

Internal Connection: Using Inner join, the query result set can only contain the row result set that meets the on Clause table and table connection conditions, if the rows in any source table do not have corresponding rows in the other table (which meets the on clause), The result set will be excluded and will not contain.

For example:

Select a. Full name of supplier, B. supply commodity name from supplier info table as a inner join commodity info table as B on A. Supplier No. = B. Supplier No.

The query result is: the full name of the current commodity supplier and the name of the supplied Commodity in all commodity information tables.

There is another way to write multi-table queries:

Select a. Full name of supplier, B. supply product name from supplier info table as a, Product Info table as B where a. Supplier No. = B. Supplier No.

In fact, internal join is equivalent to multi-Table query. I have used this multi-Table query method in the previous lesson. Please remember that if you use the [inner] Join keyword, the on keyword must be used for the Union condition of the query.

Usage of using in internal join: Using (field a, field B) is used as the Search Condition for connecting the table with the same field value as the table, which is equivalent to on (table 1. field A = table 2. field A and table 1. field B = table 2. field B)

Use the using keyword as follows:

Select a. Full name of supplier, B. supply commodity name from supplier info table as a inner join commodity info table as B using (supplier no)

Left join: Use left outer join to get all the rows in the table on the left of left join. Then, use the on clause to match the query fields of the table on the right that meet the Union conditions to the result row, when the left table does not splice values to the right table, use null to fill the table.

For example: select a. Full name of supplier, B. supply commodity name from supplier info table as a left Outer Join commodity info table as B on A. Supplier No. = B. Supplier No.

The query result is: the names of all the suppliers and the products they supply. If the full name of the supplier is "Tang Seng business Co., Ltd." The original "Changsheng Oral Liquid", the goods will not be purchased due to false exaggerated efficacy, this item should not be retained in the item information table, but there is no other supply item in Tang Seng business Co., Ltd.. In this case, the left query is used in the query result, that is to say, the rows in the table on the left of left join will appear. The value of the commodity information supplied by Tang Seng business Co., Ltd. will be null.

Right join: the use of right outer join is the same as that of left join, but all rows in the right table of right outer join are displayed, then, use the values of the columns in the left table to match the right table. If the value of the Left table cannot be found, null is used to fill in the values of rows in the right table that do not match in the result set.

The statement for implementing the left join using the right join is as follows:

Select a. Full name of supplier, B. supply product name from information table as B right Outer Join supplier information table as a on A. Supplier No. = B. Supplier No.

The difference is that you can replace the two tables with the link keyword.

Full join: use full outer join to query all the rows that match the left table, the rows that do not match the left table, and the rows that do not match the left table in the right table, similar to rows that do not match the left Outer Join or the right join.

Next, Please execute the SQL script according to the following code, and then execute these four connection relationship statements to understand the query result set.

The link field of the two tables is col.
1 Create Table A (COL int, coll INT)
2 insert into a values (1, 10)
3 insert into a values (2, 20)
4 insert into a values (3, 30)
5 insert into a values (4, 40)
6 insert into a values (5, 50)
7 Go
8 Create Table B (COL int, coll INT)
9 insert into B values (1, 10)
10 insert into B values (6, 20)
11 insert into B values (7, 30)
12 insert into B values (8, 40)
13 insert into B values (2, 50)
14

 

Cross join: If table A has five records and six fields, table B has three records and four fields, use cross join to join the two tables to query all fields. The result set is 5*3 = 15 records, and 6 + 4 = 10 fields.

Students can use the table above to perform cross-Join Operations. In the Table-to-table connection relationship, internal connections and left connections are usually used. Please have a clear understanding of these two syntaxes.

Next, we will continue to exercise the exercises on the "student achievement" database and use the table connection relationship to practice:

1. query the names of all the teachers on behalf of whom and their subjects.

2. query the names of all instructors and their subjects.

3. query the names and average scores of all male students who have passed the test.

4. query the names and average scores of all students. If the student does not take the test, the average score is replaced by 0.

 

Answer
1. Select a. teachername, B. Kemu from teacherinfo as a inner join score as B on A. teacherid = B. teacherid
2. Select a. teachername, B. Kemu from teacherinfo as a left join score as B
On a. teacherid = B. teacherid
3. select. name, AVG (B. score) as average score from student a join chengji B on. number = B. number where. sex = 'male' group by B. number,. name
4. select. name, isnull (AVG (B. score), 0) as average score from student a left join chengji B on. number = B. number Group by B. number,. name