Union without corresponding - sql, PL-SQL Programming


The use of UNION without CORRESPONDING. Example is merely by omitting CORRESPONDING, but only because the operands have identical SELECT clauses.


SELECT StudentId


WHERE Name = 'Devinder'


SELECT StudentId


WHERE CourseId = 'C1'

When CORRESPONDING is omitted, names are not used at all in the pairing of columns. Instead, SQL's definition, in yet another departure from relational database theory, depends on an ordering of the columns: the first column of the first operand is paired with the first column of the second operand, the second with the second, and so on. As with CORRESPONDING, columns thus paired do not have to be of the same type. Furthermore, the two operand tables must have the same number of columns, so that there is no unpaired column in either operand, also as in relational union.

Although the operand columns in still have the same name, StudentId, that is not a requirement in this variety of UNION. For example, SELECT StudentId AS X could be the SELECT clause of the second operand. However, if corresponding columns do not have the same name, then the corresponding column in the result is effectively anonymous (the standard defines it to have an unpredictable system- generated name). Actually, some implementations use the column names of the first operand here, thus destroying the normal commutativity of UNION. The user of an implementation that strictly follows the standard would perhaps be well advised always to make sure the corresponding columns have the same name anyway, to avoid the unpredictability of system-generated names and to improve portability from one implementation to another.

Posted Date: 1/18/2013 5:29:27 AM | Location : United States

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

Write discussion on Union without corresponding - sql
Your posts are moderated
Related Questions
Definition of CROSS JOIN - SQL Let s = t1 CROSS JOIN t2, where t1 and t2 are table expressions optionally accompanied by range variables. Then: Note: Here T denotes Table

Parameter and Keyword Description: collection_name: This keyword identifies the index-by table, nested table, or varray formerly declared within the present scope. cu

In Packages The Forward declarations also group logically related subprograms in the package. The subprogram specifications go in the package specification, & the subprogram b

THEO R Y OF ETERNITY OF LIFE (PRAYER - 1880) - The theory of eternity of life, also called the steady-state theory , states that life has ever been in existence as at presen

Updating Objects: To change the attributes of objects in an object table, you can use the UPDATE statement, as the illustration below shows: BEGIN UPDATE persons p SET p

Using the Collection Methods The collection methods below help to generalize the code and make collections easier to use and also make your applications easier to maintain:

What are the rates for help in writing PL/SQL procedures and functions?

Example of Null operator - NiNo Rule If we wanted to make HIGHER_OF adhere to "NULL in, NULL out"-let's call it the NiNo rule-we would have to write something like what is sho

Effects of NULL in Aggregate Operator - SQL Let aggop(x) be an invocation of some aggregate operator aggop in SQL, where x is an expression (usually an open expression) to be

Scoping Within the similar scope, all the declared identifiers should be unique. So, even if their datatypes differ, the variables and parameters cannot share the similar name.