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
WHILE-LOOP The WHILE-LOOP statement relates a condition with the series of statements enclosed by the keywords LOOP and END LOOP, as shown: WHILE condition LOOP sequence_of_sta

Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save

DELETE Command - SQL Loosely speaking, DELETE removes some existing rows from its target table. Suppose the university decides that course C3 is to be withdrawn. Example shows

What Are Cursor Variables  ? The Cursor variables are like C or Pascal pointers that hold the memory location (address) of some item rather of the item itself. Therefore, decl

Creating a SQL file 1. Open a new file in Notepad++ and save it to the location c:\mysql\bin, with the name lab8script.sql (the file extension should be .sql ). Add a MySQL co

Interesting properties of CROSS JOIN - SQL Compare these with the "interesting properties of JOIN", CROSS JOIN is associative but not commutative. Unlike JOIN and NATURAL JOI

what is the use of declare keyword

Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns

THEO R Y OF SPECIAL CREATION - Life originated on the earth due to natural events by the super natural power. The biblical story of creation of world within six days was p

NULL Statement The NULL statement clearly specifies in action; it does nothing other than to pass control to the next statement. It can, though, improve the readability. In a