What do we call the equivalent of CSS "Specificity" in SQL?

Question

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?

• SS_DBA: This first Select statement contains "qualifying" objects. So, I'm thinking you are looking for "qualifying".
• GTS Joe: No, that's not it. It isn't limited to the SELECT statement. You can be more specific in the WHERE clause, too, e.g.: WHERE database.table.columnOne = "Something"
• mustaccio: I doubt there is a corresponding term, because the semantics are completely different. While in CSS a less specific term will apply to all elements, in SQL omitting a column qualifier will not return all matching columns. A missing qualifier simply causes a default value, whatever that is, to be used.
• Andriy M: Regardless of whether your column is in the select list or in the WHERE clause, it can still be (fully) qualified or not qualified. (A WHERE clause is part of a SELECT statement, by the way.) The need to qualify columns may arise in an UPDATE statement as well: MySQL supports updates with joins, so you sometimes need to fully qualify columns in the SET clause. Still, I'd probably agree with @mustaccio in that it's hard to come up with something semantically similar to your CSS example. When there's a possibility for ambiguity in SQL, then either scope precedence kicks in or an error occurs.

Answer 1

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)