Oracle learning using keyword (detailed examples)

This article brings you relevant knowledge about Oracle, which mainly introduces the relevant knowledge about the using keyword. You can use the using keyword to simplify the connection query. I hope it will be helpful to everyone. .

Recommended tutorial: "Oracle Tutorial"

At work, I saw a SQL statement similar to the following:

select 
    tb.usrnm, 
    tb.typ, 
    tb.oprorder 
    from tb
    inner join rb1
    using (stfaprid) 
    where tb1.jugsumid = #jugsumid# 
    and tb1.blnorg = #blnorg# 
    and isvld = '1' 
    order by tb.typ asc, tb.oprorder asc

The sql/92 standard can use the using keyword to simplify connection queries, but the using keyword can only be used to simplify the query when it meets the following two conditions.

• 1. The query must be an equal value connection.
• 2. The columns in the equijoin must have the same name and data type.

For example: Use the using keyword, as follows:

select emptno,ename,sal,deptno,dname from emp e inner join dept d using(deptno);

If the above statement is executed, the result is the same as the result of natural connection.
        When using the using keyword to simplify the connection, you need to pay attention to the following points:

• 1. When using the deptno column in the emp table and the dept table to connect, in the using clause and select clause , you cannot specify a table name or table alias for the deptno column.
• 2. If the same multiple columns in the two tables are used in the connection query, then you can specify multiple column names in the using clause

The form is as follows:

select... from table1 inner join table2 using(column1,column2)

The above statement is equivalent to the following statement:

select... from table1 inner join table2
on table1.column1=table2.column2
and table1.column2=table2.column2;

If multiple tables are retrieved, you must use the using keyword multiple times to specify, in the following form:

select... from table1
inner join table2 using(column1)
inner join table3 using(column2);

The above statement is equivalent to the following statement:

select... from table1,table2,table3
where table1.column1=table2.column1
and table2.column2=table3.column2;

Further discussion on using

The using keyword in the join connection in Oracle is relative to the natural join.
We mentioned earlier that if natraul join is used, and if there are multiple fields with the same name and data type in the two tables, then these fields will be connected by Oracle on its own initiative.
But in fact, sometimes we don’t need to connect like this. We just need to pick one or two of their multiple fields with the same name and data type. At this time we need to use the using keyword. Below is an example.
There is a table called sales and a table called costs. There are two fields in these two tables, namely pro_id and time_id. We will not consider the actual meaning of the following connections for the time being, and only do grammatical research.
If you use natural connection, by default, the 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 natural joins, there is no chance to control the join conditions. Oracle naturally joins two fields with the same data type and name on its own initiative.
We use the using keyword below.

Select * from Sales join costs using(prod_id)

This forces Oracle to use the fields pointed out by using to connect, instead of the default two in the natural join connection.
Please note that the SQL statement here has no meaning. It is just a far-fetched example to illustrate the usage of using.
What needs to be explained here is:
If you use the using keyword, and the select result list item contains the keyword specified by the using keyword, then please do not include it in the select result list item. Indicate which table the keyword belongs to. For example, if using (prod_id) is used and the prod_id field is to be included in the result list, please do not write it as sales.prod_id or costs.prod_id but write it as prod_id, and do not use an alias. Just use the form prod_id as "product number", for example.

• Only one column name can be used in using.
• The natural join keyword and the using keyword are mutually exclusive, which means they cannot appear at the same time.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of Oracle learning using keyword (detailed examples). For more information, please follow other related articles on the PHP Chinese website!