Why SQL Treats Everything as a Relation: A Beginner’s Guide to the Relational Model

Relational Model Overview

The relational model, proposed by E.F. Codd in 1970, is based on set theory; real‑world entities and their relationships are represented as tables (rows and columns), which are essentially collections of tuples.

It consists of three parts: data structures (tables, derived tables, and views), relational operations (CRUD), and integrity constraints (primary key, foreign key, NOT NULL, UNIQUE, CHECK, DEFAULT).

SQL as a Set‑Oriented Language

SQL (Structured Query Language) is designed to be close to natural English, allowing users to specify *what* result they want while the DBMS decides *how* to compute it. All operations treat tables as sets, and query results are also tables.

Core SQL Clauses

SELECT retrieves columns; it can be used with derived tables:

SELECT employee_id, first_name, last_name, hire_date FROM employees;

SELECT * FROM (SELECT employee_id, first_name, last_name, hire_date FROM employees) t;

In PostgreSQL, functions can appear in the FROM clause, e.g.: SELECT * FROM upper('sql'); The WHERE clause implements selection, ORDER BY sorts results, and GROUP BY aggregates rows, changing the relation’s structure. An example with a syntax error demonstrates that non‑aggregated columns cannot appear in the SELECT list when grouping.

SELECT department_id, count(*), first_name FROM employees GROUP BY department_id;

The correct use would omit first_name or apply an aggregate function.

Set Operations (UNION, INTERSECT, EXCEPT)

SQL provides set operators that combine result sets:

UNION returns distinct rows from both queries.

UNION ALL retains duplicates.

INTERSECT returns rows common to both queries.

EXCEPT (or MINUS) returns rows in the first query that are not in the second.

All operators require the same number of columns with compatible data types.

SELECT department_id FROM departments UNION SELECT department_id FROM employees;

JOIN Types

Joins combine related rows from two tables:

Inner Join returns rows that satisfy the join condition.

Left Outer Join returns all rows from the left table and matching rows from the right, filling with NULLs when no match exists.

Right Outer Join is the mirror of left outer join.

Full Outer Join returns all rows from both tables, using NULLs for non‑matching sides.

Cross Join (Cartesian product) pairs every row of one table with every row of the other.

Other specialized joins such as SEMI JOIN and ANTI JOIN also exist.

Data Manipulation Language (DML)

DML covers INSERT, UPDATE, and DELETE, all operating on whole tables (relations). Example statements:

CREATE TABLE test(id int);

INSERT INTO test(id) VALUES (1),(2),(3);

INSERT INTO test(id) SELECT 1 AS id FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL SELECT 3 FROM DUAL;

The VALUES clause can be used as a table expression in many databases: SELECT * FROM (VALUES (1),(2),(3)) test(id); UPDATE and DELETE similarly affect entire tables, though they are often described as modifying or removing rows.

Illustrative Images