sql--row Value expression (row Expressions)

Overview

Recently approached a new concept-the row value expression, also called the row value constructor. This is a very powerful SQL feature, and usually the SQL expression that we manipulate can only be compared against a single field in a row, whereas a row value expression may be manipulated against multiple fields in a row.

It is defined in the sql:92 standard, but so far, not all databases are supported, and each database is not supported in a consistent degree.

This lists several databases that support row-valued expressions:

• DB2
• Hsqldb
• Mysql
• Oracle
• Postgres

Query Statement Example

SELECT *  from T WHERE inch (     SELECT from u        )

The above example is able to select the same values in the T-table and U-table fields. only one expression was used, and two fields were compared . The traditional notation is:

SELECT *  from  WHEREEXISTS(        SELECT* from u         WHERE=and=  u.u2   )

Traditional writing can also achieve the same function, but the amount of code is more, the traditional way of writing with two expressions, compare two fields, and connect with and .

UPDATE Statement Example (Mysql5.5 not supported)

UPDATE T SET =      ('A'B')WHERE  =ten

The above example is in table T, which modifies the T1 in the row of the field id=10 to ' A ' and T2 to ' B '. The traditional notation is:

UPDATESET='A'='B'  WHERE=ten

comparison of row-valued expressions

In addition to the in usage of the query statement example, the row-value expression can also use the =,! =,>,<,>=,<= and other symbols for field comparison.

The following is true:

(9,9,9)>(1,1,1)('Bob','Bar')>('Alan','Alert')(3,2,1)!=(4,3,2)

The above is relatively simple, one can see true and false, because one by one of the corresponding elements of the comparison are true ~

However, when comparing row value expressions, it is not necessary for each element to be true, the more important the left element is, as long as the element on the left is true, the entire expression is true

The following is also true:

 (9 , 1 , >  ( 1 , 9 , 9  ) (  '  zommer   ' ,  ,  '  zelda  ) 

If the first bit is the same, the second bit is compared, and so on, the following is true:

(1,1,2)>(1,1,1)(1,2,1)>(1,1,1)(1,2,1)>(1,1,2)

For an equal expression, all elements must be true, and the expression is true:

(123= (123)

For unequal expressions, only one element is true, and the expression is true:

(121!= (111)

The following is a conventional conversion with a generic row-valued expression that can be more clearly explained by how the comparison of row-valued expressions works:

row-valued expression: (A, B, C)>(X, Y, Z) corresponds to the traditional transformation: (A>X)OR((A=X and(B>Y))OR((A=X and(B=Y and(C>Z)) Row-Valued Expression: (A, B, C)>=(X, Y, Z) corresponds to the traditional transformation: (A>X)OR((A=X and(B>Y))OR((A=X and(B=Y and(C>Z))OR((A=X and(B=Y and(C=Z))

It is visible from above that a row value expression is more convenient in some cases than a traditional expression.

Resources:

Predicates with Row Value Expressions

Use the Index, Luke

sql--row Value expression (row Expressions)