Oracle's Join...using____oracle

The using keyword in a join connection in Oracle is relative to the natural join.

If you are using the Natraul join, and if more than one field in both tables has the same name and data type, the fields will be joined by Oracle on their own.

but there are times when we don't need to connect like this. We just need to select one or two of their multiple fields with the same name and data type. At this point we need to use the Using keyword.

here is an example.

in Oracle's example, the schema of SH has a table of sales, and a table is costs, and there are two fields in the two tables, pro_id and time_id respectively. For the moment, we do not consider the actual meaning of the connection below, only for grammatical research.

If you use a natural connection, by default two fields will be naturally connected together.

Select * from

Sales Natural join costs;

and

Select * from

Sales join costs on sales.prod_id = costs.prod_id and sales.time_id = costs.time_id

and

Select * from

Sales, costs

Where sales.pro_id = cost.prod_id

and sales.time_id = costs.time_id

the results should be the same.

If we use a natural connection, there is no chance to control the connection condition, and Oracle naturally joins the two fields of the same data type and name.

Let's use the Using keyword below.

Select * from

Sales join costs using (PROD_ID)

This forces Oracle to connect using the fields indicated by using, rather than the default two in the natural join connection.

The SQL statement here does not make any sense, just to illustrate a far-fetched example of using usage.

Here's what you need to say:

1. If you are using the Using keyword and the result list item for select contains the keyword specified by the Using keyword, do not indicate in the Select's Result list item which table it belongs to, for example, if you use a using (prod_id) , and if you want to include the prod_id field in the results list, do not write sales.prod_id or costs.prod_id instead of prod_id, and do not use aliases, such as the prod_id as "product number" form.

2. Only one column name can be used in a using.

3. The natural join keyword and the Using keyword are mutually exclusive, meaning they cannot occur simultaneously.