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
Closest Approximation to Relational Union - SQL Actually, just as SQL has several varieties of JOIN, it also has several varieties of UNION, none of which is equivalent to th

Controlling Cursor Variables You use 3 statements to control the cursor variable: OPEN-FOR, FETCH, & CLOSE. At First, you OPEN a cursor variable FOR a multi-row query. Then, y

Raise_application_error -  procedure of package DBMS_STANDARD , allows to issue an user_defined error messages by stored sub-program or database trigger.

ROWID and UROWID Internally, every database table has a ROWID pseudo column that stores binary values known as rowids. Each rowid shows the storage address of a row. A physical

Varrays versus Nested Tables The Nested tables are differing from varrays in the following ways: 1)  Varrays have a maximum size, while nested tables do not. 2)  Varrays are

Write a cursor to open an employee database and fetch the employee record whose age is greater than 45

IS NULL Operator The IS NULL operator returns the Boolean value TRUE whenever its operand is null or FALSE if it is not null. The comparisons including the nulls always yield NU

Number Types The Number types permit you to store the numeric data (real numbers, integers, and floating-point numbers), show quantities, and do computations. BINARY_INTEG

Declaring Records Whenever you define a RECORD type, you may declare records of that type, as the illustration shows: DECLARE TYPE StockItem IS RECORD ( item_no INTEG

Using PRIOR and NEXT The PRIOR(n) returns the index number that precede index n in a collection. The NEXT(n) returns the index number which succeed the index n. If n has no pr