Relational Algebra, Joins, Set Operations and Table Copying

1. Relational Algebra

Relational algebra is a formal collection of operations that take relations as input and produce relations as output. Because results are relations, operations can be composed into larger expressions.

Classical relational algebra uses set semantics: duplicate tuples are absent and tuple order is irrelevant. SQL adopts many relational ideas but usually works with duplicate-preserving results unless DISTINCT or a set operation removes duplicates. SQL also includes NULL and three-valued logic.

Relational algebra helps explain what a query means independently of a particular execution plan.

2. Selection

Selection chooses tuples satisfying a predicate. It is commonly written with the Greek sigma:

σ status = 'ACTIVE' (EMPLOYEE)

The SQL counterpart is WHERE:

SELECT *
FROM employee
WHERE status = 'ACTIVE';

Selection changes the number of rows but retains the relation's attributes. Several conditions may be combined with logical operators. In SQL, NULL can make a predicate UNKNOWN, which WHERE removes along with FALSE.

3. Projection

Projection chooses attributes and is commonly written with pi:

π department_id, employee_name (EMPLOYEE)

Its SQL counterpart is the select list:

SELECT department_id, employee_name
FROM employee;

Classical projection removes duplicate tuples because relations are sets. SQL SELECT normally preserves duplicates, so SELECT DISTINCT is closer when set projection semantics are required.

Projection should include the identifiers needed to distinguish results. Selecting only a nonunique label may intentionally collapse identities under DISTINCT.

4. Rename

Rename gives a relation or its attributes another name. It is essential when the same relation appears more than once.

SQL aliases perform this role:

SELECT e.employee_name, m.employee_name AS manager_name
FROM employee AS e
LEFT JOIN employee AS m
  ON m.employee_id = e.manager_id;

Aliases create separate references to the same table. They do not copy data.

5. Cartesian Product

The Cartesian product pairs every row of one relation with every row of another. If A has m rows and B has n rows, A × B contains m × n pairs.

SELECT *
FROM department
CROSS JOIN location;

Four departments and three locations produce twelve combinations. A cross product is correct when every combination is required, such as constructing a scheduling grid.

An accidental missing join condition can create a huge cross product and duplicate facts throughout a query.

6. Join as Product and Selection

A theta join combines rows that satisfy a general condition. Conceptually it is a Cartesian product followed by selection.

An equijoin uses equality:

SELECT e.employee_name, d.department_name
FROM employee AS e
INNER JOIN department AS d
  ON d.department_id = e.department_id;

The join condition relates keys with the same meaning. Equal data types alone do not make columns valid join partners.

The result grain depends on relationship cardinality. If a department has many employees, one department row contributes to several joined rows.

7. Inner Joins

INNER JOIN returns only matching row combinations. Rows on either side without a match are absent.

SELECT o.order_id, l.product_id, l.quantity
FROM order_header AS o
JOIN order_line AS l
  ON l.order_id = o.order_id;

JOIN without a modifier ordinarily means INNER JOIN. Several tables can be joined in sequence, but every join should have a clear relationship predicate and alias.

The older comma syntax places tables in FROM and join predicates in WHERE. Explicit JOIN separates relationship conditions from filtering and reduces accidental products.

8. Composite-Key Joins

When identity uses multiple columns, join on the complete key:

SELECT *
FROM shipment_item AS s
JOIN order_line AS l
  ON l.order_id = s.order_id
 AND l.line_number = s.line_number;

Joining only on order_id pairs each shipment item with every line of the order and creates false combinations. Foreign-key definitions and join conditions should align.

9. Outer Joins

An outer join retains unmatched rows from a chosen side and supplies NULL for missing columns.

A left outer join preserves all left rows:

SELECT d.department_id,
       d.department_name,
       e.employee_name
FROM department AS d
LEFT JOIN employee AS e
  ON e.department_id = d.department_id;

A department with no employee still appears once with NULL employee columns.

A right join preserves all right rows. It can usually be rewritten as a left join by swapping table order. A full outer join preserves both sides, though MySQL does not provide a direct FULL OUTER JOIN keyword and requires an intentional combination of operations.

10. ON Versus WHERE in Outer Joins

For an inner join, moving a related filter between ON and WHERE often yields the same rows. For an outer join it can change meaning.

FROM department AS d
LEFT JOIN employee AS e
  ON e.department_id = d.department_id
 AND e.status = 'ACTIVE'

This preserves every department and matches only active employees.

If e.status = 'ACTIVE' is placed in WHERE, unmatched rows have NULL status and the predicate becomes UNKNOWN. They are removed, making the result behave like an inner join for that condition.

Use ON for restrictions on which right rows qualify as matches when unmatched left rows must remain. Use WHERE to filter the result after preservation.

11. Self-Joins

A self-join relates rows in one table to other rows of the same table:

SELECT e.employee_name,
       m.employee_name AS manager_name
FROM employee AS e
LEFT JOIN employee AS m
  ON m.employee_id = e.manager_id;

Aliases distinguish the employee role from the manager role. The left join retains top-level employees whose manager_id is NULL.

Self-joins also support predecessor relationships, category hierarchies and pair comparisons. Recursive queries are more appropriate for unknown hierarchy depth.

12. Natural Joins

NATURAL JOIN automatically equates columns having the same names in both inputs and usually emits one copy of each matched column.

This convenience is fragile. Adding an unrelated same-named column later silently changes the join condition. Columns with different names but the same meaning are not joined.

Production queries should state relationships explicitly with ON or, when exactly appropriate, USING:

JOIN department USING (department_id)

Explicit joins make review and schema evolution safer.

13. Semi-Joins and Anti-Joins

A semi-join returns left rows for which at least one right match exists, without returning or multiplying by right columns. SQL normally expresses it with EXISTS:

SELECT c.customer_id, c.customer_name
FROM customer AS c
WHERE EXISTS (
    SELECT 1
    FROM order_header AS o
    WHERE o.customer_id = c.customer_id
);

An anti-join returns left rows with no match and uses NOT EXISTS. These forms state existence directly and avoid duplicates that a join plus DISTINCT could create.

14. Union

UNION combines compatible query results and removes duplicate rows:

SELECT email FROM customer
UNION
SELECT email FROM supplier;

Each operand must return the same number of columns in corresponding positions with compatible types. Output column names ordinarily come from the first query.

Duplicate removal requires additional work. Use UNION ALL when duplicates are meaningful or the inputs are known to be disjoint.

15. UNION ALL

UNION ALL concatenates compatible results without duplicate elimination:

SELECT event_time, 'ORDER' AS source
FROM order_event
UNION ALL
SELECT event_time, 'PAYMENT'
FROM payment_event;

If the same row appears in both operands, both copies remain. This often matches event streams and is usually cheaper than UNION.

ORDER BY for the combined result belongs after the final operand. Parenthesized subqueries are needed when individual operand ordering or limits have a specific purpose.

16. Intersection and Difference

Intersection returns rows present in both compatible results. Set difference returns left rows absent from the right.

In SQL products supporting them, the keywords are INTERSECT and EXCEPT; some products use MINUS for difference. Availability varies by MySQL version, so equivalent logic often uses EXISTS or NOT EXISTS.

SELECT c.customer_id
FROM customer AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM order_header AS o
    WHERE o.customer_id = c.customer_id
);

NOT EXISTS handles nullable comparison columns more predictably than NOT IN.

17. Duplicate Semantics

Relational set operations remove duplicates by definition, while SQL distinguishes duplicate-eliminating and ALL variants.

Join multiplicity is not accidental duplication when several relationships truly exist. One order joined to four lines should produce four rows at line grain.

Before adding DISTINCT, state what one result row represents and which columns define identity. DISTINCT can conceal a missing join condition without repairing the query's meaning.

18. Join Cardinality Reasoning

Use declared keys to predict cardinality:

• many-to-one: each valid child matches at most one parent;
• one-to-many: each parent can yield several child combinations;
• one-to-one: each side matches at most one;
• many-to-many: both sides can multiply through an associative relation.

Compare expected and actual row counts at intermediate stages. Unexpected multiplication often indicates an incomplete key condition or a join performed at the wrong grain.

19. Copying a Table Definition

MySQL can create a new table similar to an existing one:

CREATE TABLE archived_order LIKE order_header;

CREATE TABLE ... LIKE copies much of the table definition, including columns and indexes, subject to documented limitations. It does not copy rows:

INSERT INTO archived_order
SELECT *
FROM order_header
WHERE ordered_at < '2025-01-01';

Name columns explicitly when schemas may evolve or column order is not guaranteed to remain aligned.

20. CREATE TABLE AS SELECT

CREATE TABLE high_value_order AS
SELECT order_id, customer_id, total_amount
FROM order_header
WHERE total_amount >= 10000;

CREATE TABLE ... AS SELECT creates a table from the query's result columns and copies result rows. It does not automatically reproduce every primary key, foreign key, index, default, generated property or constraint from source tables.

Inspect and add required schema metadata after creation. A query-result copy is a new physical snapshot, not a synchronized view.

21. INSERT SELECT

INSERT ... SELECT copies query results into an existing compatible table:

INSERT INTO order_archive
    (order_id, customer_id, ordered_at, total_amount)
SELECT order_id, customer_id, ordered_at, total_amount
FROM order_header
WHERE ordered_at < '2025-01-01';

The target's constraints, defaults, generated columns and conversions apply. The operation does not automatically delete source rows. An archival move needs a transaction and carefully ordered deletion where engine and design permit.

22. AUTO_INCREMENT

MySQL AUTO_INCREMENT generates numeric surrogate identifiers:

CREATE TABLE ticket (
    ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL
);

Omitting ticket_id requests generation. LAST_INSERT_ID() can report the generated value for the current connection under its defined semantics.

Generated numbers can have gaps and should not encode row count or legal document sequencing. They provide identity, not consecutive business numbering.

When copying rows, decide whether to preserve existing identifiers or generate new ones. New identifiers require remapping every related foreign key.

23. Updating and Deleting Through Joins

MySQL supports multi-table or joined update and delete forms. Their power increases risk because join multiplication and predicates determine affected rows.

First write a SELECT returning target primary keys. Verify cardinality, duplicate target matches and outer-join meaning. Then apply the modification in a transaction when supported and inspect affected rows.

Never assume LIMIT or observed display order identifies “the first” matching row without a deterministic order and supported statement semantics.

24. Building Correct Multi-Relation Queries

Begin by stating the desired result grain. List the tables needed and identify the key relationship for every join. Use explicit JOIN syntax and complete composite keys. Decide whether unmatched rows must survive before choosing inner or outer join.

Place match restrictions in ON and final result restrictions in WHERE according to intended outer-join behavior. For set operations, align column counts, meanings and compatible types. Use UNION ALL unless duplicate removal is required.

Finally, inspect table-copy results and re-create every required constraint and index. Relational operations are composable, but correctness depends on preserving identity and understanding how each stage changes row cardinality.