Database Management SystemsSELECT Basics

SELECT Basics: Mastering Data Retrieval

LevelBeginner

Duration60 mins

TopicSELECT Basics

2 / 5

The FROM Clause: Specifying Data Sources

Where Data Begins

If SELECT defines what data to retrieve, FROM defines where to retrieve it from. The FROM clause is the foundation upon which every query is built—it specifies the tables, views, subqueries, and other data sources that provide the raw material for your query.

In the logical processing order of SQL, FROM is actually evaluated first, before WHERE, GROUP BY, or SELECT. This means FROM determines the universe of data available to all subsequent clauses. Understanding FROM deeply is essential for:

Knowing how to reference tables and views
Understanding how table aliases work and why they're critical
Grasping the foundation for JOIN operations
Working with derived tables and subqueries
Leveraging advanced constructs like LATERAL joins and table-valued functions

What You Will Learn

By the end of this page, you will understand the FROM clause comprehensively—from basic table references through aliasing, schema qualification, derived tables, and the critical relationship between FROM and the rest of the query. You'll be equipped to specify data sources correctly in any SQL context.

FROM Clause Fundamentals

At its simplest, the FROM clause names a single table from which to retrieve data. However, the full syntax supports far more sophisticated data source specifications.

Basic syntax:

FROM table_reference [, table_reference ...]

Where each table_reference can be:

A table name (optionally schema-qualified)
A view name
A subquery (derived table)
A table-valued function
A join expression
A common table expression reference

Key concept: The virtual table

The FROM clause constructs a virtual table that serves as input to all subsequent processing. When FROM contains a single table, this virtual table is simply that table's contents. When FROM contains multiple tables or joins, the virtual table is the result of combining them.

from_basic_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Simple single-table reference
SELECT * FROM employees;
 
-- Schema-qualified table reference
SELECT * FROM hr.employees;
 
-- Database + schema + table (fully qualified)
SELECT * FROM company_db.hr.employees;
 
-- Multiple tables (implicit cross join - rarely what you want!)
SELECT * FROM employees, departments;
-- Returns: every employee paired with every department
 
-- With meaningful join condition (old-style)
SELECT * 
FROM employees, departments
WHERE employees.department_id = departments.id;
 
-- Modern explicit JOIN syntax (preferred)
SELECT * 
FROM employees
JOIN departments ON employees.department_id = departments.id;

Beware Comma-Separated Tables

Listing multiple tables separated by commas (FROM a, b) creates a cross join—every row from a paired with every row from b. For tables with 1000 rows each, this produces 1,000,000 rows! Always use explicit JOIN syntax for clarity and to prevent accidental cross joins.

FROM clause processing model:

Each table reference is resolved to actual data
If multiple tables exist, they're combined (via cross join or explicit joins)
The resulting virtual table becomes input for WHERE clause filtering
Table aliases defined in FROM become available for subsequent clauses

This processing happens conceptually before anything else in the query. Even though you write SELECT first, the database logically starts with FROM.

Table Aliases: Naming Your Data Sources

Table aliases provide shorthand names for tables referenced in the FROM clause. While aliases might seem like a convenience feature, they are actually essential for certain query patterns and dramatically improve query readability.

Why table aliases matter:

Self-joins: When joining a table to itself, aliases are mandatory to distinguish the two instances
Long table names: Aliasing employee_performance_metrics_historical as epmh improves readability
Column disambiguation: In multi-table queries, e.name is clearer than employees.name
Subqueries: Derived tables (subqueries in FROM) require aliases
Expression clarity: Complex joins become readable when each table has a meaningful short name

table_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
29
30
31
32
33
34
35
36
37
38
-- Basic alias syntax (AS keyword optional but recommended)
SELECT e.first_name, e.last_name, e.salary
FROM employees AS e;
 
-- Without AS (valid but less explicit)
SELECT e.first_name, e.last_name, e.salary
FROM employees e;
 
-- Multiple table aliases
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name,
    m.first_name AS manager_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id
JOIN employees AS m ON e.manager_id = m.employee_id;
 
-- Self-join: finding employees and their managers
-- MUST use aliases to distinguish the two employee table references
SELECT 
    emp.first_name AS employee,
    emp.last_name AS emp_last_name,
    mgr.first_name AS manager,
    mgr.last_name AS mgr_last_name
FROM employees AS emp
LEFT JOIN employees AS mgr ON emp.manager_id = mgr.employee_id;
 
-- Once aliased, you MUST use the alias (original name is hidden)
SELECT 
    e.first_name,
    employees.last_name  -- ERROR in most databases! Alias 'e' hides 'employees'
FROM employees AS e;
 
-- Schema-qualified with alias
SELECT o.order_id, o.order_date
FROM sales.orders AS o
WHERE o.status = 'pending';

Table Alias Best Practices
Practice	Explanation	Example
Single letter for simple queries	Quick to write, easy to read for simple joins	`FROM employees e`
Meaningful abbreviations for complex queries	Helps readers understand which table is which	`FROM customer_orders co`
Consistent conventions	Use same alias for same table across queries	Always `e` for employees
Always use AS keyword	More explicit, some databases require it for column aliases	`FROM employees AS e`
Avoid reserved words	Some databases reject reserved words as aliases	Avoid `FROM table t, order o`

Alias Scope Rules

Once you define a table alias in FROM, that alias is available in SELECT, WHERE, GROUP BY, HAVING, and ORDER BY. In most databases, once aliased, the original table name cannot be used within that query—you must use the alias.

Schema and Catalog Qualification

In enterprise database environments, tables are organized within schemas and catalogs (databases). Understanding how to reference tables across these organizational boundaries is essential for working with complex database deployments.

Naming hierarchy:

catalog.schema.table

Or in some terminology:

database.schema.table

Different database systems have varying terminology and defaults:

PostgreSQL: database.schema.table (default schema: public)
MySQL: database.table (no schema layer, database = schema)
SQL Server: database.schema.table (default schema: dbo)
Oracle: schema.table (user = schema)

Qualification Levels by Database
Database	Format	Example	Default Schema
PostgreSQL	database.schema.table	`mydb.public.employees`	public
MySQL	database.table	`mydb.employees`	Current database
SQL Server	database.schema.table	`mydb.dbo.employees`	dbo
Oracle	schema.table	`hr.employees`	Current user's schema
SQLite	database.table	`main.employees`	main

schema_qualification.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
35
36
-- Unqualified reference uses current/default schema
SELECT * FROM employees;
 
-- Schema-qualified reference
SELECT * FROM hr.employees;
 
-- Fully qualified reference (SQL Server syntax)
SELECT * FROM CompanyDB.hr.employees;
 
-- Cross-database query (SQL Server)
SELECT 
    p.product_name,
    s.quantity
FROM ProductDB.inventory.products AS p
JOIN SalesDB.orders.order_items AS s ON p.product_id = s.product_id;
 
-- PostgreSQL cross-schema query
SELECT 
    e.employee_name,
    p.project_name
FROM hr.employees AS e
JOIN projects.assignments AS a ON e.id = a.employee_id
JOIN projects.projects AS p ON a.project_id = p.id;
 
-- Setting default schema (varies by DBMS)
-- PostgreSQL
SET search_path TO hr, public;
 
-- SQL Server
ALTER USER current_user WITH DEFAULT_SCHEMA = hr;
 
-- MySQL
USE sales_database;
 
-- After setting default, unqualified names resolve to default
SELECT * FROM employees;  -- Now resolves to hr.employees

When to Fully Qualify

Always fully qualify table names in: (1) Stored procedures and functions that may be called from different schema contexts, (2) Cross-database or cross-schema queries, (3) Production deployment scripts, (4) Documentation and training materials. Use unqualified names for ad-hoc queries in a known context.

Portability considerations:

Schema qualification syntax varies significantly across database systems. When writing portable SQL:

Use a single schema if possible
Configure default schemas appropriately
Abstract schema names in application code
Document schema dependencies in comments

Derived Tables (Subqueries in FROM)

A derived table is a subquery that appears in the FROM clause and acts as a virtual table for the outer query. This powerful technique allows you to:

Pre-aggregate data before joining
Filter or transform data before applying outer conditions
Create intermediate result sets for complex logic
Work around restrictions (like referencing aggregates in WHERE)

Syntax:

FROM (subquery) AS alias

The alias is mandatory—unlike regular tables, derived tables must be named.

derived_tables.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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- Simple derived table: pre-filtering
SELECT dt.employee_id, dt.full_name, dt.salary
FROM (
    SELECT 
        employee_id,
        CONCAT(first_name, ' ', last_name) AS full_name,
        salary
    FROM employees
    WHERE status = 'active'
) AS dt
WHERE dt.salary > 50000;
 
-- Derived table for aggregation before join
SELECT 
    d.department_name,
    dept_stats.employee_count,
    dept_stats.avg_salary
FROM departments AS d
JOIN (
    SELECT 
        department_id,
        COUNT(*) AS employee_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_stats ON d.department_id = dept_stats.department_id;
 
-- Working around WHERE limitation on aggregates
-- Problem: "Find employees earning more than their department's average"
-- Cannot use aggregate in WHERE directly
 
-- Solution: Derived table with department averages
SELECT e.employee_id, e.first_name, e.salary, dept_avg.avg_salary
FROM employees AS e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;
 
-- Nested derived tables (use sparingly - CTEs often clearer)
SELECT top_earners.dept_name, top_earners.max_salary
FROM (
    SELECT 
        d.department_name AS dept_name,
        dept_salaries.max_sal AS max_salary
    FROM departments AS d
    JOIN (
        SELECT department_id, MAX(salary) AS max_sal
        FROM employees
        GROUP BY department_id
    ) AS dept_salaries ON d.department_id = dept_salaries.department_id
) AS top_earners
WHERE top_earners.max_salary > 100000;

Derived Tables vs CTEs

Derived tables and Common Table Expressions (CTEs) solve similar problems. CTEs (WITH clause) are often more readable, especially when the subquery is referenced multiple times or when queries become deeply nested. Consider CTEs for complex derived table scenarios.

Derived Table Requirements

•Must have an alias: FROM (SELECT ...) AS dt — the alias is required
•Must have defined columns: All columns in the subquery must be named (use aliases for expressions)
•Cannot reference outer query: Unlike correlated subqueries in WHERE, FROM subqueries cannot reference outer tables (except with LATERAL)
•Materialization varies: Database may or may not materialize the derived table depending on optimization decisions

Views as Data Sources

Views are stored queries that act as virtual tables. From the perspective of the FROM clause, a view is indistinguishable from a regular table—you reference it by name and can alias, join, and filter it exactly like a table.

Why views matter:

Abstraction: Hide complex joins and calculations behind a simple name
Security: Expose only specific columns or rows to certain users
Consistency: Ensure everyone uses the same business logic
Simplification: Turn complex queries into simple table references

views_in_from.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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- Creating a view (for context)
CREATE VIEW employee_directory AS
SELECT 
    e.employee_id,
    CONCAT(e.first_name, ' ', e.last_name) AS full_name,
    e.email,
    d.department_name,
    e.hire_date
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id
WHERE e.status = 'active';
 
-- Using the view in FROM (exactly like a table)
SELECT * FROM employee_directory;
 
-- Filtering a view
SELECT full_name, department_name
FROM employee_directory
WHERE department_name = 'Engineering';
 
-- Joining views with tables
SELECT 
    ed.full_name,
    ed.department_name,
    p.project_name
FROM employee_directory AS ed
JOIN project_assignments AS pa ON ed.employee_id = pa.employee_id
JOIN projects AS p ON pa.project_id = p.project_id;
 
-- Joining multiple views
SELECT 
    ed.full_name,
    ds.total_salary_budget
FROM employee_directory AS ed
JOIN department_statistics AS ds ON ed.department_name = ds.department_name;
 
-- View with aggregation
CREATE VIEW department_summary AS
SELECT 
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS average_salary,
    SUM(e.salary) AS total_salary
FROM departments AS d
LEFT JOIN employees AS e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
 
-- Using aggregation view
SELECT department_name, employee_count
FROM department_summary
WHERE employee_count > 10
ORDER BY average_salary DESC;

Views and Query Optimization

When you query a view, the database typically merges the view's definition with your outer query for optimization. This means predicates you apply to a view can often be pushed down into the view's underlying query. However, complex views (those with DISTINCT, GROUP BY, or UNION) may require materialization before your outer predicates apply, potentially impacting performance.

View Types in FROM Clause
View Type	Description	FROM Usage
Simple view	Single table, no aggregation	Fully transparent—filter pushdown works
Join view	Multiple tables joined	Usually transparent—join conditions preserved
Aggregate view	Contains GROUP BY	May block filter pushdown
Materialized view	Pre-computed and stored	Nearly identical to table access
Updatable view	Allows INSERT/UPDATE/DELETE	Same as simple view for SELECT

Table-Valued Functions in FROM

Some database systems support table-valued functions (TVFs)—functions that return a table rather than a scalar value. These can appear in the FROM clause like any other table source, enabling powerful programmatic data generation and transformation.

Built-in table-valued functions:

Many databases provide built-in functions that return tables:

GENERATE_SERIES() in PostgreSQL
STRING_SPLIT() in SQL Server
JSON_TABLE() in MySQL/Oracle for parsing JSON
UNNEST() in PostgreSQL for expanding arrays
XMLTABLE() for parsing XML

table_valued_functions.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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- PostgreSQL: GENERATE_SERIES for number sequences
SELECT num
FROM GENERATE_SERIES(1, 10) AS num;
-- Returns: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
 
-- Date series for calendar generation
SELECT date::date AS calendar_date
FROM GENERATE_SERIES(
    '2024-01-01'::date,
    '2024-12-31'::date,
    '1 day'::interval
) AS date;
 
-- SQL Server: STRING_SPLIT for parsing delimited strings
SELECT value AS tag
FROM STRING_SPLIT('red,green,blue,yellow', ',');
-- Returns: red, green, blue, yellow (as separate rows)
 
-- Using TVF in joins
SELECT 
    p.product_id,
    p.product_name,
    t.value AS tag
FROM products AS p
CROSS APPLY STRING_SPLIT(p.tags, ',') AS t;
 
-- PostgreSQL: UNNEST for array expansion
SELECT 
    order_id,
    item
FROM orders
CROSS JOIN LATERAL UNNEST(item_array) AS item;
 
-- MySQL: JSON_TABLE for parsing JSON
SELECT jt.*
FROM orders,
JSON_TABLE(
    order_json,
    '$.items[*]' COLUMNS (
        product_id INT PATH '$.product_id',
        quantity INT PATH '$.quantity',
        price DECIMAL(10,2) PATH '$.price'
    )
) AS jt;
 
-- User-defined table-valued function (SQL Server)
CREATE FUNCTION GetEmployeesByDepartment(@DeptId INT)
RETURNS TABLE
AS
RETURN (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = @DeptId
);
 
-- Using UDF-TVF in FROM
SELECT * FROM GetEmployeesByDepartment(5);
 
-- Joining with UDF-TVF
SELECT 
    d.department_name,
    e.first_name,
    e.salary
FROM departments AS d
CROSS APPLY GetEmployeesByDepartment(d.department_id) AS e;

CROSS APPLY vs LATERAL

SQL Server uses CROSS APPLY to reference outer query columns in a table-valued function. PostgreSQL uses LATERAL for the same purpose. Standard SQL specifies LATERAL JOIN. These allow row-by-row processing where each row from the left side is used to parameterize the table-valued function on the right.

The DUAL Table and SELECT Without FROM

In standard SQL, the FROM clause is technically optional when you're selecting only constants or expressions that don't reference table data. However, different databases handle this inconsistently.

The Oracle DUAL table:

Oracle historically required a FROM clause for every SELECT. To accommodate queries that don't need table data, Oracle provides a special one-row, one-column table called DUAL. This has become a common pattern across many databases.

FROM-less SELECT Support by Database
Database	FROM Optional?	Workaround
PostgreSQL	Yes	`SELECT 1` works
MySQL	Yes	`SELECT 1` works, DUAL also supported
SQL Server	Yes	`SELECT 1` works
SQLite	Yes	`SELECT 1` works
Oracle	No	Must use `SELECT 1 FROM DUAL`
DB2	No	Must use `SELECT 1 FROM SYSIBM.SYSDUMMY1`

no_from_queries.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
35
36
37
-- PostgreSQL/MySQL/SQL Server: No FROM needed
SELECT 1 + 1;
-- Returns: 2
 
SELECT CURRENT_TIMESTAMP;
-- Returns: current date/time
 
SELECT 'Hello' || ' ' || 'World';
-- Returns: Hello World (PostgreSQL)
 
-- Oracle: DUAL required
SELECT 1 + 1 FROM DUAL;
SELECT SYSDATE FROM DUAL;
SELECT 'Hello' || ' ' || 'World' FROM DUAL;
 
-- MySQL: DUAL optional but supported for Oracle compatibility
SELECT 1 + 1 FROM DUAL;  -- Works
SELECT 1 + 1;            -- Also works
 
-- Practical uses of FROM-less queries:
-- 1. Testing database connectivity
SELECT 1;
 
-- 2. Getting current timestamp
SELECT CURRENT_TIMESTAMP AS now;
 
-- 3. Evaluating expressions
SELECT POWER(2, 10) AS result;  -- 1024
 
-- 4. String manipulation testing
SELECT UPPER('test') AS upper_result;
 
-- 5. Generating single-row results for UNION
SELECT 'Header' AS category, 0 AS sort_order
UNION ALL
SELECT category_name, 1 FROM categories
ORDER BY sort_order, category;

Portable Constant Queries

For maximum portability, consider using SELECT expression FROM (SELECT 1) AS dummy which works across all major databases. However, in most modern databases (except Oracle), a simple SELECT expression without FROM is perfectly valid and preferred for clarity.

Summary: The FROM Clause

The FROM clause is the foundation of every SELECT statement—specifying where your data originates. Despite being written after SELECT, it is processed first and creates the virtual table from which all other operations draw.

Let's consolidate the key points:

Key Takeaways

•FROM is processed first — It defines the data universe for all subsequent clauses, despite appearing after SELECT in syntax.
•Table aliases are essential — Use aliases for readability, self-joins (required), and disambiguation in multi-table queries.
•Schema qualification — Use explicit schema names for clarity, cross-schema queries, and portable stored procedures.
•Derived tables — Subqueries in FROM create virtual tables for intermediate calculations, aggregations, and logic encapsulation.
•Views are transparent — Reference views exactly like tables; the database handles query merging and optimization.
•Table-valued functions — Use LATERAL/CROSS APPLY to incorporate row-by-row function results in your FROM clause.
•FROM is optional — For constant expressions, most databases allow SELECT without FROM (Oracle requires DUAL).

What's next:

With a solid understanding of SELECT and FROM, we'll next examine column selection in detail—how to precisely specify which data elements appear in your result set, including wildcards, expressions, and complex selection patterns.

Page Complete

You now understand the FROM clause comprehensively—from simple table references through derived tables, views, and table-valued functions. This foundation is essential for all query construction, especially when we explore joins in detail later.

2 / 5

Loading learning content...

Database Management SystemsSELECT Basics

SELECT Basics: Mastering Data Retrieval

LevelBeginner

Duration60 mins

TopicSELECT Basics

2 / 5

The FROM Clause: Specifying Data Sources

Where Data Begins

Knowing how to reference tables and views
Understanding how table aliases work and why they're critical
Grasping the foundation for JOIN operations
Working with derived tables and subqueries
Leveraging advanced constructs like LATERAL joins and table-valued functions

What You Will Learn

FROM Clause Fundamentals

At its simplest, the FROM clause names a single table from which to retrieve data. However, the full syntax supports far more sophisticated data source specifications.

Basic syntax:

FROM table_reference [, table_reference ...]

Where each table_reference can be:

A table name (optionally schema-qualified)
A view name
A subquery (derived table)
A table-valued function
A join expression
A common table expression reference

Key concept: The virtual table

from_basic_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Simple single-table reference
SELECT * FROM employees;
 
-- Schema-qualified table reference
SELECT * FROM hr.employees;
 
-- Database + schema + table (fully qualified)
SELECT * FROM company_db.hr.employees;
 
-- Multiple tables (implicit cross join - rarely what you want!)
SELECT * FROM employees, departments;
-- Returns: every employee paired with every department
 
-- With meaningful join condition (old-style)
SELECT * 
FROM employees, departments
WHERE employees.department_id = departments.id;
 
-- Modern explicit JOIN syntax (preferred)
SELECT * 
FROM employees
JOIN departments ON employees.department_id = departments.id;

Beware Comma-Separated Tables

FROM clause processing model:

Each table reference is resolved to actual data
If multiple tables exist, they're combined (via cross join or explicit joins)
The resulting virtual table becomes input for WHERE clause filtering
Table aliases defined in FROM become available for subsequent clauses

This processing happens conceptually before anything else in the query. Even though you write SELECT first, the database logically starts with FROM.

Table Aliases: Naming Your Data Sources

Why table aliases matter:

Self-joins: When joining a table to itself, aliases are mandatory to distinguish the two instances
Long table names: Aliasing employee_performance_metrics_historical as epmh improves readability
Column disambiguation: In multi-table queries, e.name is clearer than employees.name
Subqueries: Derived tables (subqueries in FROM) require aliases
Expression clarity: Complex joins become readable when each table has a meaningful short name

table_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
29
30
31
32
33
34
35
36
37
38
-- Basic alias syntax (AS keyword optional but recommended)
SELECT e.first_name, e.last_name, e.salary
FROM employees AS e;
 
-- Without AS (valid but less explicit)
SELECT e.first_name, e.last_name, e.salary
FROM employees e;
 
-- Multiple table aliases
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name,
    m.first_name AS manager_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id
JOIN employees AS m ON e.manager_id = m.employee_id;
 
-- Self-join: finding employees and their managers
-- MUST use aliases to distinguish the two employee table references
SELECT 
    emp.first_name AS employee,
    emp.last_name AS emp_last_name,
    mgr.first_name AS manager,
    mgr.last_name AS mgr_last_name
FROM employees AS emp
LEFT JOIN employees AS mgr ON emp.manager_id = mgr.employee_id;
 
-- Once aliased, you MUST use the alias (original name is hidden)
SELECT 
    e.first_name,
    employees.last_name  -- ERROR in most databases! Alias 'e' hides 'employees'
FROM employees AS e;
 
-- Schema-qualified with alias
SELECT o.order_id, o.order_date
FROM sales.orders AS o
WHERE o.status = 'pending';

Table Alias Best Practices
Practice	Explanation	Example
Single letter for simple queries	Quick to write, easy to read for simple joins	`FROM employees e`
Meaningful abbreviations for complex queries	Helps readers understand which table is which	`FROM customer_orders co`
Consistent conventions	Use same alias for same table across queries	Always `e` for employees
Always use AS keyword	More explicit, some databases require it for column aliases	`FROM employees AS e`
Avoid reserved words	Some databases reject reserved words as aliases	Avoid `FROM table t, order o`

Alias Scope Rules

Schema and Catalog Qualification

Naming hierarchy:

catalog.schema.table

Or in some terminology:

database.schema.table

Different database systems have varying terminology and defaults:

PostgreSQL: database.schema.table (default schema: public)
MySQL: database.table (no schema layer, database = schema)
SQL Server: database.schema.table (default schema: dbo)
Oracle: schema.table (user = schema)

Qualification Levels by Database
Database	Format	Example	Default Schema
PostgreSQL	database.schema.table	`mydb.public.employees`	public
MySQL	database.table	`mydb.employees`	Current database
SQL Server	database.schema.table	`mydb.dbo.employees`	dbo
Oracle	schema.table	`hr.employees`	Current user's schema
SQLite	database.table	`main.employees`	main

schema_qualification.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
35
36
-- Unqualified reference uses current/default schema
SELECT * FROM employees;
 
-- Schema-qualified reference
SELECT * FROM hr.employees;
 
-- Fully qualified reference (SQL Server syntax)
SELECT * FROM CompanyDB.hr.employees;
 
-- Cross-database query (SQL Server)
SELECT 
    p.product_name,
    s.quantity
FROM ProductDB.inventory.products AS p
JOIN SalesDB.orders.order_items AS s ON p.product_id = s.product_id;
 
-- PostgreSQL cross-schema query
SELECT 
    e.employee_name,
    p.project_name
FROM hr.employees AS e
JOIN projects.assignments AS a ON e.id = a.employee_id
JOIN projects.projects AS p ON a.project_id = p.id;
 
-- Setting default schema (varies by DBMS)
-- PostgreSQL
SET search_path TO hr, public;
 
-- SQL Server
ALTER USER current_user WITH DEFAULT_SCHEMA = hr;
 
-- MySQL
USE sales_database;
 
-- After setting default, unqualified names resolve to default
SELECT * FROM employees;  -- Now resolves to hr.employees

When to Fully Qualify

Portability considerations:

Schema qualification syntax varies significantly across database systems. When writing portable SQL:

Use a single schema if possible
Configure default schemas appropriately
Abstract schema names in application code
Document schema dependencies in comments

Derived Tables (Subqueries in FROM)

A derived table is a subquery that appears in the FROM clause and acts as a virtual table for the outer query. This powerful technique allows you to:

Pre-aggregate data before joining
Filter or transform data before applying outer conditions
Create intermediate result sets for complex logic
Work around restrictions (like referencing aggregates in WHERE)

Syntax:

FROM (subquery) AS alias

The alias is mandatory—unlike regular tables, derived tables must be named.

derived_tables.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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- Simple derived table: pre-filtering
SELECT dt.employee_id, dt.full_name, dt.salary
FROM (
    SELECT 
        employee_id,
        CONCAT(first_name, ' ', last_name) AS full_name,
        salary
    FROM employees
    WHERE status = 'active'
) AS dt
WHERE dt.salary > 50000;
 
-- Derived table for aggregation before join
SELECT 
    d.department_name,
    dept_stats.employee_count,
    dept_stats.avg_salary
FROM departments AS d
JOIN (
    SELECT 
        department_id,
        COUNT(*) AS employee_count,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_stats ON d.department_id = dept_stats.department_id;
 
-- Working around WHERE limitation on aggregates
-- Problem: "Find employees earning more than their department's average"
-- Cannot use aggregate in WHERE directly
 
-- Solution: Derived table with department averages
SELECT e.employee_id, e.first_name, e.salary, dept_avg.avg_salary
FROM employees AS e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;
 
-- Nested derived tables (use sparingly - CTEs often clearer)
SELECT top_earners.dept_name, top_earners.max_salary
FROM (
    SELECT 
        d.department_name AS dept_name,
        dept_salaries.max_sal AS max_salary
    FROM departments AS d
    JOIN (
        SELECT department_id, MAX(salary) AS max_sal
        FROM employees
        GROUP BY department_id
    ) AS dept_salaries ON d.department_id = dept_salaries.department_id
) AS top_earners
WHERE top_earners.max_salary > 100000;

Derived Tables vs CTEs

Derived Table Requirements

•Must have an alias: FROM (SELECT ...) AS dt — the alias is required
•Must have defined columns: All columns in the subquery must be named (use aliases for expressions)
•Cannot reference outer query: Unlike correlated subqueries in WHERE, FROM subqueries cannot reference outer tables (except with LATERAL)
•Materialization varies: Database may or may not materialize the derived table depending on optimization decisions

Views as Data Sources

Why views matter:

Abstraction: Hide complex joins and calculations behind a simple name
Security: Expose only specific columns or rows to certain users
Consistency: Ensure everyone uses the same business logic
Simplification: Turn complex queries into simple table references

views_in_from.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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- Creating a view (for context)
CREATE VIEW employee_directory AS
SELECT 
    e.employee_id,
    CONCAT(e.first_name, ' ', e.last_name) AS full_name,
    e.email,
    d.department_name,
    e.hire_date
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id
WHERE e.status = 'active';
 
-- Using the view in FROM (exactly like a table)
SELECT * FROM employee_directory;
 
-- Filtering a view
SELECT full_name, department_name
FROM employee_directory
WHERE department_name = 'Engineering';
 
-- Joining views with tables
SELECT 
    ed.full_name,
    ed.department_name,
    p.project_name
FROM employee_directory AS ed
JOIN project_assignments AS pa ON ed.employee_id = pa.employee_id
JOIN projects AS p ON pa.project_id = p.project_id;
 
-- Joining multiple views
SELECT 
    ed.full_name,
    ds.total_salary_budget
FROM employee_directory AS ed
JOIN department_statistics AS ds ON ed.department_name = ds.department_name;
 
-- View with aggregation
CREATE VIEW department_summary AS
SELECT 
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS average_salary,
    SUM(e.salary) AS total_salary
FROM departments AS d
LEFT JOIN employees AS e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
 
-- Using aggregation view
SELECT department_name, employee_count
FROM department_summary
WHERE employee_count > 10
ORDER BY average_salary DESC;

Views and Query Optimization

View Types in FROM Clause
View Type	Description	FROM Usage
Simple view	Single table, no aggregation	Fully transparent—filter pushdown works
Join view	Multiple tables joined	Usually transparent—join conditions preserved
Aggregate view	Contains GROUP BY	May block filter pushdown
Materialized view	Pre-computed and stored	Nearly identical to table access
Updatable view	Allows INSERT/UPDATE/DELETE	Same as simple view for SELECT

Table-Valued Functions in FROM

Built-in table-valued functions:

Many databases provide built-in functions that return tables:

GENERATE_SERIES() in PostgreSQL
STRING_SPLIT() in SQL Server
JSON_TABLE() in MySQL/Oracle for parsing JSON
UNNEST() in PostgreSQL for expanding arrays
XMLTABLE() for parsing XML

table_valued_functions.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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- PostgreSQL: GENERATE_SERIES for number sequences
SELECT num
FROM GENERATE_SERIES(1, 10) AS num;
-- Returns: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
 
-- Date series for calendar generation
SELECT date::date AS calendar_date
FROM GENERATE_SERIES(
    '2024-01-01'::date,
    '2024-12-31'::date,
    '1 day'::interval
) AS date;
 
-- SQL Server: STRING_SPLIT for parsing delimited strings
SELECT value AS tag
FROM STRING_SPLIT('red,green,blue,yellow', ',');
-- Returns: red, green, blue, yellow (as separate rows)
 
-- Using TVF in joins
SELECT 
    p.product_id,
    p.product_name,
    t.value AS tag
FROM products AS p
CROSS APPLY STRING_SPLIT(p.tags, ',') AS t;
 
-- PostgreSQL: UNNEST for array expansion
SELECT 
    order_id,
    item
FROM orders
CROSS JOIN LATERAL UNNEST(item_array) AS item;
 
-- MySQL: JSON_TABLE for parsing JSON
SELECT jt.*
FROM orders,
JSON_TABLE(
    order_json,
    '$.items[*]' COLUMNS (
        product_id INT PATH '$.product_id',
        quantity INT PATH '$.quantity',
        price DECIMAL(10,2) PATH '$.price'
    )
) AS jt;
 
-- User-defined table-valued function (SQL Server)
CREATE FUNCTION GetEmployeesByDepartment(@DeptId INT)
RETURNS TABLE
AS
RETURN (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = @DeptId
);
 
-- Using UDF-TVF in FROM
SELECT * FROM GetEmployeesByDepartment(5);
 
-- Joining with UDF-TVF
SELECT 
    d.department_name,
    e.first_name,
    e.salary
FROM departments AS d
CROSS APPLY GetEmployeesByDepartment(d.department_id) AS e;

CROSS APPLY vs LATERAL

The DUAL Table and SELECT Without FROM

In standard SQL, the FROM clause is technically optional when you're selecting only constants or expressions that don't reference table data. However, different databases handle this inconsistently.

The Oracle DUAL table:

FROM-less SELECT Support by Database
Database	FROM Optional?	Workaround
PostgreSQL	Yes	`SELECT 1` works
MySQL	Yes	`SELECT 1` works, DUAL also supported
SQL Server	Yes	`SELECT 1` works
SQLite	Yes	`SELECT 1` works
Oracle	No	Must use `SELECT 1 FROM DUAL`
DB2	No	Must use `SELECT 1 FROM SYSIBM.SYSDUMMY1`

no_from_queries.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
35
36
37
-- PostgreSQL/MySQL/SQL Server: No FROM needed
SELECT 1 + 1;
-- Returns: 2
 
SELECT CURRENT_TIMESTAMP;
-- Returns: current date/time
 
SELECT 'Hello' || ' ' || 'World';
-- Returns: Hello World (PostgreSQL)
 
-- Oracle: DUAL required
SELECT 1 + 1 FROM DUAL;
SELECT SYSDATE FROM DUAL;
SELECT 'Hello' || ' ' || 'World' FROM DUAL;
 
-- MySQL: DUAL optional but supported for Oracle compatibility
SELECT 1 + 1 FROM DUAL;  -- Works
SELECT 1 + 1;            -- Also works
 
-- Practical uses of FROM-less queries:
-- 1. Testing database connectivity
SELECT 1;
 
-- 2. Getting current timestamp
SELECT CURRENT_TIMESTAMP AS now;
 
-- 3. Evaluating expressions
SELECT POWER(2, 10) AS result;  -- 1024
 
-- 4. String manipulation testing
SELECT UPPER('test') AS upper_result;
 
-- 5. Generating single-row results for UNION
SELECT 'Header' AS category, 0 AS sort_order
UNION ALL
SELECT category_name, 1 FROM categories
ORDER BY sort_order, category;

Portable Constant Queries

Summary: The FROM Clause

Let's consolidate the key points:

Key Takeaways

•FROM is processed first — It defines the data universe for all subsequent clauses, despite appearing after SELECT in syntax.
•Table aliases are essential — Use aliases for readability, self-joins (required), and disambiguation in multi-table queries.
•Schema qualification — Use explicit schema names for clarity, cross-schema queries, and portable stored procedures.
•Derived tables — Subqueries in FROM create virtual tables for intermediate calculations, aggregations, and logic encapsulation.
•Views are transparent — Reference views exactly like tables; the database handles query merging and optimization.
•Table-valued functions — Use LATERAL/CROSS APPLY to incorporate row-by-row function results in your FROM clause.
•FROM is optional — For constant expressions, most databases allow SELECT without FROM (Oracle requires DUAL).

What's next:

Page Complete

2 / 5