Use of the LEFT join, right join, and inner join in Access SQL

1. Table structure Table A table B

2. Left JOIN Example: 2.1

Select * from A left join B on a.aid = B.bid;

The left join is based on the records of Table A, a can be regarded as the right table, and B can be regarded as left table. In other words, the records of the left table A will all be represented, and the right table B will only display records that match the search criteria (in the example: A.aid = B.bid), and the table B is not sufficient to be null.

• All records in a table are displayed, and the corresponding contents are null for rows in table A that are not matched (such as aid=5, 6).
• The number of records returned must be greater than the number of records in table A, such as the aid=7 row in table A has been matched 3 times by the B table (because the B table has three rows bid=7).

Note: In Access, A.aid, B.bid cannot be abbreviated as aid, bid, or "link expression not supported", which is different from where query.

3. Right Join Example: 3.1

Select * from A Right join B on a.aid = B.bid;

Looking closely, you will find that the result of the left join is exactly the opposite, this time it is based on the right table (B), where a table is not enough to fill with null.

4. Inner Join Example: 4.1

Select * from A inner join B on a.aid = B.bid;

This shows only the records of A.aid = B.bid. This shows that inner join is not based on who, it only shows records that match the criteria.

Inner joins are equivalent to where queries such as:

Select * from A, B Where a.aid = b.bid

5. Table Association Modification and Deletion 5.1 example of modification: 5.1.1

Update A LEFT join B on a.aid = B.bidset A.aname = b.bname

The SQL actual operation table above is "SELECT * from A LEFT join B on a.aid = B.bid", so access prompts to update 13 records (the Select query records are 13). Comparing the results returned by "Example: 2.1", analyze the A table after the update:

• Aid=5, 6 's record, was updated to null
• Aid=7 's record, was updated 3 times, followed by "B1997-1", "B1997-2", "b1997-3", so the result is the last update "B1997-3"

For the above SQL can also change "A.aname = B.bname" to "B.bname = A.aname", the execution of table B will be modified, but after the execution of table B will add three lines "0, a2005-1;0, a2005-2;0, a2006", which is not difficult to understand, Because the LEFT join executes, table B will appear with three rows of null values.

Example: 5.1.2

A Where condition query can also be used in the SQL above, and the table that acts on it is also the associated table of the select query. The following SQL

Update A LEFT join B on a.aid = B.bidset a.aname = b.bnamewhere a.aid <> 5

Results of a table after execution:

Compare the first update to discover that the aid=5 has not been updated.

Only the LEFT join is described here because the process of right join and inner join is equivalent to the left join. In addition, the UPDATE statement in Access cannot contain the FROM keyword, which differs from other databases.

5.2 Delete

In Access it is not possible to delete a table's records through a left join, right join, Inner join

Example: 5.2.2

Delete from A inner join B on a.aid = B.bidwhere b.bname = "b1991"

The intent of the above SQL is to delete the records aid=1 in table A, but neither table a nor table B has changed since the execution. To achieve this, the following SQL can be implemented

Delete from Awhere a.aid in (select bid from B Where b.bname= "b1991")

6. Cartesian product

If a table has 20 records, B has 30 records, then the Cartesian 20*30=600 of the two are recorded. In other words, each record in table A is associated with all records in Table B, and three associations are actually different ways of handling "Cartesian product".

Use of the LEFT join, right join, and inner join in Access SQL