Relationships

My initial thought was to break down an SQL statement into pieces. Seems like a fairly obvious idea so I am inclined to go with it. Let us assume these basic pieces: Statement Type, SELECT expression, FROM table references, JOIN clauses, INSERT/REPLACE/UPDATE values, WHERE clauses, and lastly and ORDER/GROUP/HAVING type clause. I think, and I stress “think”, that I can break these into smaller pieces. Each can be comprised of a collection of “relationships”, or even more simply as a collection of name/value pairs. Let us see how far I can take this theory.

The following table demonstrates the idea of name/value pair making up a SELECT expression. Each name/value pair represents a “Statement Relationship”. These example relationships should represent all possible combinations of column references (column), simple expressions (expr) (like 1+1), core MySQL functions (func), and full SELECT queries (subselect) that can used within the select expression. A sub-select statement used within the SELECT expression must be a scalar value, returning only a singular value. Each has the option of using an alias for easier parsing of the resulting record set.

SELECT EXPRESSION
Type name operator value alias EXAMPLE
SELECT value [opt] SELECT 23;
value * value [opt] SELECT 25 = 25;
column [opt] SELECT col1 FROM tbltest;
column * value [opt] SELECT col1 = 25 FROM tbltest;
column * column [opt] SELECT col1 = col2 FROM tbltest;
expr [opt] SELECT (1+1);
expr * value [opt] SELECT (1+1) < 5;
expr * expr [opt] SELECT (1+1) < (2+1);
func [opt] SELECT REPLACE(‘test’, ‘e’, ‘i’);
func * value [opt] SELECT REPLACE(‘test’, ‘e’, ‘i’) = ’tist';
func * expr [opt] SELECT SIN(20) < 1;
func * func [opt] SELECT SIN(20) = COS(20);
subselect [opt] SELECT (SELECT col1 FROM tbltest);
subselect * value [opt] SELECT (SELECT col1 FROM tbltest) = ‘value';
subselect * expr [opt] SELECT (SELECT col1 FROM tbltest) > (1+1);
subselect * subselect [opt] SELECT (SELECT col1 FROM tbltest) = (SELECT col2 FROM tbltest);

One Comment

  1. […] I have five sqlRelationship classes that can be passed to the SQL statement class. (not discussed yet) The SELECT expressions, […]

Leave a Reply