AD

Problem between the Oracle result set (product and cross)

Oracle uses the MINUS and UNION

MINUS command is used in SQL statements on the two. It is the first to find the first SQL statement is the result, and then look at these results are not in the second SQL statement results. If

, Then the amount of information was removed, and will not appear in the final result. If the second SQL statement, the result does not exist in the first SQL statement is the result

Within this information that was abandoned.
MINUS syntax is as follows:
[SQL Statement 1]
MINUS
[SQL Statement 2]
We continue to use the same example:
Store_Information form
store_name Sales Date
Los Angeles $ 1500 Jan-05-1999
San Diego $ 250 Jan-07-1999
Los Angeles $ 300 Jan-08-1999
Boston $ 700 Jan-08-1999

Internet Sales Form Date Sales
Jan-07-1999 $ 250
Jan-10-1999 $ 535
Jan-11-1999 $ 320
Jan-12-1999 $ 750

We want to know what is store a few days without Internet sales turnover. To achieve this, we use the following SQL statement:
SELECT Date FROM Store_Information
MINUS
SELECT Date FROM Internet_Sales
Results:
Date
Jan-05-1999
Jan-08-1999

"Jan-05-1999", "Jan-07-1999", and "Jan-08-1999" is "SELECT Date FROM Store_Information" the result. In it, "Jan-07-1999"

Is present in the "SELECT Date FROM Internet_Sales" produced results. Therefore, "Jan-07-1999" is not in the final results.
Please note that the MINUS command, different values will only be listed once. The UNION SQL UNION directive aims to use two SQL statements combined the results. From this point of view

Of view, UNION with JOIN slightly similar, since both instructions can retrieve data from the multiple tables. UNION's a limit of two SQL statements generated by the field needs

The same types of information. In addition, when we UNION this command, we will only see the different data values (similar to SELECT DISTINCT). UNION syntax is as follows: [SQL language

Sentence 1]
UNION
[SQL Statement 2] assume that we have the following two tables, forms: Store_Informationstore_name Sales Date Los Angeles $ 1500

Jan-05-1999
San Diego $ 250 Jan-07-1999
Los Angeles $ 300 Jan-08-1999
Boston $ 700 Jan-08-1999
Form: Internet SalesDate Sales Jan-07-1999 $ 250
Jan-10-1999 $ 535
Jan-11-1999 $ 320
Jan-12-1999 $ 750 and we find out all revenue (sales) day. To achieve this, we use the following SQL statement: SELECT Date FROM

Store_Information
UNION
SELECT Date FROM Internet_Sales results: Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999
One thing worth noting is that if we in any SQL statement (or two all together) with "SELECT DISTINCT Date", then
Then we will get exactly the same results. Name can be different. However, the data type must be similar.
That is the main table (first table) The first column is varchar (100), Vice-table (all tables other than the first one) to be the first column is varchar (100 & & <100) (char if you can

You try it, or can automatically convert data types), the other does not work, there are several table columns must be the same

-------------------------------------------------- -------------------------------------------------- ---------------------------

Suppose we have a table Student, including the following fields and data:

drop table student;

create table student

(

id int primary key,

name nvarchar2 (50) not null,

score number not null

);

insert into student values (1, 'Aaron', 78);

insert into student values (2, 'Bill', 76);

insert into student values (3, 'Cindy', 89);

insert into student values (4, 'Damon', 90);

insert into student values (5, 'Ella', 73);

insert into student values (6, 'Frado', 61);

insert into student values (7, 'Gill', 99);

insert into student values (8, 'Hellen', 56);

insert into student values (9, 'Ivan', 93);

insert into student values (10, 'Jay', 90);

commit;

The difference between Union and Union All.

select *

from student

where id <4

union

select *

from student

where id> 2 and id <6

The result will be

1 Aaron 78

2 Bill 76

3 Cindy 89

4 Damon 90

5 Ella 73

If changed to Union All connecting the two result sets, the return result:

1 Aaron 78

2 Bill 76

3 Cindy 89

3 Cindy 89

4 Damon 90

5 Ella 73

You can see, Union and Union All the difference is one of the results of the repeated treatment.

Next we will check the order of the two sub-adjust, to

- Union

select *

from student

where id> 2 and id <6

union

select *

from student

where id <4

Implementation of the results to see if consistent with your expectations?

- Union All

select *

from student

where id> 2 and id <6

union all

select *

from student

where id <4

Well, this?

Accordingly, we can see the difference between the two is on the order of processing. Union All will be organized according to the order of associated data, while the Union will be sorted according to certain rules. Then the rules? We take a look at another query method:

select score, id, name

from student

where id> 2 and id <6

union

select score, id, name

from student

where id <4

The results are as follows:

73 5 Ella

76 2 Bill

78 1 Aaron

89 3 Cindy

90 4 Damon

And we expect the same: to be in accordance with the field sort order. Before our inquiry is based on the id, name, score a field order, then the result set will be sorted according to priority id; and now the new field has also changed the order of ranking of query results. And is based on a given field, a, b, c. .. in the order of the order by. The result is that order by a, b, c. ..........'s. We see next a query:

select score, id, name

from student

where id> 2

union

select score, id, name

from student

where id <4

The results are as follows:

56 8 Hellen

61 6 Frado

73 5 Ella

76 2 Bill

78 1 Aaron

89 3 Cindy

90 4 Damon

90 10 Jay

93 9 Ivan

99 7 Gill

Can see the records for the same score, will follow the next sort field id. If we want to control their own sort, is not specified with the order by can be out? The answer is yes, but in the wording of the need to pay attention to:

select score, id, name

from student

where id> 2 and id <7

union

select score, id, name

from student

where id <4

union

select score, id, name

from student

where id> 8

order by id desc

order by clauses must be written in the last result set in, and the rules will change the operation of its order after the sort order. For the Union, Union All, Intersect, Minus are valid.

Note:

1, Union field names can be different, but the same result set data types to merge;

2, if the field were the result of different sets Union, then this field Order by clause would be invalid.

================================================== ================================================== =============

Intersect and Minus Union operations and is consistent with the sum up here:

Union, the two results sets and set operations, not including duplicates, at the same time the default rules of the sort;

Union All, the two results sets and set operations, including duplicate rows, do not sort;

Intersect, the result set to the intersection of the two operations, not including duplicates, at the same time the default rules of the sort;

Minus, of the difference between the two result sets to operate, not including duplicates, at the same time ordering the default rules.

Can be specified in the final result set to change the Sort Order by clause
标签: result set, oracle, sql statements, sql statement, syntax, few days, point of view, final result, boston, internet sales, sql union, union directive, san diego, sales turnover, date sales
分类: Database
时间: 2010-06-28

相关文章

  1. Problems between Oracle result set (product and cross)

    Oracle uses the MINUS and UNION MINUS command is used in SQL statements on the two. It is the first to find th ...
  2. oracle result set operation

    Intersect and Minus and Union operations are basically the same, here with the summary: Union, the two result ...
  3. Solve the problem of the oracle user locked

    / * For the user to unlock * / alter user 'username' account unlock; / * Reset a new password for the user * / ...
  4. [Change] After the eclips prompted by installation oracle jvm version of the problem is too low

    Eclips prompted by installation oracle jvm version is too low after the issue today is installed on your machi ...
  5. oracle ora-01033: oracle initialization or shutdown in progress problem solving

    Oracle yesterday to play this problem encountered ora-01033: oracle initialization or shutdown in progress, th ...
  6. AIX oracle jdbc problem record loss

    Transfer from the Internet, extract AIX Version V5.3 ORACLE version 10.2.0.1 JDBC Driver version 10.2.0.1, 10. ...
  7. Oracle concepts (Oracle 10.2)

    1, Oracle introduced This chapter provides an overview for the Oracle database server, contains the following ...
  8. cakephp of oracle <excerpt>

    Environment: XP System + apache2.2 + php5.3 (5.2) + oracle10g (9i, two libraries are) + oracle client 10g Prob ...
  9. Oracle GoldenGate real-time using data integration

    ] [Switched http://blog.chinaunix.net/u1/53677/showart_2314585.html Oracle GoldenGate real-time using data int ...
  10. oracle (COALESCE function)

    Database application software will often multiple, related entities, the information stored in the same table. ...
  11. Use Oracle GoldenGate real-time data integration

    [Transfer] http://blog.chinaunix.net/u1/53677/showart_2314585.html Use Oracle GoldenGate real-time data integr ...
  12. windows7 the process of installing Oracle

    Windows 7 install Oracle 10g on success (I am the author from the Guangdong Ocean University, School of Inform ...
  13. PLSQL Developer 8 to connect Oracle 11g X64 Edition

    PLSQL Developer 8 to connect Oracle 11g X64 Edition Before I do Flex, Oracle is no longer just science ... ... ...
  14. ORACLE EBS basic set of guidelines for us

    ORACLE EBS basic set of guidelines for us First need to clarify is that this series document assumes the reade ...
  15. oracle chain reaction

    1, client server access to Oracle PL / SQL Developer Times ora-01033: oracle initializationg or shutdown in pr ...
  16. Modify the name of the Oracle of calculation.

    The emergence of a small problem: start the Oracle 10g listener service, listener automatically shut down. Int ...
  17. About Oracle10.2.0 start the process leading to disruption of two monitor problem

    Environment: AIX6.1 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi Tomat 5.0.28 Problem desc ...
  18. Linux install and uninstall oracle

    Install Oracle AS5 + ORACLE10G installation notes -------------------------------------------------- --------- ...
  19. Oracle10g JDBC ojdbc14 DATE type problem hibernate query every minute

    The general database, DATE field represents the date only, not including the date information, but Oracle Data ...