SQL collection (notes)

SQL collection (notes)

SQL is about the set

Oracle is a relational data, where data tables are a set of regular data. Therefore, when using SQL statements, it will save a lot of efficiency if you can follow the set idea, it also makes the statement clearer.

1. Four set Operators

1) union returns non-repeated data rows;

2) union all returns all data rows (including duplicate rows );

3) minus returns the data rows that exist in the first input query and do not exist in other queries;

4) intersect returns the data rows that exist in all data queries.

Note: Except for union all, all set operations require sorting results/unique value operations, which means more efficiency is required. Therefore, if you do not care about repetition, try to use union all.

2. null)

The relational model only has two results: True, false, and unknown. The SQL language allows three results: True, false, and unknown.

Oracle considers the unknown results to be correct, so you must pay attention to this in many search conditions. Otherwise, the search results may differ greatly from the expected ones.

By default, the order by statement puts the null value in the final output. If you want to put it in the front side, you need to add nulls first after the order by statement.

Null is automatically removed from the aggregate function, that is, it is not involved in the operation of the aggregate function.