4034

Logical Processing Order or SQL Standard in WHERE clause

Question:

I was asked couple days ago about logical processing order of the SELECT statement and more specifically about aliases and where clause and I'm not sure about one issue. If we have a query like:

SELECT name AS first_name FROM people WHERE first_name = 'Alan';

The reason why usage of aliases in WHERE clause will generate error is really logical processing order of the SELECT statement, or rather syntax parsing issue, or maybe is the rule from SQL standard?

Answer1:

It is the rule from the SQL standard (which is rather complicated because it goes into lots of details that users of SQL probably don't think about).

There are two principles behind the rule. The first is that the standard does not impose an ordering of operations, except when logically necessary (a having clause, for instance, has to logically processed after a group by). This is the basis of the notion at SQL is a <em>descriptive</em> language, where the results are described. Any particular database engine can determine its own execution paths.

The second principle is to avoid ambiguity. This is where scoping rules come in, that define what a SQL compiler knows when.

Consider the following statement:

select a as b, b as a, a + 1 as d -----------------------^ from t

The question is: which a does a+1 refer to, the column a in the table or the column b (which is aliased as a) in the select. According to the standard this is unambiguous. Column aliases are not known in the select clause where they are defined.

This extends to the where clause as well, which is evaluated in the same scope. Consider the same example:

select a as b, b as a, a + 1 as d from t where a > 100

Which a does the where condition refer to? The standard is unambiguous. The where clause does not understand column aliases in the select. This is because the select is (logically) evaluated after the where. So, when you say:

select row_number() over (order by a) as seqnum from t where a > 100

The value returned starts with the first a <em>after</em> 100. The enumeration does not happen first, with filtered rows getting sequence numbers that are filtered out.

Recommend

  • Hiding Python Code from non-programmers
  • how do i unzip stream while reading it in c# [closed]
  • Restrict classes that can implement interface in Java
  • Embed multiple jpeg images into EXCEL programmatically?
  • C++ how to get substring after get position of its index
  • Can I call custom javascript from an R jupyter notebook
  • SSRS 2008 - Sorting within a group
  • CSS - how to trim text output?
  • Deserialize Dictionary
  • Bootstrap collapse within a foreach loop
  • How to select table rows/complete table?
  • Zeromq with python hangs if connecting to invalid socket
  • Does the Azure table storage API cache results?
  • F#: In which memory area is the continuation stored: stack or heap?
  • Get used tables from sql query [duplicate]
  • ZipList with Scalaz
  • Can you pass an array from javascript to asp.net mvc controller action without using a form?
  • Where these are stored?
  • Alamofire and Reachability.swift not working on xCode8-beta5
  • Regex to match a string not followed by anything
  • Question about instantiating object
  • pyodbc doesn't report sql server error
  • Apache RewriteRule redirection with url encoded
  • Parse a date string in a specific locale (not timezone!)
  • How to use carriage return with multiple line?
  • Email verification using google app script and google forms
  • Control modification in presentation layer
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Master page gives error
  • PHP - How to update data to MySQL when click a radio button
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • Counter field in MS Access, how to generate?
  • Cassandra Data Model
  • Adding custom controls to a full screen movie
  • Confusion with PayPal's monthly billing cycle
  • Matrix multiplication with MKL
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • CSS Applying specific rule for a specific monitor resolution with only CSS is posible?
  • Checking variable from a different class in C#
  • How to get NHibernate ISession to cache entity not retrieved by primary key