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
Parameter Modes   To define the behavior of formal parameters you use the parameter modes. The 3 parameter modes, IN, OUT, & IN OUT, can be used with any subprogram. Though, a

Create a view named CustomerAddresses that shows the shipping and billing addresses for each customer in the MyGuitarShop database. This view should return these columns from the

Example of Using Aggregation on Nested Tables Example: How many students sat each exam WITH C_ER AS (SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM

Name Resolution In potentially uncertain SQL statements, the names of the database columns take precedence over the names of the local variables and formal parameters. For e.g.

Pass the nulls to a dynamic SQL: Passing Nulls: Assume that you want to pass the nulls to a dynamic SQL statement. For illustration, you may write the EXECUTE IMMEDIATE

Define basic operators of relational algebra with an example each

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

Information Hiding   With the information hiding, you see only the details that are significant at a given level of algorithm and data structure design. The Information hiding

Updating by replacement Syntax: UPDATE ENROLMENT SET Name = 'Ann' WHERE StudentId = SID ('S1'); Note the use of SET, as already noted in connection with direct a

I need SQL to infopath data connection Project Description: Want data retrieval connection from SQL to SharePoint infopath Skills required are Sharepoint, SQL