English terminology MySQL queries

Question

I need help with some "terminology" about query parts. But not only query PARTS, any "query-related figures of speech" also will be very helpful, I'm strongly interested in them. And not only about SELECTs (as in example below), INSERTs, UPDATEs and other queries.

For example:

SELECT t1.f1, t1.f2, t2.f1 AS f1a, (t2.f2 * 10) AS f2a
FROM talbe1 AS t1
INNER JOIN table2 AS t2 ON (t1.f4 <> t2.f4)
WHERE t1.f3 > t2.f3
LIMIT 100, 9999

I know that:

1) t1, t2 - "table aliases", no mistake here?

2) f1a - "field alias", f2a - idk... "expression alias" maybe? Is there a difference between them? Any collective naming?

3) 100 - "offset"

I'm not sure how to call:

1) all between SELECT and FROM: t1.f1, t1.f2, t2.f1 AS f1a, (t2.f2 * 10) AS f2a

2) t1.f3 > t2.f3 "where clause"? "condition"? which is better? other variants?

3) (t1.f4 <> t2.f4) "join condition"?

Also interested if there is more than 1 join, can I call them somehow by "order" or "depth"? And if I can do it by "depth" then how it will be with the RIGHT JOIN?

Any other interesting "naming of things" with your examples will be very helpful. Like SELF-JOIN, may be some other of joins which have special call, anything interesting you can remember.

Answer 1

SQL is very formal and it is really well structured. Any query (regardless SELECT or INSERT etc.) consists of сlauses. For SELECT-queries I suggest to start reading it from the FROM-clause. In others start from DELETE/INSERT/UPDATEclauses (first line). These clauses show which datasets are being manipulated. In these clauses you can see a table or a list or tables or JOIN-expressions (in FROM) or subqueries in parenthesis. List of tables also means join but will be explained later. E.g.

... FROM tab1 t1  
... FROM tab1 t1, tab2 t2
... FROM tab1 JOIN tab2 ON ( ... )
... FROM (select * from tab3 ...) t
DELETE tab4 ...  /* doesn't delete the table, but its records*/
INSERT INTO tab5 ...
UPDATE tab6 t6 ...
UPDATE (select * from tab3 ... ) ...

These are central clauses which shows what tables are to be processed.

Within the clause each table or subquery could be referenced using an alias. Aliases will replace the table name in all other parts of the query. In above examples t1, t2, t6 and t are aliases. Most DBMSs doesn't require the word AS before alias, and many doesn't allow it. If an alias is present you have to use this alias only.

All queries (except INSERT) could contain WHERE-clauses. This clause limits the number of affected rows. It contains a condition. It is possible to give a single simple condition of one comparison (e.g. last_name="Ellison") or complex conditions which additionally uses logical operators: AND, OR, NOT. Different parts of the condition could be grouped by parenthesises.

WHERE t1.last_name="Ellison" AND t1.first_name="Clark"
WHERE usr.id=profile.usr_id AND
     (usr.state="active" OR usr.role="contributor")

Other clauses are specific to queries. SELECT-clauses contains a list of expressions and gives the projection — produces new set of columns for processed recordset. Any expression could contain column aliases added using the optional word AS.

ORDER BY-clauses controls the order of result records (or result set). It is applicable to SELECT-query only. ORDER BY must be the last clause.

GROUP BY-clauses and HAVING-clauses are used for grouping certain records into one and filtering the result after grouping. Grouping replaces multiple records (which has same value in one or several fields) into one record. These clauses are applicable to SELECT-query.

A SELECT query could be (where square brackets indicate optional parts):

SELECT expressions
FROM tables or join expressions
[WHERE simple or complex condition]
[GROUP BY expressions
 [HAVING simple or complex condition]]
[ORDER BY expressions]

Others are simpler:

DELETE table
[WHERE simple or complex condition]

UPDATE table
SET field=expression, field=expression, ...
[WHERE simple or complex condition]

INSERT INTO table[(fields...)]
VALUES (expressions...)

or another form

INSERT INTO table[(fields...)]
SELECT...   /* normal select-query */

---

Regarding JOIN

JOIN is an implementation of a relational algebra operation. When joining some record of one table it concatenates this with a record of another table and makes a wider record. INNER JOIN (exact join) applies this action to only pairs of records which matches the joining condition.

... users INNER JOIN roles ON (roles.id=user.role_id) ...

LEFT OUTER JOIN additionally adds unmatched records from the first table into the result set. RIGHT JOIN vice-versa.

-- two identical joins using different join operators
... users LEFT OUTER JOIN roles ON (roles.id=user.role_id)...
... roles RIGHT OUTER JOIN users ON (roles.id=user.role_id)... 

FULL OUTER JOIN does both, it gives the result of an INNER JOIN plus unmatched records from left table plus unmatched records from right table.

Words INNER and OUTER are optional. You can omit them and nothing changes.

Answer 2

You seem to have understood most of the terms quite good, I would say, and I do believe you would be understood with most of your wording. Looking towards the formal documentation also seems like a good idea.

Here are my take on your questions, first lets respond to those you seem to know:

1. "Table aliases" is good
2. "field alias" or "expression alias" are good. A common answer could be "column alias", as these statements defines the columns of your result
3. "offset" (or possibly "row offset") is good

The ones you are not so sure about:

1. Between the SELECT and FROM I would either use "select expression" or "column expression" or "column definitions"
2. I tend to use "where expressions"
3. "join condition" or "join expression" are equally clear to me

When referring to the different joins, I would either differentiate them by the only "inner join" or "left join", or if you have multiple joins then use something like the "join on table1". "Self join" would of course refer to a join referring back to itself using a different alias, but could become confusing if you have lots of other joins as well.

In general most people would be understanding, and disregard minor errors in wording, as long as your intentions and the solution is understood.

Answer 3

Q: 1) all between SELECT and FROM: t1.f1, t1.f2, t2.f1 AS f1a, (t2.f2 * 10) AS f2a

A: I refer to that as the "select list". That's the list of expressions that are being returned. Each expression in the list is a column in the resultset. (An expression can be as simple as a column name, or can be more involved, the return from a function or comparison operation.

Q: 2) t1.f3 > t2.f3 "where clause"? "condition"? which is better? other variants?

A: The general term for these conditions that have to be satisfied in order for a row to be returned is a "predicate". The predicates are the conditions in the WHERE clause or ON clauses that have to be satisfied in order for a row to be returned.

Q: 3) (t1.f4 <> t2.f4) "join condition"?

A: Again, this is a predicate. This just happens to appear in the ON clause rather than the WHERE clause. The term "join condition" is also appropriate. But in terms of how that database parses and executes the statement, it's just another predicate.

Normative practice is for a "join condition" to be included in the ON clause, and other conditions in the WHERE clause.

Answer 4

You may be interested in what the official MySQL syntax has to say about the matter. In my opinion you can never be overly formal when trying to describe something as mathematical as programming language terminology.

1.

t1.f1 is called a select_expr, fudge-pronounced in English as "select expression". Note that:

Each select_expr indicates a column that you want to retrieve.

So another less formal way of calling it in English would probably be "column."

2.

The syntax seems to distinguish between the clause and the condition, where the clause includes the keyword WHERE itself, but the condition is just the plain-old SQL expression inside.

The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected.

and also

In the WHERE expression, you can use any of the functions and operators that MySQL supports, except for aggregate (summary) functions. See Section 9.5, “Expression Syntax”

3.

From a different syntax documentation page

MySQL supports the following JOIN syntaxes for the table_references part of SELECT statements

So the whole part about which tables to SELECT from is called the "table references". From here we can dig into the syntax a bit for the production which matches your above example:

join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

This means the part you're referring to is contained within the "join condition." However as with the WHERE clause above, the join condition also contains the ON keyword. The actual expression (t1.f4 <> t2.f4) is, again, a plain-old conditional_expr or "conditional expression", just like the one from the WHERE clause above.

Some more details from this doc page yield a valuable insight:

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.

So you could informally distinguish between them in a useful, meaningful way by calling your WHERE condition a "row-restriction expression" and the JOIN condition a "join condition expression."

Answer 5

1) 2) I would use aliases that show what they refer to. For example, select ... from my_first_table mft inner join user_rights ur where ... . But if you don't have better options, "t1, t2, ..." are OK.

3) yes

---

1) select clause, or fields

2) where clause

3) "join condition" is right