Differences between several database concepts

1. Where and having

The where clause search condition is applied before grouping, while the having search condition is applied after grouping. Therefore, a where clause is not followed by a gathering function (a function that calculates a column in a group of rows and returns a single value such as Count () sum ()).

2. exist and in

In is a set operator, with a column name on the front.

Exists is an existence judgment. If there is a result in the following query, exists is true; otherwise, it is false, and the front side cannot have any column names.

3. Internal Connection and external connection

Internal join: Check the data corresponding to the data in the two tables.
External join: Check the corresponding data based on a table (full join is based on multiple tables)

Student table
No name
1
2 B
3 C
4 d

Grade table
No grade
1 90
2 98
3 95

Join inner join (the data corresponding to the search condition is not listed in No4)
Syntax: Select * from student inner join grade on student. No = Grade. No
Result
Student. No name grade. No grade
1 A 1 90
2 B 2 98
3 C 3 95

Left join (all data in the left table, corresponding data in the right table)
Syntax: Select * from student left join grade on student. No = Grade. No
Result:
Student. No name grade. No grade
1 A 1 90
2 B 2 98
3 C 3 95
4 d

Right join (all data in the right table, corresponding data in the left table)
Syntax: Select * from student right join grade on student. No = Grade. No
Result:
Student. No name grade. No grade
1 A 1 90
2 B 2 98
3 C 3 95

Full connection
Syntax: Select * from student full join grade on student. No = Grade. No
Result:
No name grade
1 A 90
2 B 98
3 C 95
4 d
1 A 90
2 B 98
3 C 95

Note: you cannot directly use full join in access. You must use Union all to merge the left and right connections.