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

Ask an Expert

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

Write your message here..

Related Questions

Tables within a table - sql, Tables within a Table - SQL Figure here ... Tables within a Table - SQL Figure here is an exact copy of the one in the theory book and as before it is just an alternative way of representing some of the information con

Example of unwrap operator - sql, Example of UNWRAP Operator - SQL Exa... Example of UNWRAP Operator - SQL Example here shows how unwrapping can be done in longhand in SQL. Example: Unwrapping in SQL Letting CONTACT_INFO_WRAPPED denote the res

Understanding varrays, Understanding Varrays The Items of type VARRAY ar... Understanding Varrays The Items of type VARRAY are termed as the varrays. They permit you to relate a single identifier with the whole collection. This relationship lets you man

Creating and destroying base tables, Creating and Destroying Base Tables: ... Creating and Destroying Base Tables: Example shows an SQL command to create the base table counterpart of the ENROLMENT variable Example  Creating a base table. CREATE T

Grouping and ungrouping in sql, Grouping and Ungrouping in SQL Example... Grouping and Ungrouping in SQL Example specifying EXAM_MARK in place of COURSE in the main FROM clause. Example: Obtaining C_ER2 from EXAM_MARK SELECT CourseId, CAST

Manipulating collections-nested table examples, Manipulating Collections ... 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

Short-circuit evaluation-pl/sql expressions , Short-Circuit Evaluation ... Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Need fullcalendar modifications, Project Description: I am looking to ch... Project Description: I am looking to change FullCalendar to add/delete sql server data when events are removed or dropped from Calendar. Events should only be included by dra

Creating a sql file, Creating a SQL file 1. Open a new file in Notepad... Creating a SQL file 1. Open a new file in Notepad++ and save it to the location c:\mysql\bin, with the name lab8script.sql (the file extension should be .sql ). Add a MySQL co

Name resolution - pl/sql, Name Resolution   During the compilation, th... Name Resolution   During the compilation, the PL/SQL compiler relates identifiers like the name of a variable with an address or memory location, actual value, or datatype. Th