A relational Database Model allows database users to analyze data thoroughly. To

Question

A relational Database Model allows database users to analyze data thoroughly. To accomplish this, advanced commands such as UNION and INTERSECT may be used.

Explanation / Answer

You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS. All set operators have equal precedence. If a SQL statement contains multiple set operators, then Oracle Database evaluates them from the left to right unless parentheses explicitly specify another order. The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same datatype group (such as numeric or character). If component queries select character data, then the datatype of the return values are determined as follows: If both queries select values of datatype CHAR of equal length, then the returned values have datatype CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value. If either or both of the queries select values of datatype VARCHAR2, then the returned values have datatype VARCHAR2. If component queries select numeric data, then the datatype of the return values is determined by numeric precedence: If any query selects values of type BINARY_DOUBLE, then the returned values have datatype BINARY_DOUBLE. If no query selects values of type BINARY_DOUBLE but any query selects values of type BINARY_FLOAT, then the returned values have datatype BINARY_FLOAT. If all queries select values of type NUMBER, then the returned values have datatype NUMBER. In queries using set operators, Oracle does not perform implicit conversion across datatype groups. Therefore, if the corresponding expressions of component queries resolve to both character data and numeric data, Oracle returns an error. The following statement combines the results of two queries with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_CHAR function) when columns do not exist in one or the other table: SELECT location_id, department_name "Department", TO_CHAR(NULL) "Warehouse" FROM departments UNION SELECT location_id, TO_CHAR(NULL) "Department", warehouse_name FROM warehouses; LOCATION_ID Department Warehouse ----------- --------------------- -------------------------- 1400 IT 1400 Southlake, Texas 1500 Shipping 1500 San Francisco 1600 New Jersey 1700 Accounting 1700 Administration 1700 Benefits 1700 Construction The SQL INTERSECT query allows you to return the results of 2 or more "select" queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results. Each SQL statement within the SQL INTERSECT query must have the same number of fields in the result sets with similar data types. The syntax for the SQL INTERSECT query is: select field1, field2, . field_n from tables INTERSECT select field1, field2, . field_n from tables; SQL INTERSECT Query - Single field example The following is an example of an SQL INTERSECT query that has one field with the same data type: select supplier_id from suppliers INTERSECT select supplier_id from orders; In this SQL INTERSECT query example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set. SQL INTERSECT Query - Using ORDER BY Clause example The following is an SQL INTERSECT query that uses an SQL ORDER BY clause: select supplier_id, supplier_name from suppliers where supplier_id > 2000 INTERSECT select company_id, company_name from companies where company_id > 1000 ORDER BY 2; Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the SQL 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 supplier_name / company_name fields are in position #2 in the result set.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.