Union without corresponding - sql, PL-SQL Programming

UNION without CORRESPONDING - SQL

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

Example: UNION without CORRESPONDING

SELECT StudentId

FROM IS_CALLED

WHERE Name = 'Devinder'

UNION DISTINCT

SELECT StudentId

FROM IS_ENROLLED_ON

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
Comparison Operators The Comparison operators can compare one expression to another. The outcome is always true, false, or null. Usually, you use a comparison operators in condi

Row Counterparts of Table Operators SQL does not have counterparts tuple rename, tuple projection, tuple extension, tuple join and tuple compose. To obtain the same effects as

Recursion The Recursion is a powerful method for simplify the design of the algorithms. Principally, the recursion means the self-reference. In the recursive mathematical serie

Use of Table Comparisons - SQL Table comparisons where it is noted that although table expressions cannot be compared, we have TABLE (t) to convert a table expression t into

BIOCHE M ICA L ORIGIN OF LIFE - It is generally agreed by astronomers, geologists and biologists that the earth is approximately 4500-5000 million years old. It is an

ROWID The ROWID returns the rowid (binary address) of a row in the database table. You can use the variables of the type UROWID to store rowids in a readable format. In the il

Question: a) Given the following relation: Location(loc_id, bldg_code, room, capacity) The underlined field is a primary key. (i) Write a PL/SQL program using the impl

Collections:   The collection is an ordered group of elements, all of similar type (for illustration, the grades for a class of students). Each element has an exclusive subsc

INSERT Command in SQL Loosely speaking, INSERT takes the rows of a given source table and adds them to the specified target table, retaining all the existing rows in the targ

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