mysql - Filtering Condition in n-Table Joins

Question

We seem to have a need for a multi-table JOIN operation and I am referring to some notes from an RDBMS class that I took several years ago. In this class the instructor graphically depicted the structure of a generic N-table JOIN query.

The figure seems to conform to examples of multi-table JOINs that I have seen but I have a question. Does the WHERE clause, for providing filtering, necessarily have to be the last clause in the query? Intuitively it appears that we can impose filtering conditions before a following JOIN clause, in order to properly scope the data, before we input it to the next JOIN operation.

question from:https://stackoverflow.com/questions/65929308/filtering-condition-in-n-table-joins

Answer 1

SQL queries consist of a sequence of clauses. The diagram you have is rather misleading. Common clauses -- and the order they must appear for a valid query -- are:

• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY

Note that JOIN is not a clause. It is an operator, and an operator that specifically appears only in the FROM clause.

So, the answer to your question is that WHERE clauses immediately follow the FROM clause. The only "sort-of" exception is when a "window" clause is included and that is syntactically between the FROM and the WHERE.

Next, multiple table joins are often quite efficient and there is no reason whatsoever to discourage their use. Support for joins, in fact, is one of the key design features that databases are designed around.

And finally. What actually gets executed is not the string that you create. A query, in fact, describes the result set you want. It does not describe the processing. SQL is a descriptive language, not a procedural language.

The SQL engine has two steps to convert your query string to an executable form (typically a directed acyclic graph). One is to compile the query, and the second is to optimize the query. So, where filtering actually occurs . . . that depends on what the optimizer decides. And where it occurs has little relationship to what you think of when you think of SQL queries (DAGs don't generally have nodes called "select" or "join").