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
DBMS_OUTPUT: The Package DBMS_OUTPUT enables you to display output from the PL/SQL subprograms and blocks, that makes it easier to test and debug them. The procedure put_ line

INSERT Command in SQL Loosely speaking, INSERT takes the rows of a given source table and adds them to the specified target table, retaining all the existing rows in the targ

SQLs counterpart of the key words: The text from the opening parenthesis to the end of the fourth line specifies the declared type of the table, meaning that every table ever

Packaging Cursors   You can split a cursor specification from its body for placement in a package. In that way, you can change the cursor body without changing the cursor spec

Implicit Cursor is declared and used by the oracle environment internally. while the explicit cursor is declared and used by the external user. more over implicitly cursors are no

Character Types The Character types allow you to store alphanumeric data, represent words and text, and manipulate the character strings. CHAR You use the CHAR dataty

Assignment Source Not a Literal - Variable Syntax: SET SN = SID (SUBSTRING (SN.C FROM 1 FOR 1)||'5');

Consider the schema for FreeChecking Bank, that we designed given below. Translate the given ER schema into SQL CREATE TABLE statements (indicating primary key, unique and foreign

Declaring Exceptions The Exceptions can be declared only in the declarative part of the PL/SQL subprogram, block, or package. By introducing its name, you can declare an excep

Data Types in SQL - Interval, Boolean INTERVAL for values denoting, not intervals (!) but durations in time, such as 5 years, 3 days, 2 minutes, and so on. BOOLEAN, con