Access tutorial 17.3 use SQL statements to join a table

In SQL, many of the power comes from the ability to join information in several tables or queries and display the results as a single logical record set. This JOIN operation includes the INNER, LEFT, and right join operations. First, let's talk about the usage of INNERJOIN: The INNERJOIN statement can be used to combine records in two tables, as long

In SQL, many of the power comes from the ability to join information in several tables or queries and display the results as a single logical record set. This JOIN operation includes the INNER, LEFT, and right join operations. First, let's talk about the use of inner join: the inner join statement can be used to combine records in two tables, as long

In SQL, a lot of power comes from the information of several tables or queries.JoinAnd display the result as a single logical record set. In thisJoinIncluding INNER, LEFT, right join operations.

First, let's talk about inner join usage: INNER JOINStatementIt can be used to combine records in two tables, as long as there is a consistent value in the public field. In syntax, inner join is defined in this way.

Getting startedTutorial17.3UseSQLStatementJoinTable "/>

For example, we want to add the "library table" and "press table"JoinAnd then list the books published by the publishing house. Let's first look at the two tables,

Getting startedTutorial17.3UseSQLStatementJoinTable "/>

Getting startedTutorial17.3UseSQLStatementJoinTable "/>

Enter the following content in the SQL design view:

Getting startedTutorial17.3UseSQLStatementJoinTable "/>

Now we execute this SQLStatement,

Getting startedTutorial17.3UseSQLStatementJoinTable "/>

We found that the current query results listed all the publishers and books.

This query may also use the followingStatement.

Getting startedTutorial17.3UseSQLStatementJoinTable "/>

After both queries are switched to the data table view, we will find that the query results of the two queries are the same,UseNew data can be added to the query of the inner join operation. Just like adding data to a table. WithoutUseIn the inner join operation, new data cannot be added to queries.UseThe inner join Operation queries two tables with relevant content.JoinThe newly generated table.

Now that we know its usefulness, let's look at the structure of left join:

FROM [Table Name 1] left join [Table name 2]
ON [Table Name 1. Field A] <relational operator> [Table name 2. Field B]

In fact, the left join function is to keep all records in table 1 on the LEFT of LEFT, and store field B and table name 1 in table 2 on the right. records corresponding to field a are displayed. Right join is opposite to left join.

So the example just now can also be written:

Getting startedTutorial17.3UseSQLStatementJoinTable "/>

Click the "execute" button on the toolbar. The displayed data table is the same as the data table displayed in the left join operation. Now we can see that their functions are interchangeable.

If you wantJoinIn the JOIN operation, we can perform nested operations. There are three tables: Table 1, table 2, and table 3.JoinUp:

FROM (Table 1 inner join table 2 ON table 1. No. = TABLE 2. No)
Inner join table 3
ON Table 1. Sequence Number = TABLE 3. Sequence Number

In this way, the three tables areJoin.