SQL statement similar to the following is displayed:

select sum(count) from (select count(1) count from tb left outer join WFWKSEQTAB on tb.JUGSUMID = tb1.SEQNO inner join cdeorg on tb.ibkcde = cdeorg.ibkcde where 1 = 1 AND (FNAUTM IS NULL or fnautm = 0) AND (tb.FLWSTS ! = '3' or tb.FLWSTS IS NULL) and UPPER(tb.customername) like '%' || 'shq_test_20180302' || '%' ESCAPE '/' and tb.biztyp in ('1', '2', '5', '02') and tb.jugsumid in (select jugsumid from casaprlogtab where (1 = 1)) union select count(1) count from tb...Copy the code
  • Union: Union two result sets, excluding duplicate rows, and sort the default rules at the same time;
  • Union All: Performs the Union operation on two result sets, including repeating rows without sorting;

1. The purpose of the Union directive is to merge the results of two SQL statements.

SELECT Date FROM Store_Information
UNION 
SELECT Date FROM Internet_Sales;
Copy the code

Pay attention to

  • In the union usage, the field types of the two SELECT statements match and the field types must be the same.

In the actual software development process, as shown in the example above, more complex situations can be encountered, as described below

select '1' as type,FL_ID ,FL_CODE,FL_CNAME,_FLDA,FL_PARENTID from FLDA WHERE ZT_ID = 2006030002 union SELECT '2' as Tpye,XM_ID,_XM_CODE,XM_CNAME,FL_ID FROM XMDA WHERE exists (SELECT * FROM (SELECT FL_ID FROM FLDA WHERE ZT_ID=200603002 )  a where XMDA.fl_id=fl_id) order by type,FL_PARENTID , FL_ID;Copy the code

FL_ID = ‘FL_ID’; FL_ID = ‘FL_ID’; FL_ID = ‘FL_ID’; FL_ID = ‘UNION’; FL_ID = ‘FL_ID’;

UNION will filter out duplicate records after table join, so it will sort the result set generated after table join, delete duplicate records and then return the result.

2. UNION ALL will be encountered in the query, and its usage is the same as that of UNION, except that UNION contains the distinct function, which will remove the duplicate records in two tables, while UNION ALL does not. Therefore, in terms of efficiency, UNION ALL will be a little higher, but not much is actually used.

The header uses the field of the first join block, whereas the UNION all simply merges the two results and returns them. In this way, if the two result sets are returned with duplicate data, the result set is returned with half duplicate data.

From the point of view of efficiency, UNION ALL is much faster than UNION. Therefore, if it can be confirmed that the two result sets combined do not contain duplicate data, UNION ALL is used as follows: Try to use UNION ALL, because UNION requires sorting and removing duplicate records, which is inefficient.

Pay attention to

  • Union can merge result sets with different field names but the same data type;
  • If a result set with a different field name is Union, the Order BY clause for that field is invalidated;
  • Union, perform the Union operation on two result sets, excluding duplicate lines, and sort the default rules at the same time;
  • Union All, perform the Union operation on the two result sets, including repeating rows without sorting;
  • You can change the sorting by specifying the Order by clause in the last result set.

Use keywords to learn

In addition, a SQL statement similar to the following is found in the work:

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 
Copy the code

The SQL /92 standard can use the using keyword to simplify join queries, but only if the query meets the following two conditions.

  • 1. The query must be an equivalent join.
  • 2. Columns in the equivalent join 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); 
Copy the code

The result of the above statement is the same as the result of the natural join. When using the using keyword to simplify connections, note the following:

  • 1. Do not specify a table name or alias for deptno in the using clause or select clause when emP and DEPT are joined.
  • 2. You can specify multiple column names in the using clause if the same columns from two tables are used in a join query

The form is as follows:

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

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;
Copy the code

If multiple tables are retrieved, the using keyword must be specified multiple times, in the form of:

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

The above statement is equivalent to the following statement:

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

Reconsider the using

Oracle uses the using keyword in a join as opposed to a natural join.

As we mentioned earlier, if you use natRaul JOIN, and if there are multiple fields in two tables with the same name and data type, oracle will join them at its own discretion.

But sometimes we don’t need to do that. We just need to pick one or two of their multiple fields with the same name and data type. This is where we need the using keyword. Here’s an example.

There is a table named SALES and a table named Costs, and both tables have two fields named PRO_id and time_id. Let us leave aside for the moment the practical implications of the following connection for grammatical study.

If you use a natural join, the two fields will be joined together naturally by default.

Select * from Sales natural join costs;
Copy the code

and

Select * from Sales join costs on Sales.prod_id = costs.prod_id and sales.time_id = costs.time_id
Copy the code

and

Select * from Sales ,costs Where Sales.pro_id = cost.prod_id
and sales.time_id = costs.time_id
Copy the code

I should get the same answer. If we use a natural join, we have no chance to control the join conditions. Oracle takes the liberty of joining two fields of the same data type and name together.

Next we use the using keyword.

Select * from Sales join costs using(prod_id)
Copy the code

This forces Oracle to use the fields indicated by using for joins instead of the default two in natural Join joins.

Note that the SQL statement here does not make any sense and is just a weak example to illustrate the use of using. There are other things to note here: If you use the using keyword and the select result list item contains the keyword specified by the using keyword, do not specify which table the keyword belongs to in the select result list item. For example, if using(prod_id) is used, To include the prod_id field in the result list, do not write sales. Prod_id or costs. Prod_id, write prod_id, and do not use aliases, such as prod_id as “product number.”

  • Only one column name can be used in using.
  • The natural join keyword and the using keyword are mutually exclusive, meaning that they cannot occur together.