Union all - sql, PL-SQL Programming

UNION ALL - SQL

Further varieties of UNION arise when we replace the key word DISTINCT by ALL in any of the foregoing examples, as in Example. ALL specifies that if row r appears n times in one operand and m times in the other, then it appears n+m times in the result-i.e., no elimination of duplicate rows takes place.

Example: UNION ALL

SELECT StudentId

FROM IS_CALLED

WHERE Name = 'Devinder'

UNION ALL

SELECT StudentId

FROM IS_ENROLLED_ON

WHERE CourseId = 'C1'

Clearly, UNION ALL represents another departure from relational theory. However, it is commonly used when the operands can be guaranteed to be disjoint because in such cases omission of ALL would incur the possibly significant overhead of the duplicate elimination process with no effect on the final result. Some authorities have argued that there really ought to be yet another variety of UNION, such that if row r appears n times in one operand and m times in the other, with m, n, then it appears m times in the result. Relational devotees might smile at this observation but refrain from comment.

Posted Date: 1/18/2013 5:37:00 AM | Location : United States







Related Discussions:- Union all - sql, Assignment Help, Ask Question on Union all - sql, Get Answer, Expert's Help, Union all - sql Discussions

Write discussion on Union all - sql
Your posts are moderated
Related Questions
MILLER-UREY' S EXPERIMENTAL PROCEDURES - They recreated the probable conditions on the primitive earth in the laboratory. An atmosphere containing hydrogen, ammonia, me

TYPES OF EVOLUTION - Sequential evolution                  :                    Minor changes in the gene pool of a population from one generation to the next, with the resul

Example of Check Constraints Example: Workaround for when subqueries not permitted in CHECK constraints CREATE FUNCTION NO_MORE_THAN_20000_ENROLMENTS ( ) RETURNS BOOLEAN

First Step at defining type SID in SQL CREATE TYPE SID AS ( C VARCHAR(5) ) ; Explanation: TYPE SID announces that a type named SID is being defined to the syst

What are 3 good practices of modeling and/or implementing data warehouses?

Raise_application_error -  procedure of package DBMS_STANDARD , allows to issue an user_defined error messages by stored sub-program or database trigger.

If two relations R and S are joined, then the non matching tuples of both R and S are ignored in __________________.

Use the NOCOPY Compiler Hint By default, the OUT and IN OUT parameters are passed by the value i.e. the value of an IN OUT actual parameter is copied into the corresponding fo

Manipulating Collections Within PL/SQL, the collections add procedural power and flexibility. The biggest benefit is that your program can compute subscripts to process the spec

Using COUNT The COUNT returns the number of elements that a collection presently contains. For instance, when a varray projects contains 15 elements, then the following IF con