SQL query entry (Part 1)

Introduction
In the previous article (Introduction to SQL query (part I), I have explained the basic concepts of database query and Single Table query in detail. In this article, it mainly describes the various connections in SQL and the scope of use, and further explains the different ideas of relational algebra and relational algorithms for the same query.

Introduction to multi-table join
In relational databases, a query usually involves multiple tables, because few databases only have one table. If most queries only involve one table, that table is often lower than the third paradigm, and there are a lot of redundancy and exceptions.

Therefore, Join is an important means to Join multiple tables into one table.

For example, two simple tables are used to connect Student and Class tables ,:

After connection

Cartesian Product
The implementation of cartesian products in SQL is not only Cross Join ). All join methods are converted into temporary Cartesian Product tables. Cartesian product is a concept in relational algebra, which indicates any combination of each row of data in two tables, the join of two tables in is Cartesian Product (cross join)

In practical application, cartesian products are mostly useless. It is of practical significance only when restrictions are added to the join of two tables.

Internal Connection
If it is understood in different steps, the inner join can be considered as a cross join between two tables first, and then a subset of rows that do not meet the condition is removed by adding a restriction condition (on keyword in SQL, the result is an internal connection. in the above figure, if I add restrictions

For the two tables in the beginning, if the query statement is as follows:

Copy codeThe Code is as follows: SELECT *
FROM [Class] c
Inner join
[Student] s
On c. ClassID = s. StudentClassID

You can understand the partitions in the preceding query statement. First, you can concatenate the Class table and the Student table to generate the following table:

Then, only the StudentClassID and ClassID that are equalColumn(Marked in green ).Subset of a table

Of course, the restrictions after the on clause are not only equal signs, but also comparison operators, including> (greater than) and> = (greater than or equal), <=( less than or equal to), <(less than),!> (Not greater ),! <(Not less than) and <> (not equal ). Of course, the Data Types of the two columns involved in the condition must match.

For the preceding query statement, if you change the limit after on from equal to greater:

Copy codeThe Code is as follows: SELECT *
FROM [Class] c
Inner join
[Student] s
On c. ClassID> s. StudentClassID

Then, the result filters out the subsets whose ClassID is greater than StudentClassID from the Descartes of Step 1:

Although the table connected above does not have any practical significance, it is only used as a DEMO here :-)

Relational Algorithm
The concept of cartesian products above is the concept of relational algebra, and I mentioned in the previous article that there is a query method for relational calculus. the relational algebra above is understood as distribution, and the above statement derivation process is as follows: "The Student and Class in the table are connected internally, matching all the rows of ClassID and StudentClassID are equal, and all columns are selected"

The relational algorithm is more concerned with what I want. For example, the above query is the same. The method of thinking with relational algorithm is to "find information for all students, including their class information, class ID, student ID, Student name"

The SQL query statement using the relational algorithm is as follows:

Copy codeThe Code is as follows: SELECT *
FROM [Class] c
,
[Student] s
Where c. ClassID = s. StudentClassID

Of course, the returned results will not change after the query:

External Connection
Suppose we still have the above two tables, student and class. I add a student named Eric to the student, but for some reason I forgot to fill in its class ID:

When I want to execute a query like this: Give me the names of all students and their classes: