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
Variable Declaration - SQL SQL's support for variables is very similar to Tutorial D's, except that the syntax for creating persistent  variables-base tables-is quite differen

%ISOPEN The %ISOPEN yields TRUE if its cursor or cursor variable is open; or else, the %ISOPEN yields FALSE. In the illustration, you use the %ISOPEN to select an action:

1- You can check attribute names from each table in DBF11 by running for example:  desc dbf11.Member;  desc dbf11.Agent;  desc dbf11.Producer; Because some attribute names in

Example of Check Constraints Example: Workaround for when subqueries not permitted in CHECK constraints CREATE FUNCTION NO_MORE_THAN_20000_ENROLMENTS ( ) RETURNS BOOLEAN

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

Majority of Differences among 9i, 10G, 11G :- These are some combine feature which has differences among others. Automatic Workload Repository (AWR) Drop database' s

Data Types in SQL - Timestamp TIMESTAMP for values representing points in time on a specified uniform scale. DATE is used for timestamps on a scale of one day, such as DATE '2

Extension and AND in SQL The theory book gives the following simple example of relational extension in Tutorial D: EXTEND IS_CALLED ADD ( FirstLetter ( Name ) AS Initial )

Query: SELECT * FROM EMPLOYEE1; Select 5 columns and all rows from one table Query: SELECT C_ID, COMPANY, BUILDING, DEPARTMENT, BRANCH FROM CONTRACT;

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi