Table comparison - sql, PL-SQL Programming

Assignment Help:

Table Comparison - SQL

The following definitions for relation comparisons:

Let r1 and r2 be relations having the same heading. Then:

r1 ⊆ r2 is true if every tuple of r1 is also a tuple of r2, otherwise false.

r1 ⊇ r2 is equivalent to r2 ⊆ r1

r1 = r2 is equivalent to r1 ⊆ r2 AND r2 ⊆ r1

The question arises as to whether SQL tables can be similarly compared. SQL does not have direct counterparts of ⊆ and ⊇. It does of course have =, but table expressions cannot be used as comparands. However, as we have seen in Examples et seq., the operator TABLE has been available since SQL: 2003 to derive from a given table expression a value of a multiset type whose element type is a row type. In other words, (SELECT * FROM t1) = (SELECT * FROM t2) is illegal but we can obtain the required effect by writing TABLE (SELECT * FROM t1) = TABLE (SELECT * FROM t2). So, to compare two tables, we have to use an operator named TABLE to "convert" them from tables into multisets of rows!

To test for every row of t1 being also a row of t2 we could write, for example, NOT EXISTS (SELECT * FROM t1 EXCEPT SELECT * FROM t2). In fact, SQL's NOT EXISTS is an exact counterpart of Tutorial D's IS_EMPTY operator. However, note carefully that the case where every row in t1 appears in t2 and every row of t2 appears in t1 does not guarantee that t1 and t2 are the same table. Row r might appear twice in t1 but only once in t2, for example.


Related Discussions:- Table comparison - sql

Pl/sql conditional control: if statements, Pl/sql Conditional Control: IF s...

Pl/sql Conditional Control: IF statements Frequently, it is necessary to take the alternative actions depending on the circumstances. The IF statement execute a series of statem

Example of unwrap operator - sql, Example of UNWRAP Operator - SQL Exa...

Example of UNWRAP Operator - SQL Example here shows how unwrapping can be done in longhand in SQL. Example: Unwrapping in SQL Letting CONTACT_INFO_WRAPPED denote the res

I need simple hosted sql database, Project Description: I want a small r...

Project Description: I want a small relational database to be built. I want the database to have the subsequent information tables: Employee Information Document storage

Set operators - sql operators, Set Operators The Set operators combine...

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

Name resolution-naming conventions, Name Resolution In potentially uncer...

Name Resolution In potentially uncertain SQL statements, the names of the database columns take precedence over the names of the local variables and formal parameters. For e.g.

Insert statement - syntax, INSERT Statement The INSERT statement adds f...

INSERT Statement The INSERT statement adds fresh rows of data to the specified database table or view. Syntax:

Use tsql function sql server 2012, I want someone to write a TSQL function ...

I want someone to write a TSQL function that returns the name of the ODBC DSN. I will use the queries below, to get information about the connection, but none of these return th

Wrapping and unwrapping in sql, Wrapping and unwrapping in SQL Operato...

Wrapping and unwrapping in SQL Operators WRAP and UNWRAP in connection with attributes whose declared types are tuple types. Example shows how extension and projection can be

Map and order methods, Map and Order Methods: The values of the scalar...

Map and Order Methods: The values of the scalar datatype like CHAR or REAL have a predefined order that allows them to be compared. While, the instances of an object type has

Collections in pl sql, Collections:   The collection is an ordered gr...

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

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