SQL ON

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 theSales.SalesTerritory table.

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:

SELECT  C.CustomerID , C.CustomerType ,C.AccountNumber ,T.Name    FROM Sales .SalesTerritory AS T

INNER JOIN Sales .Customer AS C

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:

{ 0 comments }