The SQL ON clause is used to create JOIN queries when information are located in different tables, i.e. allows to join related data tables by specifying a condition that involves a key field that identifies both tables, the key field contains data that is found in the first table and in the second table, and therefore both tables are related by this key field. Is optional you have these fields defined as a FOREIGN KEY between two tables, what is essential is that there are two fields that have the data to identify the records from both tables.

Following is the general syntax to use the SQL ON clause for two tables, using the JOIN query:

SELECT fieldNamess FROM table_1 AS one

JOIN table_2 AS two

ON one.field = two.field

When the type of JOIN (INNER, LEFT, RIGHT) is not specified the default JOIN is INNER JOIN, which returns a record when it meets the condition of the JOIN specified in the operator ON.

In the following example we are using the AdventureWorks
database, to get the name of the territory for the customers. The required information is contained in the tables, Sales.Customer and Sales.SalesTerritory
, the field that will serve as a JOIN condition is TerritoryID
, which will be used in the SQL ON operator. This field will allow us to relate data from both tables. In the table definition, Sales.Customer
has a FOREIGN KEY with the Sales.SalesTerritory

In the result query we will get the ID
and customer type as well as the account number from the Sales.Customer
table and the name of the territory from the Sales.SalesTerritory
table. The JOIN syntax is as follows:

, C.CustomerType
, C.AccountNumber
, T.Name
FROM Sales .SalesTerritory

INNER JOIN Sales .Customer

ON T.TerritoryID
= C.TerritoryID

As we see the INNER JOIN operator is used to mention the second table where we will find the additional information, later in the JOIN condition we use the SQL ON operator, placing the field that will allow us to join the information from the two tables. In the image we can see the output of the query:

责编内容来自:TheXploit (源链) | 更多关于

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 创业投资 » SQL ON

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录