oracle database: table connection

Table connection introduction

When the data we get is not from the same table, but from multiple tables, we need to use the table connection. Table join is the process of joining rows of one table with rows of another table to form new rows according to specified conditions.
To put it simply, we store data in different tables, and different tables have their own table structure, and different tables can be associated. In most practical applications, we don’t just need the information of a table, such as You need to find out the students in Beijing area from a class table, and then use this information to retrieve their mathematics scores in the score table. If there is no multi-table connection, you can only manually query the information in the first table as the first It is conceivable that this process is very cumbersome to query the final results from the retrieval information of the two tables.

Connect query

  1. That is, multiple tables (especially those with foreign key relationships) are required when querying, and the values ​​between multiple tables are required to connect;
  2. Currently SQLthe standard query raised two connections, the first one is the older SQL92standard, and the second is the widespread use of newer SQL99standards
  3. 92The form is simple, but the writing is more verbose. 99 is not only optimized at the bottom level, but also looks clearer in form and more logical. It is generally recommended to use the 99standard.

92 Grammar

Multiple tables need to be placed fromafter all, and all the connection conditions are placed in whereit, so the equivalent join, non-equivalent join, outer join, etc. in SQL92 are actually only whereconditional filtering
structure: select ... from table1,table2,table3,... where ...
many times you need to alias the table ( 1. Simplify the name of the table 2. There may be self-joining)
The principle of connection: according fromto the order of appearance of the following tables, the previous table is used as a memory forloop, and the following table is used as an outer forloop

Cartesian Product

Those who have studied linear algebra know that the Cartesian product, in layman's terms, is that each member of two sets is related to any member of the other set.
For example, there is an attendance record sheet that records the attendance information of 100 people in April 2020. In theory, these people have records every day, but in fact, some people have missing data on certain days. To find out the missing data, it is a little troublesome to query day by day or query by one person. In this case, you can do a Cartesian product processing for each person and each day, and then associate it with the actual table. It's easy to find out the results.
select ... from t1,t2

Here first prepare the required data: 学生表stu、班级表cla

--创建学生表
create table stu(
       name varchar(20),
       age number(3),
       sex varchar(10)
);
--往学生表里面添加数据
insert into stu values('赵丽颖',22,'女');
insert into stu values('迪丽热巴',24,'女');
insert into stu values('杨紫',23,'女');
--创建班级表
create table cla(
       cla_num number(2),
       cl_name varchar(20)
);
--往班级表里面添加数据
insert into cla values(11,'七年级一班');
insert into cla values(12,'七年级二班');
insert into cla values(21,'八年级一班');
insert into cla values(22,'八年级二班');

The data content is shown in the following figure:
stu表Next, let's look at the Cartesian product of these two tables:

Insert picture description here


cla表

Insert picture description here


select * from stu,cla;

Here you can query all the data in the two tables at the same time,

Insert picture description here


you can see that the data is a combination of the data in the two tables, and it becomes a 3*4=12table of data

Equivalent connection

Taking the same value of the condition column on the basis of Descartes is equivalent connection. Here we modify the above data so that they have the same column:

alter table stu add (cla_num number(2));
update stu set cla_num = 11 where name = '赵丽颖';
update stu set cla_num = 12 where name = '迪丽热巴';
update stu set cla_num = 21 where name = '杨紫';

Check the stu表content:

Insert picture description here


Next, check the information of each student and the information of the class.

  1. Query 学生信息data: ,班级信息
  2. Data Sources:stu表和cla表
  3. Join stu.cla_num = cla.cla_numconditions: . The content of this field in the two tables is the same. [If there is no condition, it is Cartesian product]
--查询一下每一个学生的信息以及所在班级的信息
select * from stu s,cla a where s.cla_num = a.cla_num;

Here the way the table played an alias, respectively, sand a, behind the judge two fields in the table should be the same, look at the results:

Insert picture description here


we continue to go stuinside to add a few data:

insert into stu values('周杰伦',24,'男',11);
insert into stu values('彭于晏',23,'男',12);
insert into stu values('胡歌',22,'男',22);
insert into stu values('貂蝉',18,'女',12);
insert into stu values('王昭君',19,'女',21);

This is the data in the table as shown in the figure below:

Insert picture description here


Next, let's make a small example: query every class and the number of students in each class where students exist [all exist here].

  1. Query data: class information, the number of students in the corresponding class
  2. Source:stu , clahas been calculated to give
  3. Connection condition: the class numbers are equal

The first step is to query how many students correspond to each class number

--查询有学生的班级的学生人数:
select count(*),cla_num from stu group by cla_num;

The result is as follows:

Insert picture description here


You can see how many students correspond to each class number. Next, use the above result set table to query

select * from cla c,(select count(*),cla_num from stu group by cla_num) b where c.cla_num = b.cla_num;

Here is an alias for the result set table to bfacilitate whereconditional judgment later

Insert picture description here

Non-equivalent join

Common operators for non-equivalent concatenation:

!=,>,<,<>,between and

In order to demonstrate the example of non-equivalent join, here need to add a field, and a new table grade, indicating the grade level

--添加成绩字段
alter table stu add (gra_num number(3));
--添加成绩
update stu set gra_num = 89 where name = '赵丽颖';
update stu set gra_num = 99 where name = '迪丽热巴';
update stu set gra_num = 67 where name = '胡歌';
update stu set gra_num = 58 where name = '周杰伦';
update stu set gra_num = 72 where name = '迪丽热巴';
update stu set gra_num = 100 where name = '王昭君';
update stu set gra_num = 69 where name = '彭于晏';
update stu set gra_num = 70 where name = '貂蝉';
update stu set gra_num = 86 where name = '杨紫';
--创建成绩等级表
create table grade(
       grade varchar(20),
       lowgra number(3),
       higra number(3)
);
--添加成绩等级表的内容
insert into grade values('完美',100,100);
insert into grade values('优秀',80,99);
insert into grade values('良好',70,79);
insert into grade values('及格',60,69);
insert into grade values('不及格',0,59);

Next look at the contents of the two tables:
stu表

Insert picture description here


gradeTable

Insert picture description here


scores are divided into 5 levels, as shown in the figure above.
Example 1: Query student information and grade of achievement, grade comes from grade table

  1. Query data:name,sex,gra_num,grade
  2. Data Sources:stu表和grade表
  3. Link gra_num 在grade表中字段lowgra和higra之间conditions: .
select name,sex,gra_num,grade from stu s,grade g where s.gra_num between lowgra and higra;

The alias method is also used here. The following conditions determine which level the score is in, and then the level number is obtained. The result is shown in the figure below.

Insert picture description here

Outer join

Inner joins use comparison operators to match rows in the two tables based on the values ​​of the columns common to each table. The external connection can be a left external connection, a right external connection, or a complete external connection.

  • The result set of the left outer join includes all rows of the left table specified in the left outer clause, not just the rows matched by the join column. If a row of the left table does not have a matching row in the right table, in the associated result set, all the select list columns of the right table are null values
  • The right outer link is the reverse link of the left outer link. All rows of the right table will be returned. If a row of the right table does not have a matching row in the left table, a null value will be returned for the left table.

Here to add a data to the class table:

 insert into cla values(31,'九年级一班');

At this time we knew that there were no students in the first class of grade ninth.
Example: View the information of each student and class information:

 select * from cla c,(select count(*),cla_num from stu group by cla_num) b where c.cla_num=b.cla_num;

The method of internal connection is used. Because there is no one in the ninth grade first class, it is not displayed, as shown in the figure below.

Insert picture description here

Sometimes, even if there is no one in this class, you want to display this class when you query. There is a class but no one is different from not having this class. At this time, you need to use an external connection.
The outer join has the concept of a main table, as long as the result appears in the main table, it must appear in the result set. 【看+号,带+号的表为从表,对立面的表为主表

Example: View the information of each class and the corresponding number of students

  1. Query data:班级信息,班级人数
  2. Data Sources:cla,结果集表
  3. Connection conditions:cla_num相等
  4. Main table:, 班级表Slave table:学生表
select * from cla c,(select count(*),cla_num from stu group by cla_num) b where c.cla_num = b.cla_num(+);

Here is added after the result set table (+), which means this is 从表, the main table cla表

Insert picture description here


can be seen here, all classes are displayed, and the class without students is automatically empty.
If you want to set the null value to 0, you can usenvl函数

select c.cla_num,cl_name,nvl(cc,0) cou from cla c,(select count(*) cc,cla_num from stu group by cla_num) b where c.cla_num = b.cla_num(+);

Here we give count(*)the alias cc, and then use the nvl function. If count(*)the return value is empty, replace it with 0, and then give this column an ​​alias of cou.

Insert picture description here


You can see that the originally empty value has become 0, which makes it easier for us to read the result.

Extended exercise: Add a data in the stu table, but there is no class information, and then query all student information and the information of his class. If there is no class, use "no class" to replace the null value. It is not demonstrated here, it is very similar to the above example.

Self-connection

Special equivalence exchange, the data comes from the same table.
In order to demonstrate this self-connection effect, stuadd two fields to the table, one is 学生编号and the other is 组长编号, respectively stu_num,group_num, each student has a student number, but not everyone has a group leader.

--添加学生id字段
alter table stu add(stu_num number(2));
--给学生添加学生id
update stu set stu_num = 1 where name = '赵丽颖';
update stu set stu_num = 2 where name = '迪丽热巴';
update stu set stu_num = 3 where name = '杨紫';
update stu set stu_num = 4 where name = '周杰伦';
update stu set stu_num = 5 where name = '彭于晏';
update stu set stu_num = 6 where name = '胡歌';
update stu set stu_num = 7 where name = '貂蝉';
update stu set stu_num = 8 where name = '王昭君';
--添加组长字段
alter table stu add(group_num number(2));
update stu set group_num = 1 where name = '周杰伦';
update stu set group_num = 2 where name = '彭于晏';
update stu set group_num = 3 where name = '胡歌';
update stu set group_num = 8 where name = '貂蝉';

Check stu表the content after adding :

Insert picture description here


They are the student number and the number of the respective group leader. Of course, the group leader himself does not have a group leader.
Now that the data is available, let’s make an example: query each student [student with a group leader]’s own information and his group leader’s information.

  1. Query data:学生自己的信息,组长的信息
  2. Source:stu表 s1 ,stu表 s2
  3. Connection conditions:s1.stu_num = s2.group_num
select * from stu s1,stu s2 where s1.stu_num = s2.group_num;

An alias must be given here, otherwise an error will be reported. Let's take a look at the result:

Insert picture description here


Self-join is generally used to process query processing between people with different identities in the same table.

99 Grammar

  1. cross join Cross-connect to realize Cartesian product.
  2. natural join Natural connection, equivalent connection (need to have the same name column, primary and foreign key)
  3. join using Equivalent connection [columns with the same name]
  4. join on onConnection, can be equivalent connection, non-equivalent connection, self-connection, can solve all connections, the relationship column must be distinguished
  5. outer join External connection, there is a difference between master and slave tables
  6. full join on | usingFully connected, directly match if satisfied, and complement each other if not satisfied null, to ensure that all table records appear at least once

Example 1: 99标准realize the Cartesian product
here in order to make the results less, using cla表andgrade表

 --99标准实现笛卡尔积
select * from cla cross join grade;

The result is as follows:

Insert picture description here


Example 2, 99 standard realizes equivalence connection: View the name, age, gender, class, class name of all students

  1. Data to be queried: view the name, age, gender, class, class name of all students
  2. Data source: stu表andcla表
  3. Connection conditions:stu.cla_num = cla.cla_num
  4. 99标准Can be achieved using natural connections natural join
select name,age,sex,cla_num,cl_name from stu natural join cla;

There is no need to use join conditions here, because after 99标准use natural join, columns with the same name or primary and foreign keys will be automatically found. Example: Equivalent joins can also be made. At this time , columns with the same name are required.

Insert picture description here


join using

select name,age,sex,cla_num,cl_name from stu join cla using (cla_num);

The main attention here is the grammatical format, usingfollowed by the column with the same name, indicating which column is used to connect.

Insert picture description here


Summary of 99 Standards:

  • corss join Cross-connect [There is nothing to say about this, it is the Cartesian product]
  • natural join There must be columns with the same name, and there is a primary and foreign key relationship (just a column with the same name): Equivalent joins can be made
  • join usingThere must be columns with the same name, no primary and foreign keys are required, there can be multiple columns with the same name, use the same name column specified using using: Equivalent connection
    example: query the class number is 12all student names, class number, class name
    1, natural connection realization :
--自然连接实现
select name,cla_num,cl_name from stu natural join cla where cla_num = 12;

2. suingConnection realization:

--using连接实现
select name,cla_num,cl_name from stu join cla using(cla_num) where cla_num = 12;

The results are the same, as shown in the following figure:

Insert picture description here


Example: jion onRealize non-equivalent connection:
query the name, student number, class number, class name of all students

  • Query data: student's name, student number, class number, class name
  • Data source: stu表andcla表
  • Connection conditions:stu.cla_num = cla.cla_num
--jion on 实现非等值连接:
select name,stu_num,s.cla_num,cl_name from stu s join cla c on s.cla_num = c.cla_num;

What is used here is join onto achieve equivalence connection.

Insert picture description here


There is a little knowledge point. If you are using natural connection or usingconnection, you do not need to add qualifiers for columns with the same name, because the columns with the same name will be found automatically. If you are using it join on, you need to add a qualifier.
Example: jion onRealize non-equivalent connection
View each student’s name, grade, age, grade level

select name,age,gra_num,grade from stu join grade on gra_num between lowgra and higra;

Take a look at the results:

Insert picture description here


Example: query the 12name, age, class number, grade level, class name of the class student

  1. Query 学生姓名data: 年龄, 班级编号, 成绩等级, ,班级名称
  2. Data Sources:stu,cla,grade
  3. Join conditions: gra_num between lowgra and higra, stu.cla_num = cla.cla_num.
--查询12班级学生姓名,年龄,班级编号,成绩等级,班级名称
select name,age,s.cla_num,grade,cl_name from stu s 
join cla c on s.cla_num = c.cla_num 
join grade g on s.gra_num 
between g.lowgra and g.higra;

Here, the joinfront of each second can be used as a result table, joinwhich is connected with the following table

Insert picture description here

You can query the required data correctly.

Example: View each student ID, student name, group leader ID, group leader name [ outer join]

  • Query 学生编号data: 学生名称, 组长编号, ,组长名称
  • Source:stu s1 ,stu s2
  • Connection conditions:s1.stu_num = s2.group_num
  • on External connection
 select s1.stu_num,s1.name,s1.group_num,s2.stu_num from stu s1 left outer join stu s2 on s1.group_num = s2.stu_num;

Here, because the main table is the first one stu表on jointhe left side, it must be specified left. The outer join can be omitted outer, but it is not omitted here.

Insert picture description here


The right outer join is the keyword leftreplaced by one right, which is not demonstrated here.

Table connection summary:

  • Table connection: When the query data comes from multiple tables
  • 1. Cartesian product
  • 2. Equivalent connection
  • 3. Non-equivalent connection
  • 4. Self-connection
  • 5. External connection (main table)
--92标准:
select ... from table1,table2 where table1.xx1 = table2.xx2 and table1.xx = cc;
select ... from table1,table2 where table1.xx1 = table2.xx2(+);
 -- 99标准:
select ... from table1 cross join table2 where ...
select ... from table1 natural join table2 where ...
select ... from table1 join table2 using(同名字段) where ...
select ... from table1 join table2 on 连接条件 where ...
select ... from table1 left/right outer join table2 on 连接条件 where ...
`--全连接`
select ... from table1 full join table2 on 连接条件 where ...

Full connection means that the two tables are the main tables, and the contents of each table must appear, and the ones that are not are replaced by null.