Left Join and Right Join Operations
When used for the FROM clause, the source table record is combined. From table 1 [left | Right] JOIN Table 2 ON Table 1. Field 1 CompoPR Table 2. Field 2
Some describe the name of Table1, Table2 records the combined table. Field1, Field2 The name of the field being coupled. And these fields must have the same data type and data containing the same type, but they don't need the same name. CompoPR Any relationship comparative operation operator: "=," <, "">, "<=," "> =," or "<>"
Description
Create a left external join with Left Join operation. The external connection of the left will contain all the records in the two tables starting from the first (left), even in the second (right side) table, there is no synergistic record.
Use the Right Join operation to create the right external join. The right external join will contain all records in the two tables starting from the second (right), even in the first (left) table, there is no matching record.
For example, you can use the LEFT JOIN and the department (left) and employee (right) table to select all departments, including the department that is not assigned to the employee. All employees can be selected using Right Join, including employees that are not assigned to the department.
The following example shows how to join class tables and product tables in the class identifier field. The query will list all kinds of lists that include those who do not have products in it:
Select categoryname,
ProductName, PRODUCTNAME
From categories left join products
ON categories.categoryid = products.categoryId;
In this example, CategoryID is a join field, but because it is not included in the SELECT statement, it is not included in the query result. To include the link field, enter a field name in the SELECT statement - in this example for categories.categoryID.
note
To create a query that only contains the same records as the data in the join field, use the Inner Join.
You can write a nested LEFT JOIN or a Right Join in Inner Join, but you cannot write nested inner Join in an Left Join or a Right Join. Please refer to the INNER JOIN topic for discussions using nested, from which to know how to write nested joints in other connections. You can link multiple ON clauses. For more information, see the discussion of the clause links in the INNER JOIN topic.
If you try to join a field containing MEMO or OLECT data, it will cause errors.
LEFT JOIN, RIGHT JOIN operation example
This example assumes that there is a false unit name and field identifier in the employee table. Note that these fields do not exist in the Northwind database employee table.
This example selects all units that contain those units that do not contain employees.
This example calls the process ENUMFIELDS process and can be found in the SELECT statement example.
S ub leftrightjoinx ()
D IM DBS AS Database, RST As Recordset
'Modify this exercise in your computer correctly refers to the path of Northwind.
S et dbs = OpenDatabase ("northwind.mdb")
'Orders for shipping costs more than $ 100,' Select all units, including
'No employee
S et = dbs.openrecordset_
("S elect [department name]," _
& "FirstName & Chr (32) & Lastname As Name" _
& "From departments left join employees" _
& "On departments. [Department id] =" _
& "EMPLOYEES. [Department ID]" _
& "ORDER BY [Department Name];")
'PopulateRecordset.
R st.movelast
'Call EnumFields to print the contents of the record set.
'Transfer records objects and requirements of the required character width.
'
E Numfields RST, 20
D bs.close
E ND SUB
