Relational Operators (in SQL)

It's useful to think about what SELECT does and why it does it, framed in a conceptual way. In most SQL implementations the SELECT statement provides for the relational operations that source, mix, compare, and filter data. These relational operations are typically divided into three categories:

  1. Fundamental operations

    • Restriction
    • Projection
    • Cartesian product
    • Union
    • Difference
    • Rename

  2. Additional operations

    • Intersection
    • Natural join
    • Assign

  3. Extended operations

    • Generalized projection
    • Left outer join
    • Right outer join
    • Full outer join

The fundamental operations are just that: fundamental. They define the basic relational operations, and all of them (with the exception of rename) have their basis in set theory. The additional operations are for convenience, offering a shorthand way of performing frequently used combinations of the fundamental operations. For instance, intersection can be performed by taking the union of two sets and from that removing via difference the results of two further unions that have each had difference applied for one of the two initial sets. Finally, the extended operations add features to the fundamental and additional operations. For example, the generalized projection operation adds arithmetic expressions, aggregates, and grouping features to the fundamental projection operations. The outer joins extend the join operations and allow additional information and/or incomplete information to be retrieved from the database.

In standard ANSI SQL, SELECT can perform every one of these relational operations. These operations map to the original relational operators defined by E. F. Codd in his original work on relational theory (with the exception of DIVIDE).

All of these operations are defined in terms of relations or, as they are commonly called, tables! They take one or more relations as their inputs and produce a relation as their output. This allows operations to be strung together into relational expressions. Relational expressions can therefore be created to arbitrary complexity. For example, the output of a select operation (a relation) can be fed as the input to another select statement, as follows:

select name from (select name, type_id from (select * from foods));

Here, the output of the innermost SELECT is fed to the next SELECT, whose output is in turn fed to the outermost SELECT. It is all a single relational expression. Anyone familiar with piping commands in Linux, Unix, or Windows will appreciate this behavior. The output of any select statement can be used as input to yet another statement.