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

Execute immediate statement - syntax, EXECUTE IMMEDIATE Statement   Th... EXECUTE IMMEDIATE Statement   The EXECUTE IMMEDIATE statement prepare (parses) and instantly executes a dynamic SQL statement or an anonymous PL/SQL block. Syntax:

Join and and in sql, JOIN and AND in SQL In this Section is all about ... JOIN and AND in SQL In this Section is all about one operator, JOIN. SQL's closest counterpart, NATURAL JOIN, has already been covered. Here we look at several other "join" op

Keyword and parameter description - forall statement, Keyword &Parameter De... Keyword &Parameter Description: index_name: This is an undeclared identifier which can be referenced only within the FORALL statement and only as the collection subscript

Cartesian product, Using a Join on 2 tables, select all columns and all row... Using a Join on 2 tables, select all columns and all rows from the tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 JOIN CONTRACT ON EMPLOYEE

Create a procedure to tax calculation task, Complete the following steps to... Complete the following steps to create a procedure to calculate the tax on an order. The BB_TAX table contains the states that require taxes to be submitted for Internet sales. If

Introduction to SQl and DQL, which operation is used if we are interested i... which operation is used if we are interested in only certain columns of a table?

Insert statement - syntax, INSERT Statement The INSERT statement adds f... INSERT Statement The INSERT statement adds fresh rows of data to the specified database table or view. Syntax:

Calculate the total shopper spending, Many of the reports generated from th... Many of the reports generated from the system calculate the total dollars in purchases for a shopper. Complete the following steps to create a function named TOT_PURCH_SF that acce

Parameter and keyword description - insert statement, Parameter and Keyword... Parameter and Keyword Description:   table_reference: This identifies a table or view which should be available when you execute the INSERT statement, and for that you sho

Conditionals - sql, Conditionals - SQL At first sight SQL does not app... Conditionals - SQL At first sight SQL does not appear to have a single operator for expressing logical implication. In this respect it would be in common with most programming