Table comparison - sql, PL-SQL Programming

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.

Posted Date: 1/18/2013 7:02:48 AM | Location : United States







Related Discussions:- Table comparison - sql, Assignment Help, Ask Question on Table comparison - sql, Get Answer, Expert's Help, Table comparison - sql Discussions

Write discussion on Table comparison - sql
Your posts are moderated
Related Questions
PPD , CPA, Filesharing Site Project Description: This is very easy I need a PPD , CPA, Filesharing Site. [PPD] stands for (pay per download) example hotsharecash [Files

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

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illu

Literature review

Delimiters A delimiter is a simple or compound symbol which has a special meaning to PL/SQL. For example, you use delimiters to symbolize an arithmetic operation like additio

Develop Data Business Intelligence Project Project Description: We are linking our Microsoft SQL Database to GoodData Business Intelligence. We are seeking somebody who has e

UTL_FILE: The Package UTL_FILE permits your PL/SQL programs to read & write operating system (OS) text files. It gives a restricted version of the standard OS stream file I/O,

Mixed Notation The fourth procedure call shows that you can mix the positional and named notation. In this situation, the first parameter uses the positional notation, & the s

Project Description: I want to write some SQL statements. The things I need are between pages 5-7. The only problem is that i want it till tomorrow. Skills required is SQL

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