Mastering Set-Based SQL: From Relational Theory to Practical Queries

The relational model, proposed by E.F. Codd in 1970, represents entities and their relationships as tables (rows and columns), essentially treating a table as a set of rows.

The model consists of three parts: data structures, relational operations, and integrity constraints.

Data structures are relational tables, including base tables, derived tables (query results), and virtual tables (views).

Common relational operations are CRUD, implemented via SQL; queries include selection, projection, union, intersection, exception, and Cartesian product.

Integrity constraints enforce entity integrity (primary keys), referential integrity (foreign keys), and user-defined constraints (NOT NULL, UNIQUE, CHECK, DEFAULT).

Today's focus is the relational operation language, SQL, using example data from a CSDN article.

Set‑Oriented SQL

SQL is a set‑based language that lets users describe the desired result (what) while the DBMS determines how to obtain it (how). The result of every query is a relation (a set of rows).

In relational databases, the terms relation, table, and set are often interchangeable.

SELECT

A simple query:

SELECT employee_id, first_name, last_name, hire_date
FROM employees;

The result is a table, which can be used as a derived table:

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

Derived tables can be nested further.

PostgreSQL example using a function in the FROM clause:

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

The upper() function returns a one‑row, one‑column table.

The SELECT clause corresponds to the projection operation. The diagram below illustrates this concept.

Other common clauses:

WHERE performs selection (filtering):

ORDER BY sorts the result set:

GROUP BY

Grouping changes the structure of the relation. Example:

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

This query is invalid because first_name is not aggregated or grouped.

Despite the error, GROUP BY always produces a set.

UNION, INTERSECT, EXCEPT

Set operations combine two result sets. The two sets must have the same number of columns and compatible data types.

UNION removes duplicates; UNION ALL retains them.

INTERSECT returns the common rows, without duplicates.

EXCEPT (or MINUS) returns rows present in the first set but not in the second, without duplicates.

The DISTINCT keyword removes duplicate rows, effectively turning a multiset into a set.

SQL’s set concepts stem from mathematical set theory, but SQL treats sets as multisets (allowing duplicate rows). ORDER BY can impose an order on the result.

JOIN

Joins combine related rows from two tables. Types include inner join, left/right/full outer join, and cross join (Cartesian product).

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 (NULL when no match):

Right Outer Join is symmetric to left outer join:

t1 RIGHT JOIN t2
t2 LEFT JOIN t1

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

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

Other join variants include SEMI JOIN and ANTI JOIN.

Set operations can often be expressed with joins, e.g.:

SELECT department_id
FROM departments
UNION
SELECT department_id
FROM employees;

Equivalent using a full outer join:

SELECT COALESCE(d.department_id, e.department_id)
FROM departments d
FULL JOIN employees e ON (e.department_id = d.department_id);

DML

Data Manipulation Language includes INSERT, UPDATE, and DELETE. 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 FROM DUAL and UNION ALL
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 also defines a relation, as shown in PostgreSQL and SQL Server:

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

UPDATE and DELETE statements similarly operate on whole relations, even though we often describe them as affecting individual rows.