database table connection (inner connection, outer connection "left connection, right connection, full connection" cross connection)

Left JOIN and right connections:

Left JOIN: The query is based on left table, the left table data are all displayed, the right table shows all the data matching the left table, the mismatch is shown as null

Right connection (opposite to left join): the right table as the benchmark for the query, the right table data are all displayed, the left table and the right table matches the data are all displayed, the mismatch is displayed as null

Full connection (the combination of the left and right connections): first to left table as the benchmark for the query display, and then the right table as the benchmark for the query display

Benchmark: Check the restrictions on a table!

Book table

BookID	BookName	StudentID
1	Mansions	0003
2	Water Margin	0005
3	Kingdoms	0003
4	Journey to	0002
5	The flower of the Chao XI	0006
6	The Romance of the West Chamber	0010

Student table

StudentID	Studentname
0001	Zhang Fei
0002	Guan yu
0003	Harry
0004	John doe
0005	Zhao
0006	Bill
Null	Null

1. Internal connection:

Select * from [book] as b,[student] as where B.studentid=s.studentid

Equivalent to

Select * from [book] as-b inner join [student] as s on B.studentid=s.studentid

The result is:

BookID	BookName	StudentID	StudentID	Studentname
1	Mansions	0001	0001	Zhang Fei
2	Water Margin	0002	0002	Guan yu
3	Kingdoms	0003	0003	John doe
4	Journey to	0004	0004	Zhao
5	The flower of the Chao XI	0005	0005	Bill

2. Left connection

Select * from [book] as-B left joins [student] as s on B.studentid =s.studentid

Results

BookID	BookName	StudentID	StudentID	Studentname
1	Mansions	0003	0003	John doe
2	Water Margin	0005	0005	Zhao
3	Kingdoms	0003	0003	John doe
4	Journey to	0002	0002	Guan yu
5	The flower of the Chao XI	0006	0006	Bill
6	The Romance of the West Chamber	0010	Null	Null

3. Right Connection

select * from [book] as B right join [student] as S on b.studentid = S.studentid

BookID	BookName	Studentdi	StudentID	Studentname
Null	Null	Null	0001	Zhang Fei
4	Journey to	002	0002	Guan yu
3	Kingdoms	003	0003	Harry
Null	Null	Null	0004	John doe
2	Water Margin	005	0005	Zhao
5	The flower of the Chao XI	006	0006	Bill

4. Fully connected

select * from [book] as b full out join   [student] As s on b.studentid = S.studentid

BookID	BookName	StudentID	StudentID	Studentname

database table connection (inner connection, outer connection "left connection, right connection, full connection" cross connection)