Union without corresponding - sql, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Union without corresponding - sql

Using count, Using COUNT The COUNT returns the number of elements that...

Using COUNT The COUNT returns the number of elements that a collection presently contains. For instance, when a varray projects contains 15 elements, then the following IF con

Cursors in pl/sql, Cursors The Oracle uses work areas to execute the SQ...

Cursors The Oracle uses work areas to execute the SQL statements and to store process information. A PL/SQL construct known as the cursor. Let's you assume name a work area and

Defining records, Defining and Declaring Records To create records, yo...

Defining and Declaring Records To create records, you have to define a RECORD type, and then declare records of that type. You may also define RECORD types in the declarative

Comparison operators- pl/sql, Comparison Operators The Comparison operat...

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

Open-for statement, OPEN-FOR Statement The OPEN-FOR statements execute ...

OPEN-FOR Statement The OPEN-FOR statements execute the multi-row query related with a cursor variable. It also allocates the resources used by the Oracle to process the query a

Positional and named notation, Positional and Named Notation You can wr...

Positional and Named Notation You can write the actual parameters when calling a subprogram, using either positional or named notation. That is, you can point to the relationsh

Implicit rollbacks, Implicit Rollbacks Before execute the INSERT, UPDA...

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

Parameter aliasing, Parameter Aliasing   To optimize the subprogram ca...

Parameter Aliasing   To optimize the subprogram call, the PL/SQL compiler can decide between the two techniques of the parameter passing. With the by-value techniques, the v

Tables within a table - sql, Tables within a Table - SQL Figure here ...

Tables within a Table - SQL Figure here is an exact copy of the one in the theory book and as before it is just an alternative way of representing some of the information con

Data types, Datatypes Every constant and variable has a datatype that s...

Datatypes Every constant and variable has a datatype that specifies the storage format, constraints, and the valid range of values. The PL/SQL gives a variety of predefined dat

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd