Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve multi-table join query in SQL, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. theory

As long as the common fields of the two tables have matching values, the records in the two tables are combined.

Personal understanding: use a common field to find the intersection of the two tables that meet the requirements, and merge the records of each table that meet the requirements with a common field as a guide.

Grammar

Select * FROM table1 INNER JOIN table2 ON table1. Field1 compopr table2. Field2

The INNER JOIN operation consists of the following sections:

The section describes the name of the table in which the records are to be combined by table1 and table2. The name of the field to be joined by field1,field2. If they are not numbers, these fields must have the same data type and contain similar data, but they do not have to have the same name. Compopr any relational comparison operator: "=", "", "=" or "".

Description

You can use INNER JOIN operations in any FROM clause. This is the most commonly used join type. As long as there are matching values on the common fields of the two tables, the Inner join combines the records in those tables.

You can use INNER JOIN with the Departments and Employees tables to select all employees in each department. To select all parts (even if there are no assigned employees in some departments) or all employees (even if some employees are not assigned to any department), you can create an outer join through a LEFT JOIN or RIGHT JOIN operation.

If you try to join fields that contain comments or OLE object data, an error will occur.

You can join any two similar types of numeric fields. For example, you can join autonumbered and long integer fields because they are of similar types. However, you cannot join single precision and double precision type fields.

The following example shows how to join the Categories and Products tables through the CategoryID field:

SELECT CategoryName, ProductName

FROM Categories INNER JOIN Products

ON Categories.CategoryID = Products.CategoryID

In the previous example, CategoryID is a joined field, but it is not included in the query output because it is not included in the SELECT statement. To include a joined field, include the field name in the SELECT statement, which in this case refers to Categories.CategoryID.

You can also link multiple ON clauses in a JOIN statement, using the following syntax:

SELECT fieldsFROM table1 INNER JOIN table2ON table1.field1 compopr table2.field1 ANDON table1.field2 compopr table2.field2 ORON table1.field3 compopr table2.field3

You can also nest JOIN statements with the following syntax:

SELECT fieldsFROM table1 INNER JOIN (table2 INNER JOIN [(] table3 [INNER JOIN [(] tablex [INNER JOIN...)]] ON table3.field3 compopr tablex.fieldx)] ON table2.field2 compopr table3.field3) ON table1.field1 compopr table2.field2

LEFT JOIN or RIGHT JOIN can be nested within INNER JOIN, but INNER JOIN cannot be nested within LEFT JOIN or RIGHT JOIN.

two。 Operation example

Table An is as follows: aID aNum1 a200501112 a200501123 a200501134 a200501145 a20050115

Table B is as follows: bID bName1 20060324012 20060324023 20060324034 20060324048 2006032408

The experiment is as follows: 1.left join

The sql statement is as follows: select * from Aleft join B on A.aID = B.bID

The results are as follows: aID aNum bID bName1 a20050111 1 20060324012 a20050112 2 20060324023 a20050113 3 20060324034 a20050114 4 20060324045 a20050115 NULL NULL (the number of rows affected is 5)

The results show that left join is based on the record of table A, A can be regarded as the left table, B can be regarded as the right table, and left join is based on the left table. In other words, the records in table An on the left will be fully represented, while those in table B on the right will only show records that meet the search criteria (in this case, A.aID = B.bID). The deficiencies in table B are all NULL.

The 2.right joinsql statement is as follows: select * from Aright join B on A.aID = B.bID the result is as follows: aID aNum bID bName1 a20050111 1 20060324012 a20050112 2 20060324023 a20050113 3 20060324034 a200501144 2006032404NULL NULL 8 2006032408 (the number of rows affected is 5 rows) the result shows that, contrary to the result of left join, this time it is based on table B on the right, and the deficiency in Table An is filled with NULL.

The 3.inner joinsql statement is as follows: select * from Ainnerjoin B on A.aID = B.bID

The results are as follows: aID aNum bID bName1 a20050111 1 20060324012 a20050112 2 20060324023 a20050113 3 20060324034 a200501144 2006032404

The results show that obviously, only the record of A.aID = B.bID is shown here. This shows that inner join is not based on anyone, it only displays records that meet the criteria. In addition, innerjoin can be used in conjunction with the where statement, such as: select * from An innerjoin B on A.aID = B.bID where b.bnameplate data 2006032401', then only one piece of data will be put back.

Summary: connect multiple tables to install the above three rules into a single table

The above is how to implement multi-table join query in SQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.