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

Begin parameter description in pl sql, BEGIN Parameter Description in pl sq...

BEGIN Parameter Description in pl sql: BEGIN: This keyword signals the beginning of the executable section of a PL/SQL block, that contains executable statements. The execut

program for employees -ado.net framework , Challenge 1 You are require...

Challenge 1 You are required to do the project and write a test plan for it. Demo 4 is a check writer program for employees. In it, the user enters all information about the

Primary key, PRIMARY KEY: PRIMARY KEY  indicates that the table is sub...

PRIMARY KEY: PRIMARY KEY  indicates that the table is subject to a key constraint, in this case declaring that no two rows in the table assigned to ENROLMENT can ever have the

Example of table literal - sql, Example of Table Literal - SQL Exampl...

Example of Table Literal - SQL Example: A Table Literal (correct version) VALUES ('S1', 'C1', 'Anne'), ('S1', 'C2', 'Anne'), ('S2', 'C1', 'Boris'), ('S3', 'C3'

Creating Views, Create a view named CustomerAddresses that shows the shippi...

Create a view named CustomerAddresses that shows the shipping and billing addresses for each customer in the MyGuitarShop database. This view should return these columns from the

Why use cursor variables, Why Use Cursor Variables ? Primarily, you use...

Why Use Cursor Variables ? Primarily, you use the cursor variables to pass the query result sets between the PL/SQL stored subprograms and different clients. Neither PL/SQL nor

Authorisations - privileges, Authorisations - Privileges As relational...

Authorisations - Privileges As relational theory is silent on the issue of authorisation, it offers nothing with which SQL's vast edifice in support of what it calls privilege

Write a pl-sql program using the implicit cursor, Question: a) Given th...

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

%found - implicit cursor attributes, %FOUND Until the SQL data manipul...

%FOUND Until the SQL data manipulation statement is executed, the %FOUND yields NULL. Afterward, the %FOUND yields TRUE, when an INSERT, UPDATE, or DELETE statement affected o

Insert command in sql, INSERT Command in SQL Loosely speaking, INSERT...

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

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