Expanding the Relationship Theory

Lets continue to fit the SQL relationship theory into basic SQL statement syntax. You may recall, a statement “relationship”, as defined by myself, is a name/value pair used to create the building blocks of a complete SQL statement. The next step is to analyze the name/value pairs that make up the SET/VALUES expressions used within INSERT/REPLACE/UPDATE statements. Because these pairs are used to populate a database, the “name” portion of each name/value pair needs to be a column reference. An operator need not be defined for the only allowed value is “equal to” (=). The “value” portion can be as diverse as the SELECT statement breakdown. Basically anything that returns a scalar value can be used within the “value” portion of the relationship. No aliases are allowed.

SET/VALUES EXPRESSION
Type name operator value alias EXAMPLE
VALUES/SET column expr INSERT INTO tbltest (col) VALUES (1+1);
column value INSERT INTO tbltest (col) VALUES (‘value’);
column func INSERT INTO tbltest (col) VALUES (REPLACE(‘test’, ‘e’, ‘i’));
column column INSERT INTO tbltest (col1, col2) VALUES (1, col1*2);
column subselect INSERT INTO tbltest (col1, col2) VALUES (1, (SELECT col3 FROM tbltest2 WHERE id = 1));
column(1) subselect INSERT INTO tbltest (col1, col2) (SELECT col3, col4 FROM tbltest2 WHERE id = 2);
column(2)
column(3)
column(n)

Moving on to the WHERE clause we can easily expand the relationship theory. Each element of the WHERE clause can be viewed as a statement relationship, or a name/value pair. A WHERE relationship, or condition as termed my the MySQL documentation, is an expression that evaluates to true or false. You can use a subselect as both the name and the value portion of each WHERE condition, however, because these subselects are required to return a scalar value, the expression will always evaluate to the same result. Therefore, I have excluded the “subselect” = “subselect” relationship option.

WHERE CONDITION
Type name operator value alias EXAMPLE
WHERE expr SELECT * FROM tbltest WHERE (1=1);
expr * value SELECT * FROM tbltest WHERE ((2-1)=1);
expr * expr SELECT * FROM tbltest WHERE ((10-5)>(1+2));
func * value SELECT * FROM tbltest WHERE REPLACE(‘test’, ‘e’, ‘i’) = ’tist';
func * expr SELECT * FROM tbltest WHERE REPLACE(‘test’, ‘e’, ‘i’) = ’tist';
func * func SELECT * FROM tbltest WHERE SIN(20) >= ABS(-.05);
column * value SELECT * FROM tbltest WHERE col1 = 10;
column * expr SELECT * FROM tbltest WHERE col1 = (10-9);
column * func SELECT * FROM tbltest WHERE col1 = CEIL(12.145);
column * column SELECT * FROM tbltest WHERE col1 = col2;
column * subselect SELECT * FROM tbltest WHERE col1 IN (SELECT id FROM tbltest2);
columns = subselect SELECT * FROM tbltest WHERE (col1,col2) = (SELECT col3,col4 FROM tbltest2);

The last condition satisfies the ROW() subselect option. In this case a set of column references must equal the column values returned from the subselect. This will be satisfied by allowing the user to pass an array of column references as the “name” and a subselect as the “value”.

Each relationship listed above can be used within any JOIN statement ON clause. For each ON condition is basically a condition to determine “where” to link the tables. The only exception may be the multiple columns equal to a subselect. However, I think we can expand the relationship theory to any JOIN clause conditional statements.

Leave a Reply