SQL multi-Table connection Query

Create two tables:

Table 1: student:



Table 2: course:



(In this case, the table is created to demonstrate the connection to the SQL statement. Of course, we will not create the table in actual development. In actual development, the two tables will have their own primary keys .)

1. External Connection

External connections can be divided into: left join, right join, and full outer join.

1. left join or left outer join

SQL statement: select * from student left join course on student. ID = course. ID
Execution result:



The left Outer join contains all rows in the left join table. If a row in the left table does not match in the right table, all the rows in the right table of the corresponding row in the result are NULL ).

Note: we cannot say that the number of rows in the result is equal to the number of rows in the left table. Of course, the number of rows in the query result is equal to the number of rows in the left table, because the left and right tables have a one-to-one relationship.

2. right join or right outer join
SQL statement: select * from student right join course on student. ID = course. ID
Execution result:



The outer right join contains all rows in the right join table. If a row in the left table does not match in the right table, all the rows in the left table in the result are NULL ).

Note: similarly, we cannot say that the number of rows in the result is equal to the number of rows in the right table. Of course, the number of rows in the query result is equal to the number of rows in the left table, because the left and right tables have a one-to-one relationship.

3. full outer join or full outer join

SQL statement: select * from student full join course on student. ID = course. ID
Execution result:



A full outer join contains all rows in both the left and right tables. If a row in the right table does not match in the left table, all the rows in the right table of the corresponding row in the result are NULL ), if a row in the left table does not match in the right table, all the parts in the left table of the corresponding row in the result are NULL ).

2. Internal join or inner join

SQL statement: select * from student inner join course on student. ID = course. ID
Execution result:



Inner join is a comparison operator that returns only rows that meet the conditions.

This is equivalent to: select * from student, course where student. ID = course. ID

Iii. cross join

1. Concept: A cross join without a WHERE clause will generate a Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set.

SQL statement: select * from student cross join course
Execution result:



If we add the WHERE clause to this SQL statement at this time, for example, SQL: select * from student cross join course where student. ID = course. ID

The result set that meets the conditions is returned, and the result is the same as the execution result shown in inner join.

4. Join statements with One-to-multiple relationship and multiple-to-one or multiple-to-many relationship

Of course, the above two tables have A one-to-one relationship. If tables A and B are one-to-many, many-to-one, or many-to-many, how should we write A connection SQL statement?

In fact, the one-to-many SQL statements of the two tables are similar to those of the one-to-one SQL statement, but the query results are different. Of course, the two tables must be slightly changed.

For example, the column in table 1 can be changed:
Sno Name Cno
The columns in Table 2 can be changed:
Cno CName
In this way, the two tables can write one-to-many and one-to-one SQL statements, the same as the one-to-one SQL statements above.

The following describes how to create tables and some SQL statements when the two tables are multi-to-many.

Create three tables:

Table A: student:



Table B: course:



Table C: student_course:



One student can choose multiple courses and one course can be selected by multiple students. Therefore, student and course are many-to-many relationships.

When the two tables have multiple-to-many relationships, we need to create an intermediate table student_course. The intermediate table must have at least two primary keys. Of course, there can be other content.

SQL statement: select s. name, C. cname from student_course as SC left join student as s on s. sno = SC. sno left join course as c on c. cno = SC. cno

Execution result:



The execution result of this SQL statement is the course selection of students.

The following are special supplements:

Select * from (SELECT id, jihao, ht. hetongid, hetonghao, banshichu, zulindanwei, jiansheluduan, shebeijinchangriqi, hetongfangshi,
Jine, yusuangongchengliang, (ht. jine/30) * (datediff (day, ht. shebeijinchangriqi, getdate () as yusuanjine, yihuikuanjine,
(Ht. jine/30) * (datediff (day, ht. shebeijinchangriqi, getdate ()-yihuikuanjine) as qiankuanjine, ht. shebeituichangriqi
FROM db_hetongguanli_y_g as ht left join select sum (gongchengliang) as yusuangongchengliang, hetongid
From db_meirigongchengliang group by hetongid) as d on ht. hetongid = d. hetongid left
Join (select sum (shijihuikuanjine) as yihuikuanjine, hetongid from db_huikuan group by hetongid) as a on ht. hetongid = a. hetongid) as B
Where B. hetongfangshi = 'monthly rent 'and B. hetongid = (select hetongid from db_shebeixinxi where jihao =' "+ this. DropDownList1.SelectedValue + "')";

========================================================== ========================================================== ============================

Select * from (select ht. id, ht. qiandingren, ht. hetongid, ht. banshichu, ht. jihao, ht. hetonghao, ht. zulindanwei,
Ht. jiansheluduan, ht. qingqianriqi, ht. lumianshigongcailiao, ht. hetongfangshi, ht. jine, ht. hetongqiandingriqi,
Ht. hetongjiesuanjine, ht. hetongjiesuanriqi, ht. shebeijinchangriqi, ht. shebeituichangriqi, ht. jiesuangongchengliang,
D. yifukuanjine, (ht. hetongjiesuanjine-d.yifukuanjine) as qiankuanjine, ht. kaipiaojine, ht. ranyoufei, ht. yunfei,
Ht. qitafeiyong, ht. feiliaofei from db_hetongguanli_y_g as ht left join (select hetongid, sum (shijihuikuanjine) as yifukuanjine
From db_huikuan group by hetongid) as d on ht. hetongid = d. hetongid) as
Where a. hetongid = (select hetongid from db_shebeixinxi where jihao = '20140901 ')
Go