Left join of access

Source: Internet
Author: User

There is no profound technology to discuss in this article, but it is just a blind spot in your knowledge: I don't know how to perform left join operations in access. After searching on the internet, I finally found the answer on csdn. Therefore, I think it is necessary to record the answer :)
Recently, in this project, we need to import data from an Access database to Oracle. At first, I thought it was easy to use the SQL-Server Import and Export tool. Then I found that some field information was not directly imported, so I had to work out a program myself.
First, list the problem, as shown in. The problem is clear at a glance and no other nonsense is needed.

So here we will also write down the Oracle left join operation, which is a small summary. The Oracle left join SQL statement is as follows: Select corpname, labor, acreage, detail from, B Where B. id =. ID (+) order by B. id ASC
The left join SQL statement of access is as follows: Select corpname, labor, acreage, detail from B left join a on B. ID = A. ID order by B. ID ASC

Therefore, it is not difficult to introduce the corresponding right connection operation method.
The problem is that it is easy to solve, but there are new questions. What databases are supported for these two different usages? What are the advantages and disadvantages of performance between them? As a result, I began to seek a chat on the Internet and figured out some of my thoughts, which may be wrong. Please give me some advice!
Some of the content is captured from the Internet, but the source is not found!

1. Outer Join) 

SQL-92 standard (the official name of the SQL standard is ISO/IEC 9705 "database language SQL ". The latest version is called ISO/IEC 9075: 2003 or SQL: 2003. This version was earlier than SQL: 1999 and SQL-92. If you are interested, go to SQL standards to see different standards and their differences.) The Connection syntax format of the from clause is as follows:
From join_table join_type join_table [ON (join_condition# sqlsqlsql92standardjoinsyntax.pdf

Join_table indicates the name of the table involved in the join operation. The join operation can be performed on the same table or on multiple tables. The Join Operation on the same table is also called a self-Join Operation. Join_type indicates the connection type. There are three types: inner join, outer join, and cross join ).
For external connections, the ms SQL-server supports two forms:
1. The format inherited from Sybase
Field 1 * = Field 2 (left join)
Field 1 = * Field 2 (Right join)
There is no full outer connection in this form.
2. Standard Outer Join syntax
Left [outer] Join on Expression
Right [outer] Join on Expression
Full [outer] Join on Expression
For Oracle, there are only two external connection methods for Oracle during 8i: left and right connections. After 9i, Oracle also supports standard external connection syntax, so it is also two ways
1. Field 1 = Field 2 (+) (left join)
Field 1 (+) = Field 2 (Right join)
There is no full external connection in this form.
2. Standard Outer Join syntax
Left [outer] Join on Expression
Right [outer] Join on Expression
Full [outer] Join on Expression
Here, we only compare ms SQL Server and Oracle, and do not list other databases such as MySQL and PostgreSQL.
Ii. Possible Optimization Methods for Join Operations
I think the so-called optimization is still a matter of opinion and wisdom, which needs to be determined based on the actual situation. Because you have little experience, you can only talk about some common optimizations. If you have any mistakes, please give me more advice.
1. for multiple tables that require frequent Join Operations (especially tables with large data volumes), each table should be indexed on the join operation fields;
2. Do not directly select * After Select, as long as you need to select columns;
3. The fields in the table to be joined should be those with more unique values;
When I wrote this article, I really couldn't think of anything else, so I went to the Internet to search for an article about join performance, as a result, I found a good article (only English Version). Many of my ideas and articles have included this article. The article mainly focuses on the Performance of join in ms SQL Server, but I think it is still universal.
Everybody can look at http://www.sql-server-performance.com/tuning_joins.asp here
If the web page is slow, you can also click to download joincecece

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.