What do we call the equivalent of CSS “Specificity” in SQL

identifierterminology

In CSS, the more HTML elements you specify, the more precedence the selector gets. This is known as "specificity." For example,

div p span {...}

Is more specific than, and thus would override:

span {...}

In SQL, one can start a query like:

SELECT database.table.columnOne, database.table.columnTwo FROM ...

The same query can be expressed, less specifically, as:

SELECT columnOne, columnTwo FROM ...

Is there a proper term in SQL for being more specific in queries, or is it just called "specificity" like in CSS?

Best Answer

In CSS, the more HTML elements you specify, the more precedence the selector gets. This is known as "specificity." For example,

That's not true. CSS specificity is very complex because it's trying to apply conflicting rules rather simply disambiguate. While I understand what you're talking about SQL suffers no such problem with identifiers as queries are either executed with certainty or an error is thrown.

Is there a proper term in SQL for being more specific in queries, or is it just called "specificity" like in CSS?

The intent here is different, "specifity" doesn't exist.

  • In CSS, the goal is to establish a ruleset to apply conflicting rules.
  • In SQL, the goal with the syntax you've mentioned is to merely disambiguate.

In the context given we would say <catalog>.<schema>.<table>.<column> is "fully qualified", as in it leaves no further room for ambiguity. This definition comes from ISO9075-1.,

fully qualified (of a name of some SQL object) With all optional components specified explicitly

NOTE 3 — A fully qualified name does not necessarily identify an object uniquely. For example, although a fully qualified specific name, consisting of a catalog name, a schema name and a specific name, uniquely identifies a routine, a fully qualified routine name doesn't necessarily do so.

NOTE 3 is referring to routines being typed, catalog.schema.myRoutine(text,text) can be different from catalog.schema.myRoutine(int,int).

This is massively different from "specificity" which implies cascading mutability -- if we say "show all animals" and "hide all dogs" and "show collies" what should happen?

<div class="animals">
  <div class="dogs">
    <div class="collies" id="Lassie">

.animals { display: block }
.dogs { display: none }
.collies { display: block }

In CSS, you have to know if multiple rules can apply to the same identifier, which one has precedence in the event they conflict. In SQL, the rule-set is much simpler. If there is any ambiguity, things will throw a fatal error. All databases that I can think of forgo any attempt at guesswork. Preventing that fatal error from being thrown requires qualification of your "identifiers".

For instance, the following creates a result set that returns two x (some databases would likely throw an error here),

SELECT *
FROM ( VALUES (1) ) AS t(x)
CROSS JOIN ( VALUES (2) ) AS g(x);
 x | x 
---+---
 1 | 2
(1 row)

but the second you say you want x with SELECT x rather than SELECT * all databases that I know of throw an error,

ERROR:  column reference "x" is ambiguous
LINE 1: SELECT x FROM ( VALUES (1) ) AS t(x) CROSS JOIN ( VALUES (2)...

Logically you may ask what the equivalent of SQL "fully qualified" identifiers in CSS. That would be a simple identifier (id attribute selectors). The spec requires identifiers be absolute, thus there is no ambiguity when you specify

#Lassie { color: blue }

It's infinitely (fully-) (globally-) "specific" (qualified), and far more specific than

.animals.dogs.collies { color: white }

Actually there is a small rabbit hole I'm excluding and that's XHTML namespaces and Foreign Elements. which do in fact provide for multiple #Lassies (that is to say identifiers aren't always globally unique)

Related Question