Understanding the Relational Model and SQL Operations: A Comprehensive Guide

In the field of computer science, the relational model—proposed by E.F. Codd in 1970—uses set theory to represent real‑world entities and their relationships as tables composed of rows and columns. A relational database therefore consists of three parts: data structures (tables), relational operations, and integrity constraints.

Relational Model

The data structure is the relational table, which includes base tables, derived tables (query results), and virtual tables (views). Common relational operations are the CRUD commands (Create, Read, Update, Delete) expressed in SQL, where the query operation includes selection, projection, union, intersection, difference, and Cartesian product.

Integrity constraints ensure data correctness: entity integrity (primary keys), referential integrity (foreign keys), and user‑defined constraints such as NOT NULL, UNIQUE, CHECK, and DEFAULT.

SQL as a Set‑Oriented Language

SQL (Structured Query Language) is a declarative, set‑oriented language that lets users specify *what* result they want, leaving the *how* to the database engine. All operations work on sets, and the result of each operation is itself a set (a table).

SELECT

A simple query example:

SELECT employee_id, first_name, last_name, hire_date
FROM employees;

The result can be used as a derived table:

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

In PostgreSQL, functions can appear in the FROM clause, producing a one‑row, one‑column table:

-- PostgreSQL
SELECT *
FROM upper('sql');

SELECT corresponds to the projection operation in relational algebra.

WHERE

The WHERE clause filters rows and represents the selection operation.

ORDER BY

ORDER BY sorts the result set.

GROUP BY

GROUP BY changes the structure of the set by aggregating rows. Example (contains a logical error because first_name cannot appear without being aggregated):

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

The operation produces a new relation where each group becomes a single row.

Set Operations: UNION, INTERSECT, EXCEPT

These operators combine two sets. UNION removes duplicates, UNION ALL retains them. INTERSECT returns the common rows, and EXCEPT (or MINUS) returns rows present in the first set but not in the second.

JOIN Types

JOIN retrieves related data stored in separate tables. Common types include:

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 table (nulls when no match).

Right Outer Join – symmetric to left outer join.

Full Outer Join – combines left and right outer joins, returning all rows from both tables.

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

Other join variants such as SEMI JOIN and ANTI JOIN also exist.

DML (Data Manipulation Language)

DML includes INSERT, UPDATE, and DELETE, which operate on whole tables (relations). Example of creating a table and inserting rows:

CREATE TABLE test(id int);
-- MySQL, SQL Server, etc.
INSERT INTO test(id) VALUES (1),(2),(3);
-- Oracle example using SELECT UNION ALL
INSERT INTO test(id)
SELECT 1 AS id FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL;

VALUES can also be used as a table expression in PostgreSQL and SQL Server:

SELECT *
FROM (VALUES (1),(2),(3)) test(id);

UPDATE and DELETE statements similarly treat the target as a relation, even though we often describe them as affecting a single row.

Overall, the article demonstrates that SQL treats tables as sets, and most operations—whether selection, projection, aggregation, set combination, or join—are fundamentally set‑oriented manipulations of relational data.