Skip to content

set.md

Latest commit

 

History

History
25 lines (25 loc) · 2.84 KB

set.md

File metadata and controls

25 lines (25 loc) · 2.84 KB

UNION

  • The Oracle UNION operator is used to combine the result sets of 2 or more Oracle SELECT statements. It removes duplicate rows between the various SELECT statements.
  • Each SELECT statement within the UNION operator must have the same number of fields in the result sets with similar data types.
  • By default, the output order cannot be relied assured without using ORDER BY.
  • UNION chỉ cần same về số lượng 2 bên
    • In this Oracle UNION operator, since the column names are different between the two SELECT statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the ORDER BY 2.
    • The ORDER BY clause can only appear once, and it must be at the very end of the entire query, not in individual SELECT blocks.
    • The number, but not names, of columns must be identical for all SELECT statements in the query.
    • The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.
    • The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query.
  • SELECT và UNION
    • Khi nói về select của 2 vế union
      • Yes, the column names can differ, but:
        • The data types must be compatible
        • The names in the final result are taken from the first SELECT

UNION ALL

  • The Oracle UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It returns all rows from the query and it does not remove duplicate rows between the various SELECT statements.
  • Each SELECT statement within the Oracle UNION ALL operator must have the same number of fields in the result sets with similar data types.

INTERSECT:

  • The Oracle INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
  • Before its introduction to the language you had to mimic the INTERSECT behaviour using and INNER JOIN.
  • NOTE: These are equivalent to a point. AS NULL aren’t values, there fore NULL = NULL is always false. Given this, the INNER JOIN will fail to match on joins; however, the INTERSECT operator does match NULLS.

MINUS

  • The Oracle MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The MINUS operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.
  • https://www.techonthenet.com/oracle/minus.php