Database Management SystemsQuery Processing

Parsing and Validation

LevelIntermediate

Duration75 mins

TopicQuery Processing

3 / 5

Name Resolution

The Art of Knowing What Names Mean

SQL queries are full of names—table names, column names, function names, aliases, schema names, and more. But a name by itself is just a string of characters. The database must resolve each name to determine exactly what object it refers to in the current context.

This might seem straightforward, but consider:

SELECT name FROM employees e JOIN departments d ON e.dept_id = d.id

Which table does name come from? If both tables have a name column, it's ambiguous. If only one has it, the resolution is automatic but still requires checking. What about e—is that a schema, a table, or an alias? The answers depend on scope, context, and resolution rules that are far more sophisticated than they first appear.

Name resolution is the process of mapping each name in a query to a specific database object or query-defined entity. This process must handle qualification, aliasing, subquery scopes, lateral references, and numerous SQL-specific rules.

What You Will Learn

By the end of this page, you will understand the complete name resolution process: how databases use namespaces, scope chains, and resolution rules to convert names into unambiguous object references. You'll learn why some queries produce ambiguity errors, how aliases work, and how name resolution differs across SQL contexts.

Understanding Namespaces in SQL

SQL organizes objects into namespaces—logical domains where names must be unique. Understanding namespaces is fundamental to understanding name resolution.

The SQL Namespace Hierarchy:

Most relational databases organize names into a hierarchical structure:

Catalog (Database)
└── Schema (Namespace)
    ├── Tables
    ├── Views
    ├── Functions
    ├── Procedures
    ├── Sequences
    ├── Types
    └── Other Objects

Within a schema, each object type typically has its own namespace:

Table employees and function employees() can coexist (different namespaces)
But two tables named employees cannot exist in the same schema (same namespace)

SQL Object Namespaces
Namespace	Contains	Uniqueness Scope	Example Conflict
Relations	Tables, Views, Materialized Views	Per schema	Can't have table and view with same name
Functions	Functions, Procedures	Per schema + signature	Same name OK with different parameters
Types	User-defined types, Domains	Per schema	Type and table can share names
Columns	Table/View columns	Per table	Same column name OK in different tables
Aliases	Table/Column aliases	Per query scope	Can reuse aliases in different queries
Constraints	PKs, FKs, Checks, Unique	Per schema (usually)	Constraint names must be unique

namespace-examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Namespace independence examples
 
-- Same name in different object types (often allowed)
CREATE TABLE orders (id INT, total DECIMAL);
CREATE TYPE orders AS (id INT, status TEXT);  -- May work in some DBs
 
-- Same name in different schemas (always allowed)
CREATE SCHEMA sales;
CREATE SCHEMA warehouse;
CREATE TABLE sales.orders (id INT, customer_id INT);
CREATE TABLE warehouse.orders (id INT, product_id INT);
 
-- Same column name in different tables (always allowed)
CREATE TABLE customers (id INT, name VARCHAR(100));
CREATE TABLE products (id INT, name VARCHAR(100));
 
-- Alias reuse in query (scope-dependent)
SELECT e.name AS manager_name FROM employees e
WHERE e.id IN (
    SELECT e.manager_id FROM employees e  -- Different scope, same alias OK
);

Database Variations

Namespace rules vary by database. PostgreSQL separates tables from functions, allowing both to share names. Oracle treats top-level schemas differently than PostgreSQL. MySQL's databases are essentially schemas. Always consult your specific database's documentation for precise namespace behavior.

Qualified vs. Unqualified Names

Names in SQL can be specified with varying levels of qualification. More qualification means less ambiguity but more verbosity.

Levels of Qualification:

qualification-levels.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Full qualification for table reference (3-part name)
-- catalog.schema.table
SELECT * FROM production_db.hr.employees;
 
-- Schema-qualified (2-part name)
-- schema.table
SELECT * FROM hr.employees;
 
-- Unqualified (1-part name)
-- Uses search_path/current schema
SELECT * FROM employees;
 
-- Full qualification for column reference
-- schema.table.column (3-part after table resolution)
SELECT hr.employees.employee_name FROM hr.employees;
 
-- Alias-qualified column reference
SELECT e.employee_name FROM employees AS e;
 
-- Unqualified column reference
SELECT employee_name FROM employees;

The Schema Search Path:

When names are unqualified, databases use a search path (or equivalent concept) to find objects:

-- PostgreSQL search path
SHOW search_path;      -- Result: "$user", public

-- Set search path explicitly
SET search_path TO myschema, public;

Resolution proceeds left-to-right through the search path until a matching object is found. The $user placeholder expands to the current username, allowing user-specific schemas.

Resolution Algorithm for Unqualified Names:

Parse the name to extract components
If fully qualified, look up directly in specified location
If partially qualified, combine with search path
If unqualified, search each schema in path order
Return first match found, or error if no match

Best Practices for Name Qualification

•Production code — Always schema-qualify table references to avoid path-dependent behavior
•Multi-table queries — Alias all tables and qualify all column references with aliases
•Dynamic SQL — Use fully qualified names to avoid security risks from path manipulation
•Migrations — Specify exact schemas to ensure operations target intended objects
•Interactive queries — Unqualified names are acceptable for convenience

Table Alias Resolution

Table aliases are names introduced within a query to refer to tables, views, or subqueries. Understanding alias resolution is crucial because aliases fundamentally change how column references are resolved.

Alias Introduction:

Aliases are introduced in the FROM clause:

alias-introduction.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Explicit alias with AS keyword (recommended for clarity)
SELECT e.name FROM employees AS e;
 
-- Implicit alias without AS (valid but less clear)
SELECT e.name FROM employees e;
 
-- Subquery alias (AS is often required)
SELECT sub.total 
FROM (SELECT SUM(amount) AS total FROM orders) AS sub;
 
-- Lateral subquery with alias
SELECT d.name, emp.count
FROM departments d
CROSS JOIN LATERAL (
    SELECT COUNT(*) AS count 
    FROM employees WHERE department_id = d.id
) AS emp;
 
-- Table function alias
SELECT t.* 
FROM generate_series(1, 10) AS t(num);

Alias Visibility and Shadowing:

When a table alias is defined, it typically shadows the original table name within that query scope:

-- After aliasing, use the alias, not the original name
SELECT employees.name    -- Often errors or unexpected in some DBs
FROM employees AS e;

-- Correct: use the alias
SELECT e.name
FROM employees AS e;

However, this behavior varies by database:

PostgreSQL: Both original name and alias work
SQL Server: Alias required after introduction; original name may not work
MySQL: Both typically work

Self-Joins Require Aliases:

Aliases become mandatory when joining a table to itself:

self-join-aliases.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Find employees and their managers (self-join)
-- Without aliases: IMPOSSIBLE - can't distinguish instances
SELECT name, manager_name
FROM employees, employees;  -- Error: ambiguous!
 
-- With aliases: Clear and unambiguous  
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
 
-- The aliases 'e' and 'm' create two logical instances of employees
-- Column references must be qualified to specify which instance
 
-- Hierarchical query example
WITH RECURSIVE org_chart AS (
    -- Anchor: top-level employees (no manager)
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: employees reporting to those already in chart
    SELECT e.employee_id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN org_chart o ON e.manager_id = o.employee_id
)
SELECT * FROM org_chart;

Alias Naming Conventions

Use meaningful short aliases: 'e' for employees, 'd' for departments, 'o' for orders. For self-joins, use descriptive aliases: 'emp' and 'mgr', or 'parent' and 'child'. Long queries benefit from slightly longer aliases for clarity.

Column Resolution Rules and Precedence

Column name resolution is particularly complex because columns can come from multiple sources with overlapping names. The resolver must apply specific rules to determine which column a reference means.

Column Reference Sources:

Table columns — Defined in table DDL
Computed columns — Expressions given aliases in SELECT
Subquery columns — Columns from derived tables
Set operations — Column names from first query in UNION
Lateral references — Columns from tables earlier in FROM clause
Outer queries — Columns from enclosing queries (correlated subqueries)

Resolution Precedence:

column-resolution-precedence.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Precedence Example 1: Local over Outer Scope
SELECT e.name,
    (SELECT name FROM departments WHERE id = e.dept_id) AS dept_name
FROM employees e;
-- In subquery: 'name' resolves to departments.name (local scope)
-- 'e.dept_id' resolves to outer employees table (correlated reference)
 
-- Precedence Example 2: Explicit Qualifier Wins
SELECT employees.name  -- Explicit: employees.name
FROM employees
JOIN departments ON employees.dept_id = departments.id;
-- 'employees.name' is unambiguous due to table qualifier
 
-- Precedence Example 3: Ambiguity Error
SELECT name  -- Which name?
FROM employees
JOIN departments ON employees.dept_id = departments.id;
-- ERROR: column reference "name" is ambiguous
 
-- Precedence Example 4: SELECT Aliases in ORDER BY
SELECT salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary;  -- Alias from SELECT is visible in ORDER BY
 
-- But NOT in WHERE (processed before SELECT)
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;  -- ERROR: column does not exist

SQL Clause Processing Order:

Column visibility depends on SQL's logical processing order:

1. FROM     → Table aliases become visible
2. JOIN     → Join tables and their aliases visible
3. WHERE    → All columns from FROM/JOIN visible
4. GROUP BY → Column references, can define grouping aliases (some DBs)
5. HAVING   → Aggregates and GROUP BY expressions visible
6. SELECT   → Aliases defined here (visible only to later clauses)
7. ORDER BY → All columns plus SELECT aliases visible
8. LIMIT    → No new visibility

This explains why SELECT aliases work in ORDER BY but not WHERE.

Column Visibility by Clause
Clause	Table Columns	SELECT Aliases	Aggregates	Outer Scope
FROM	Being defined	No	No	Yes (lateral)
WHERE	Yes	No	No	Yes (correlated)
GROUP BY	Yes	Sometimes*	No	Yes
HAVING	Grouped only	Sometimes*	Yes	Yes
SELECT	Yes	Defining	Yes	Yes
ORDER BY	Yes	Yes	Yes	Outer no longer exists

Database Variations

SELECT alias visibility in GROUP BY/HAVING varies by database. MySQL allows it liberally. PostgreSQL requires exact matching. SQL Server is strict about standard behavior. Always test aliased GROUP BY expressions in your target database.

Subquery and CTE Scoping

Queries often contain nested scopes through subqueries and CTEs. Name resolution must correctly handle these scope boundaries, determining when inner scopes can reference outer scopes and vice versa.

Scope Types in SQL:

SQL Scope Hierarchy

•Main Query Scope — The outermost SELECT/INSERT/UPDATE/DELETE
•CTE Scope — WITH clause definitions; visible to main query and later CTEs
•Subquery Scope — Nested queries in SELECT, FROM, WHERE, etc.
•Lateral Scope — Special FROM clause scoping for lateral joins
•Correlated Scope — Subqueries referencing outer query columns

scope-examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- CTE Scope: CTEs visible to later CTEs and main query
WITH 
    dept_summary AS (
        SELECT department_id, COUNT(*) as emp_count
        FROM employees
        GROUP BY department_id
    ),
    high_count_depts AS (
        SELECT department_id 
        FROM dept_summary  -- References earlier CTE
        WHERE emp_count > 10
    )
SELECT d.name, ds.emp_count
FROM departments d
JOIN dept_summary ds ON d.id = ds.department_id  -- Main query uses CTE
JOIN high_count_depts hcd ON d.id = hcd.department_id;
 
-- Correlated Subquery: Inner references outer
SELECT e.name, e.salary,
    (SELECT AVG(salary) FROM employees 
     WHERE department_id = e.department_id) AS dept_avg  -- 'e' from outer
FROM employees e;
 
-- Lateral Subquery: Explicit outer reference permission
SELECT d.name, emp_stats.*
FROM departments d
CROSS JOIN LATERAL (
    SELECT COUNT(*) AS count, AVG(salary) AS avg_salary
    FROM employees
    WHERE department_id = d.id  -- 'd' visible due to LATERAL
) AS emp_stats;
 
-- Without LATERAL, this would error:
-- JOIN (SELECT ... WHERE department_id = d.id) -- Error: 'd' not visible

Scope Resolution Order:

When resolving a name, the analyzer searches scopes from innermost to outermost:

1. Current query's FROM clause (aliases and columns)
2. Enclosing query's scope (if correlated subquery)
3. Further enclosing queries (deeply nested correlation)
4. CTE definitions (if in WITH clause scope)

Scope Shadowing:

An inner scope can shadow (hide) names from outer scopes:

scope-shadowing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Example: Shadowing in nested subqueries
SELECT e.name AS outer_name,
    (SELECT e.name  -- This 'e' is from inner FROM, not outer!
     FROM employees e  -- New 'e' alias shadows outer 'e'
     WHERE e.manager_id = outer_e.employee_id
    ) AS manager_name
FROM employees outer_e;  -- Use distinct alias to avoid confusion
 
-- Best practice: Use unique aliases across all nesting levels
SELECT emp.name,
    (SELECT mgr.name 
     FROM employees mgr 
     WHERE mgr.employee_id = emp.manager_id
    ) AS manager_name
FROM employees emp;

Shadowing Hazards

Alias shadowing can create subtle bugs where an inner query references a different object than intended. Always use unique aliases across nested scopes. If you reuse 'e' for employees in both outer and inner queries, you're asking for confusion.

Function and Operator Resolution

Beyond tables and columns, name resolution also applies to functions and operators. This is more complex because functions can be overloaded—multiple functions with the same name but different parameter signatures.

Function Overload Resolution:

Consider the function SUBSTR:

SUBSTR(string, start)           -- 2-argument version
SUBSTR(string, start, length)   -- 3-argument version

The database must choose which overload to call based on argument count and types.

Resolution Algorithm:

Find all functions with matching name in search path
Filter to those accepting correct argument count
Filter to those accepting compatible argument types
If multiple remain, rank by type conversion cost
Select lowest-cost match or error if ambiguous

function-resolution.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- PostgreSQL function overloading example
 
-- Multiple ROUND functions exist:
-- round(numeric) -> numeric
-- round(numeric, integer) -> numeric  
-- round(double precision) -> double precision
 
SELECT ROUND(3.14159);        -- Uses round(numeric) → 3
SELECT ROUND(3.14159, 2);     -- Uses round(numeric, int) → 3.14
SELECT ROUND(3.14159::float); -- Uses round(double precision) → 3
 
-- Type coercion in function resolution
SELECT CONCAT('Value: ', 42);     
-- 42 is integer, CONCAT expects text
-- Database implicitly casts 42 to text
 
-- Operator resolution works similarly
-- The '+' operator is overloaded:
SELECT 1 + 2;           -- integer + integer → integer
SELECT 1.5 + 2;         -- numeric + integer → numeric  
SELECT '2020-01-01'::date + 7;  -- date + integer → date
 
-- Explicit casting resolves ambiguity
SELECT SUBSTRING('hello'::varchar, 1, 3);  -- Forces specific overload

Type Precedence and Coercion:

When exact type matches aren't found, databases use type coercion to find acceptable matches. Each database defines a type hierarchy and conversion costs:

Integer → BigInt → Numeric → Float → String
(lower priority ——————————————→ higher priority for coercion)

Ambiguity errors occur when multiple overloads have equal coercion cost.

Schema-Qualified Function Calls:

schema-qualified-functions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- User-defined function in specific schema
CREATE FUNCTION analytics.compute_score(numeric) RETURNS numeric AS $$
    SELECT $1 * 100;
$$ LANGUAGE SQL;
 
-- Qualified call
SELECT analytics.compute_score(0.85);
 
-- Unqualified call requires function's schema in search_path
SET search_path TO analytics, public;
SELECT compute_score(0.85);
 
-- Built-in function override (be careful!)
CREATE FUNCTION public.upper(text) RETURNS text AS $$
    SELECT 'OVERRIDDEN: ' || pg_catalog.upper($1);
$$ LANGUAGE SQL;
 
-- Now public.upper shadows pg_catalog.upper
SELECT upper('hello');  -- Result depends on search_path order!

Case Sensitivity and Quoted Identifiers

Case sensitivity in name resolution is a notorious source of confusion and portability issues. Different databases handle case differently, and quoting identifiers changes behavior.

Standard SQL Behavior:

The SQL standard says:

Unquoted identifiers are folded (converted) to uppercase
Quoted identifiers preserve exact case and are case-sensitive
Keywords are case-insensitive

Actual Database Behavior:

Case Sensitivity by Database
Database	Unquoted Identifiers	Quoted Identifiers	Default Folding
PostgreSQL	Case-insensitive	Case-sensitive	Lowercase
MySQL (Linux)	Case-sensitive*	Case-sensitive	As-written
MySQL (Windows)	Case-insensitive	Case-insensitive	Lowercase
Oracle	Case-insensitive	Case-sensitive	Uppercase
SQL Server	Collation-dependent	Collation-dependent	As-written
SQLite	Case-insensitive	Case-sensitive	As-written

case-sensitivity-examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- PostgreSQL Examples
 
-- These are all the same table (folded to lowercase)
CREATE TABLE Employees (ID INT);
SELECT * FROM EMPLOYEES;
SELECT * FROM employees;
SELECT * FROM EmPlOyEeS;
 
-- But this is different (quoted = exact case preserved)
CREATE TABLE "Employees" (ID INT);
SELECT * FROM "Employees";  -- Works
SELECT * FROM employees;    -- Different table!
 
-- Quoting can create headaches
CREATE TABLE "MyTable" ("MyColumn" INT);
-- Now you're stuck quoting forever:
SELECT "MyColumn" FROM "MyTable";
-- This fails:
SELECT MyColumn FROM MyTable;  -- Looks for mytable.mycolumn
 
-- Oracle behaves oppositely (folds to UPPERCASE)
-- Unquoted: CREATE TABLE employees → stored as EMPLOYEES
-- These work: SELECT * FROM EMPLOYEES/employees/EmPlOyEeS
-- Quoted "employees" would be DIFFERENT from EMPLOYEES

Portability Best Practice

For maximum portability: use lowercase unquoted identifiers everywhere. Never use reserved words as identifiers. Avoid special characters. This works consistently across PostgreSQL, MySQL, SQLite, and most other databases. If you must use mixed case, accept that you'll always quote.

Reserved Words and Quoting:

Quoting also allows using reserved words as identifiers:

-- These are reserved words used as identifiers
CREATE TABLE "select" ("from" INT, "where" TEXT);  -- Quoted works
CREATE TABLE order (id INT);  -- ERROR: ORDER is reserved
CREATE TABLE "order" (id INT);  -- Quoted works

-- Some databases allow unquoted reserved words in column position
SELECT t.from, t.order FROM mytable t;  -- Context-dependent parsing

The lexer and parser work together to determine whether an identifier-like token is a keyword or user identifier, but this gets complicated with contextual keywords.

Summary: Name Resolution

Name resolution transforms the identifiers in your query into unambiguous references to database objects. Let's consolidate the key concepts:

Key Takeaways

•Namespaces organize database objects; objects in different namespaces can share names while objects in the same namespace cannot.
•Qualification levels (catalog.schema.table.column) provide explicit resolution; unqualified names use search paths.
•Table aliases create query-local references; mandatory for self-joins and recommended for all multi-table queries.
•Column resolution follows SQL clause processing order—SELECT aliases aren't visible in WHERE but are in ORDER BY.
•Scope hierarchy in subqueries and CTEs determines which names are visible; inner scopes can shadow outer ones.
•Function resolution uses overload matching based on argument count and type compatibility.
•Case sensitivity varies dramatically by database; lowercase unquoted identifiers are most portable.

What's Next:

With all names resolved to specific objects, the semantic analyzer's final major task is type checking—ensuring that operations are applied to compatible data types. The next page explores SQL's type system, implicit conversions, and how type mismatches are detected and resolved.

Page Complete

You now understand SQL name resolution: how databases map textual identifiers to specific objects through qualification, aliasing, scoping, and search paths. This knowledge helps you write unambiguous queries and understand resolution-related errors.

3 / 5

Loading learning content...

Database Management SystemsQuery Processing

Parsing and Validation

LevelIntermediate

Duration75 mins

TopicQuery Processing

3 / 5

Name Resolution

The Art of Knowing What Names Mean

This might seem straightforward, but consider:

SELECT name FROM employees e JOIN departments d ON e.dept_id = d.id

What You Will Learn

Understanding Namespaces in SQL

SQL organizes objects into namespaces—logical domains where names must be unique. Understanding namespaces is fundamental to understanding name resolution.

The SQL Namespace Hierarchy:

Most relational databases organize names into a hierarchical structure:

Catalog (Database)
└── Schema (Namespace)
    ├── Tables
    ├── Views
    ├── Functions
    ├── Procedures
    ├── Sequences
    ├── Types
    └── Other Objects

Within a schema, each object type typically has its own namespace:

Table employees and function employees() can coexist (different namespaces)
But two tables named employees cannot exist in the same schema (same namespace)

SQL Object Namespaces
Namespace	Contains	Uniqueness Scope	Example Conflict
Relations	Tables, Views, Materialized Views	Per schema	Can't have table and view with same name
Functions	Functions, Procedures	Per schema + signature	Same name OK with different parameters
Types	User-defined types, Domains	Per schema	Type and table can share names
Columns	Table/View columns	Per table	Same column name OK in different tables
Aliases	Table/Column aliases	Per query scope	Can reuse aliases in different queries
Constraints	PKs, FKs, Checks, Unique	Per schema (usually)	Constraint names must be unique

namespace-examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Namespace independence examples
 
-- Same name in different object types (often allowed)
CREATE TABLE orders (id INT, total DECIMAL);
CREATE TYPE orders AS (id INT, status TEXT);  -- May work in some DBs
 
-- Same name in different schemas (always allowed)
CREATE SCHEMA sales;
CREATE SCHEMA warehouse;
CREATE TABLE sales.orders (id INT, customer_id INT);
CREATE TABLE warehouse.orders (id INT, product_id INT);
 
-- Same column name in different tables (always allowed)
CREATE TABLE customers (id INT, name VARCHAR(100));
CREATE TABLE products (id INT, name VARCHAR(100));
 
-- Alias reuse in query (scope-dependent)
SELECT e.name AS manager_name FROM employees e
WHERE e.id IN (
    SELECT e.manager_id FROM employees e  -- Different scope, same alias OK
);

Database Variations

Qualified vs. Unqualified Names

Names in SQL can be specified with varying levels of qualification. More qualification means less ambiguity but more verbosity.

Levels of Qualification:

qualification-levels.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Full qualification for table reference (3-part name)
-- catalog.schema.table
SELECT * FROM production_db.hr.employees;
 
-- Schema-qualified (2-part name)
-- schema.table
SELECT * FROM hr.employees;
 
-- Unqualified (1-part name)
-- Uses search_path/current schema
SELECT * FROM employees;
 
-- Full qualification for column reference
-- schema.table.column (3-part after table resolution)
SELECT hr.employees.employee_name FROM hr.employees;
 
-- Alias-qualified column reference
SELECT e.employee_name FROM employees AS e;
 
-- Unqualified column reference
SELECT employee_name FROM employees;

The Schema Search Path:

When names are unqualified, databases use a search path (or equivalent concept) to find objects:

-- PostgreSQL search path
SHOW search_path;      -- Result: "$user", public

-- Set search path explicitly
SET search_path TO myschema, public;

Resolution proceeds left-to-right through the search path until a matching object is found. The $user placeholder expands to the current username, allowing user-specific schemas.

Resolution Algorithm for Unqualified Names:

Parse the name to extract components
If fully qualified, look up directly in specified location
If partially qualified, combine with search path
If unqualified, search each schema in path order
Return first match found, or error if no match

Best Practices for Name Qualification

•Production code — Always schema-qualify table references to avoid path-dependent behavior
•Multi-table queries — Alias all tables and qualify all column references with aliases
•Dynamic SQL — Use fully qualified names to avoid security risks from path manipulation
•Migrations — Specify exact schemas to ensure operations target intended objects
•Interactive queries — Unqualified names are acceptable for convenience

Table Alias Resolution

Alias Introduction:

Aliases are introduced in the FROM clause:

alias-introduction.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Explicit alias with AS keyword (recommended for clarity)
SELECT e.name FROM employees AS e;
 
-- Implicit alias without AS (valid but less clear)
SELECT e.name FROM employees e;
 
-- Subquery alias (AS is often required)
SELECT sub.total 
FROM (SELECT SUM(amount) AS total FROM orders) AS sub;
 
-- Lateral subquery with alias
SELECT d.name, emp.count
FROM departments d
CROSS JOIN LATERAL (
    SELECT COUNT(*) AS count 
    FROM employees WHERE department_id = d.id
) AS emp;
 
-- Table function alias
SELECT t.* 
FROM generate_series(1, 10) AS t(num);

Alias Visibility and Shadowing:

When a table alias is defined, it typically shadows the original table name within that query scope:

-- After aliasing, use the alias, not the original name
SELECT employees.name    -- Often errors or unexpected in some DBs
FROM employees AS e;

-- Correct: use the alias
SELECT e.name
FROM employees AS e;

However, this behavior varies by database:

PostgreSQL: Both original name and alias work
SQL Server: Alias required after introduction; original name may not work
MySQL: Both typically work

Self-Joins Require Aliases:

Aliases become mandatory when joining a table to itself:

self-join-aliases.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Find employees and their managers (self-join)
-- Without aliases: IMPOSSIBLE - can't distinguish instances
SELECT name, manager_name
FROM employees, employees;  -- Error: ambiguous!
 
-- With aliases: Clear and unambiguous  
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
 
-- The aliases 'e' and 'm' create two logical instances of employees
-- Column references must be qualified to specify which instance
 
-- Hierarchical query example
WITH RECURSIVE org_chart AS (
    -- Anchor: top-level employees (no manager)
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: employees reporting to those already in chart
    SELECT e.employee_id, e.name, e.manager_id, o.level + 1
    FROM employees e
    JOIN org_chart o ON e.manager_id = o.employee_id
)
SELECT * FROM org_chart;

Alias Naming Conventions

Column Resolution Rules and Precedence

Column Reference Sources:

Table columns — Defined in table DDL
Computed columns — Expressions given aliases in SELECT
Subquery columns — Columns from derived tables
Set operations — Column names from first query in UNION
Lateral references — Columns from tables earlier in FROM clause
Outer queries — Columns from enclosing queries (correlated subqueries)

Resolution Precedence:

column-resolution-precedence.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Precedence Example 1: Local over Outer Scope
SELECT e.name,
    (SELECT name FROM departments WHERE id = e.dept_id) AS dept_name
FROM employees e;
-- In subquery: 'name' resolves to departments.name (local scope)
-- 'e.dept_id' resolves to outer employees table (correlated reference)
 
-- Precedence Example 2: Explicit Qualifier Wins
SELECT employees.name  -- Explicit: employees.name
FROM employees
JOIN departments ON employees.dept_id = departments.id;
-- 'employees.name' is unambiguous due to table qualifier
 
-- Precedence Example 3: Ambiguity Error
SELECT name  -- Which name?
FROM employees
JOIN departments ON employees.dept_id = departments.id;
-- ERROR: column reference "name" is ambiguous
 
-- Precedence Example 4: SELECT Aliases in ORDER BY
SELECT salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary;  -- Alias from SELECT is visible in ORDER BY
 
-- But NOT in WHERE (processed before SELECT)
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;  -- ERROR: column does not exist

SQL Clause Processing Order:

Column visibility depends on SQL's logical processing order:

1. FROM     → Table aliases become visible
2. JOIN     → Join tables and their aliases visible
3. WHERE    → All columns from FROM/JOIN visible
4. GROUP BY → Column references, can define grouping aliases (some DBs)
5. HAVING   → Aggregates and GROUP BY expressions visible
6. SELECT   → Aliases defined here (visible only to later clauses)
7. ORDER BY → All columns plus SELECT aliases visible
8. LIMIT    → No new visibility

This explains why SELECT aliases work in ORDER BY but not WHERE.

Column Visibility by Clause
Clause	Table Columns	SELECT Aliases	Aggregates	Outer Scope
FROM	Being defined	No	No	Yes (lateral)
WHERE	Yes	No	No	Yes (correlated)
GROUP BY	Yes	Sometimes*	No	Yes
HAVING	Grouped only	Sometimes*	Yes	Yes
SELECT	Yes	Defining	Yes	Yes
ORDER BY	Yes	Yes	Yes	Outer no longer exists

Database Variations

Subquery and CTE Scoping

Queries often contain nested scopes through subqueries and CTEs. Name resolution must correctly handle these scope boundaries, determining when inner scopes can reference outer scopes and vice versa.

Scope Types in SQL:

SQL Scope Hierarchy

•Main Query Scope — The outermost SELECT/INSERT/UPDATE/DELETE
•CTE Scope — WITH clause definitions; visible to main query and later CTEs
•Subquery Scope — Nested queries in SELECT, FROM, WHERE, etc.
•Lateral Scope — Special FROM clause scoping for lateral joins
•Correlated Scope — Subqueries referencing outer query columns

scope-examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- CTE Scope: CTEs visible to later CTEs and main query
WITH 
    dept_summary AS (
        SELECT department_id, COUNT(*) as emp_count
        FROM employees
        GROUP BY department_id
    ),
    high_count_depts AS (
        SELECT department_id 
        FROM dept_summary  -- References earlier CTE
        WHERE emp_count > 10
    )
SELECT d.name, ds.emp_count
FROM departments d
JOIN dept_summary ds ON d.id = ds.department_id  -- Main query uses CTE
JOIN high_count_depts hcd ON d.id = hcd.department_id;
 
-- Correlated Subquery: Inner references outer
SELECT e.name, e.salary,
    (SELECT AVG(salary) FROM employees 
     WHERE department_id = e.department_id) AS dept_avg  -- 'e' from outer
FROM employees e;
 
-- Lateral Subquery: Explicit outer reference permission
SELECT d.name, emp_stats.*
FROM departments d
CROSS JOIN LATERAL (
    SELECT COUNT(*) AS count, AVG(salary) AS avg_salary
    FROM employees
    WHERE department_id = d.id  -- 'd' visible due to LATERAL
) AS emp_stats;
 
-- Without LATERAL, this would error:
-- JOIN (SELECT ... WHERE department_id = d.id) -- Error: 'd' not visible

Scope Resolution Order:

When resolving a name, the analyzer searches scopes from innermost to outermost:

1. Current query's FROM clause (aliases and columns)
2. Enclosing query's scope (if correlated subquery)
3. Further enclosing queries (deeply nested correlation)
4. CTE definitions (if in WITH clause scope)

Scope Shadowing:

An inner scope can shadow (hide) names from outer scopes:

scope-shadowing.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Example: Shadowing in nested subqueries
SELECT e.name AS outer_name,
    (SELECT e.name  -- This 'e' is from inner FROM, not outer!
     FROM employees e  -- New 'e' alias shadows outer 'e'
     WHERE e.manager_id = outer_e.employee_id
    ) AS manager_name
FROM employees outer_e;  -- Use distinct alias to avoid confusion
 
-- Best practice: Use unique aliases across all nesting levels
SELECT emp.name,
    (SELECT mgr.name 
     FROM employees mgr 
     WHERE mgr.employee_id = emp.manager_id
    ) AS manager_name
FROM employees emp;

Shadowing Hazards

Function and Operator Resolution

Function Overload Resolution:

Consider the function SUBSTR:

SUBSTR(string, start)           -- 2-argument version
SUBSTR(string, start, length)   -- 3-argument version

The database must choose which overload to call based on argument count and types.

Resolution Algorithm:

Find all functions with matching name in search path
Filter to those accepting correct argument count
Filter to those accepting compatible argument types
If multiple remain, rank by type conversion cost
Select lowest-cost match or error if ambiguous

function-resolution.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- PostgreSQL function overloading example
 
-- Multiple ROUND functions exist:
-- round(numeric) -> numeric
-- round(numeric, integer) -> numeric  
-- round(double precision) -> double precision
 
SELECT ROUND(3.14159);        -- Uses round(numeric) → 3
SELECT ROUND(3.14159, 2);     -- Uses round(numeric, int) → 3.14
SELECT ROUND(3.14159::float); -- Uses round(double precision) → 3
 
-- Type coercion in function resolution
SELECT CONCAT('Value: ', 42);     
-- 42 is integer, CONCAT expects text
-- Database implicitly casts 42 to text
 
-- Operator resolution works similarly
-- The '+' operator is overloaded:
SELECT 1 + 2;           -- integer + integer → integer
SELECT 1.5 + 2;         -- numeric + integer → numeric  
SELECT '2020-01-01'::date + 7;  -- date + integer → date
 
-- Explicit casting resolves ambiguity
SELECT SUBSTRING('hello'::varchar, 1, 3);  -- Forces specific overload

Type Precedence and Coercion:

When exact type matches aren't found, databases use type coercion to find acceptable matches. Each database defines a type hierarchy and conversion costs:

Integer → BigInt → Numeric → Float → String
(lower priority ——————————————→ higher priority for coercion)

Ambiguity errors occur when multiple overloads have equal coercion cost.

Schema-Qualified Function Calls:

schema-qualified-functions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- User-defined function in specific schema
CREATE FUNCTION analytics.compute_score(numeric) RETURNS numeric AS $$
    SELECT $1 * 100;
$$ LANGUAGE SQL;
 
-- Qualified call
SELECT analytics.compute_score(0.85);
 
-- Unqualified call requires function's schema in search_path
SET search_path TO analytics, public;
SELECT compute_score(0.85);
 
-- Built-in function override (be careful!)
CREATE FUNCTION public.upper(text) RETURNS text AS $$
    SELECT 'OVERRIDDEN: ' || pg_catalog.upper($1);
$$ LANGUAGE SQL;
 
-- Now public.upper shadows pg_catalog.upper
SELECT upper('hello');  -- Result depends on search_path order!

Case Sensitivity and Quoted Identifiers

Case sensitivity in name resolution is a notorious source of confusion and portability issues. Different databases handle case differently, and quoting identifiers changes behavior.

Standard SQL Behavior:

The SQL standard says:

Unquoted identifiers are folded (converted) to uppercase
Quoted identifiers preserve exact case and are case-sensitive
Keywords are case-insensitive

Actual Database Behavior:

Case Sensitivity by Database
Database	Unquoted Identifiers	Quoted Identifiers	Default Folding
PostgreSQL	Case-insensitive	Case-sensitive	Lowercase
MySQL (Linux)	Case-sensitive*	Case-sensitive	As-written
MySQL (Windows)	Case-insensitive	Case-insensitive	Lowercase
Oracle	Case-insensitive	Case-sensitive	Uppercase
SQL Server	Collation-dependent	Collation-dependent	As-written
SQLite	Case-insensitive	Case-sensitive	As-written

case-sensitivity-examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- PostgreSQL Examples
 
-- These are all the same table (folded to lowercase)
CREATE TABLE Employees (ID INT);
SELECT * FROM EMPLOYEES;
SELECT * FROM employees;
SELECT * FROM EmPlOyEeS;
 
-- But this is different (quoted = exact case preserved)
CREATE TABLE "Employees" (ID INT);
SELECT * FROM "Employees";  -- Works
SELECT * FROM employees;    -- Different table!
 
-- Quoting can create headaches
CREATE TABLE "MyTable" ("MyColumn" INT);
-- Now you're stuck quoting forever:
SELECT "MyColumn" FROM "MyTable";
-- This fails:
SELECT MyColumn FROM MyTable;  -- Looks for mytable.mycolumn
 
-- Oracle behaves oppositely (folds to UPPERCASE)
-- Unquoted: CREATE TABLE employees → stored as EMPLOYEES
-- These work: SELECT * FROM EMPLOYEES/employees/EmPlOyEeS
-- Quoted "employees" would be DIFFERENT from EMPLOYEES

Portability Best Practice

Reserved Words and Quoting:

Quoting also allows using reserved words as identifiers:

-- These are reserved words used as identifiers
CREATE TABLE "select" ("from" INT, "where" TEXT);  -- Quoted works
CREATE TABLE order (id INT);  -- ERROR: ORDER is reserved
CREATE TABLE "order" (id INT);  -- Quoted works

-- Some databases allow unquoted reserved words in column position
SELECT t.from, t.order FROM mytable t;  -- Context-dependent parsing

The lexer and parser work together to determine whether an identifier-like token is a keyword or user identifier, but this gets complicated with contextual keywords.

Summary: Name Resolution

Name resolution transforms the identifiers in your query into unambiguous references to database objects. Let's consolidate the key concepts:

Key Takeaways

•Namespaces organize database objects; objects in different namespaces can share names while objects in the same namespace cannot.
•Qualification levels (catalog.schema.table.column) provide explicit resolution; unqualified names use search paths.
•Table aliases create query-local references; mandatory for self-joins and recommended for all multi-table queries.
•Column resolution follows SQL clause processing order—SELECT aliases aren't visible in WHERE but are in ORDER BY.
•Scope hierarchy in subqueries and CTEs determines which names are visible; inner scopes can shadow outer ones.
•Function resolution uses overload matching based on argument count and type compatibility.
•Case sensitivity varies dramatically by database; lowercase unquoted identifiers are most portable.

What's Next:

Page Complete

3 / 5