Learning guides & operator cheat sheet
Relational algebra reference
Concise syntax for every operator and join variant, conceptual notes on set semantics and NULLs, and examples you can run in the sandbox.
Conceptual guides
Core ideas that show up across many queries—read these when diagnostics mention compatibility, NULLs, or join choice.
Set semantics vs SQL bags
Relational algebra treats relations as sets of tuples. RAT follows that model in the sandbox evaluator, while everyday SQL often keeps duplicate rows unless you add DISTINCT.
What changes with set semantics
Projection, union, intersection, and difference all remove duplicate tuples. Two identical rows collapse to one, and NULL compares equal to NULL when deciding membership.
Contrast with SQL
SELECT without DISTINCT can return bags (multisets). RAT’s algebra path matches SELECT DISTINCT-style results for π, ∪, ∩, and −. When comparing to SQL in the sandbox, use the comparison panel to see both paths.
NULLs and three-valued logic
Predicates in selection and join conditions evaluate to true, false, or unknown. Unknown rows are filtered out unless you test NULL explicitly.
Comparisons with NULL
Any comparison x = NULL (or x < NULL) is unknown, not false. Use IS NULL or IS NOT NULL in σ and join conditions when you mean missing data.
| Predicate on row | Kept by σ? |
|---|---|
| salary > 70000 → true | Yes |
| major_id = NULL → unknown | No |
| major_id IS NULL → true | Yes |
NULL in results
Outer joins pad non-matching sides with NULL. Set operations treat NULL as a normal value when comparing tuple equality.
Union-compatible schemas
Set operators require both operands to have the same number of attributes and compatible types column-by-column (position matters).
Rules
Degree (arity) must match. Attribute types at each position must be compatible—typically identical types, with nullable widening allowed in the engine checks.
Typical fix
Project both sides to the same column names before ∪, ∩, or −: π name ( Employees ) ∪ π name ( Professors ). Renaming attributes helps align labels.
Natural join vs theta join
Natural join equates every pair of identically named attributes. Theta join applies one explicit boolean condition to the cross product.
When names align
Students ⋈ Majors joins on major_id automatically because both relations expose that name.
When they do not
Use Employees ⋈[Employees.dept_id = Departments.dept_id] Departments when you need a predicate that natural join would not infer, or rename columns first.
Outer joins preserve dangling tuples
Inner joins drop non-matching rows. Left, right, and full outer joins keep unmatched rows and fill missing columns with NULL.
Choose the preserved side
⟕ keeps the left relation, ⟖ keeps the right, ⟗ keeps both. Swap operands if you picked the wrong side.
Condition
Bracket syntax ⟕[θ] works like theta join for matching; unmatched rows still appear with NULL padding on the other side.
Renaming and ambiguous attributes
Duplicate attribute names after ⨯ or joins make predicates ambiguous. Rename relations or attributes before referencing columns.
Self-join pattern
ρ E1 ( Employees ) ⨯ ρ E2 ( Employees ) then σ E1.salary > E2.salary ( … ) keeps references clear.
Diagnostics
E_AMBIGUOUS_ATTRIBUTE means the analyzer cannot pick a column. Add ρ aliases or qualify names with relation aliases where supported.
Division as “all required values”
R ÷ S keeps values x in the quotient attributes when every tuple in S appears combined with x somewhere in R.
Mental model
Think “students who enrolled in every course listed in S” or “suppliers who ship every part type in S”. S supplies the pattern; R must cover all pairings.
Edge cases
When S has no rows, the quotient is π_{A−B}(R). When no x covers all of S, the result is empty. Divisor attributes must be a subset of R’s attributes.
Operator cheat sheet
14 operators shown.
Selection (Restrict)
Unicode: σ <predicate> ( R )
ASCII: sigma <predicate> ( R )
When to use it
Filter rows before projecting or joining so intermediate results stay small and readable.
Operands & output schema
- One relation R and a boolean predicate over R’s attributes (literals, comparisons, AND/OR/NOT, IS NULL).
- Same attributes as R; only qualifying tuples remain.
ASCII aliases
sigmasSELECTselectCommon mistakes
- Using = NULL instead of IS NULL. Comparisons with NULL are unknown in three-valued logic, so σ major_id = NULL returns no rows. Write σ major_id IS NULL ( R ) instead.
- Forgetting parentheses. Selection binds to the relation on its right. Wrap nested expressions: π name ( σ salary > 70000 ( Employees ) ).
Projection
Unicode: π <a1, a2, …> ( R )
ASCII: pi a1, a2 ( R )
When to use it
Drop unused columns and collapse duplicate rows after joins or unions.
Operands & output schema
- One relation R and a non-empty list of attribute names that exist on R.
- Only the listed attributes; duplicate tuples are removed (set semantics).
ASCII aliases
pipPROJECTprojectCommon mistakes
- Expecting bag semantics. RAT eliminates duplicate rows after projection. Two identical names collapse to one tuple even if the source relation had duplicates.
- Projecting attributes not on R. Attribute names must exist on the input schema. Join or rename first if the column lives on another relation.
Relation rename
Unicode: ρ Alias ( R )
ASCII: rho Alias ( R )
When to use it
Self-joins and comparisons between two uses of the same base table.
Operands & output schema
- One relation R and a fresh relation alias.
- Same attributes and tuples; relation name becomes Alias.
ASCII aliases
rhorRENAMErenameCommon mistakes
- Skipping rename on self-joins. Joining R to itself without aliases yields duplicate attribute names and ambiguous references in predicates.
Attribute rename
Unicode: ρ[old → new] ( R )
ASCII: rho[old -> new] ( R )
When to use it
Align column names before union or clarify labels after a join.
Operands & output schema
- One relation R and a bracket map old → new for each attribute to rename.
- Same tuples; listed attributes appear under new names.
ASCII aliases
rho_attrrename_attrCommon mistakes
- Arity mismatch in ρ S(a,b) form. When renaming both relation and attributes, the attribute list must match the degree of R exactly.
- Renaming to an existing name. Target attribute names must stay unique within the result schema.
Cartesian Product (Cross Join)
Unicode: R ⨯ S
ASCII: R * S or R cross S
When to use it
Build a wide intermediate relation before σ or ⋈_θ when no shared column names exist.
Operands & output schema
- Two relations R and S (any schemas).
- Concatenation of R’s then S’s attributes; |R| × |S| rows before any selection.
ASCII aliases
cross*xCROSScross_joinCommon mistakes
- Accidental cross product. Using ⨯ when you meant ⋈ multiplies every row pair. Prefer natural or theta join when a predicate links the tables.
- Duplicate attribute names. If R and S share names, the product keeps both copies and later references become ambiguous—rename first.
Natural Join
Unicode: R ⋈ S
ASCII: R join S or R natural_join S
When to use it
Foreign-key style links when column names already match (e.g., dept_id on both sides).
Operands & output schema
- Two relations; equi-join on all attributes with identical names and compatible types.
- Shared columns once, then R-only, then S-only attributes; duplicate join keys removed.
ASCII aliases
joinnatural_join><|><|Common mistakes
- No common attribute names. Natural join matches on name equality only. Use ⋈[condition] or rename columns first.
- Confusing with theta join. Natural join ignores arbitrary predicates; it always equates every shared name.
Theta Join (Conditional Join)
Unicode: R ⋈[θ] S
ASCII: R join[condition] S
When to use it
Join predicates that are not limited to identically named columns.
Operands & output schema
- Two relations and a boolean θ over the combined schema (often R.attr = S.attr).
- All attributes from R and S; only row pairs satisfying θ.
ASCII aliases
theta_joinjoin_onJOINCommon mistakes
- Omitting the bracket condition. Bare ⋈ is natural join syntax in RAT, not theta join. Use ⋈[Employees.dept_id = Departments.dept_id].
Left Outer Join
Unicode: R ⟕[θ] S
ASCII: R left_join[condition] S
When to use it
Keep every left row even when the right side has no match (optional relationships).
Operands & output schema
- Two relations and optional θ (defaults to natural join on common names when omitted).
- All R rows; unmatched S columns padded with NULL.
ASCII aliases
left_joinleft_outer_join|><Common mistakes
- Swapping left and right. ⟕ keeps the left operand. Students ⟕ Majors keeps every student, not every major.
Right Outer Join
Unicode: R ⟖[θ] S
ASCII: R right_join[condition] S
When to use it
Mirror of left outer join when the preserved side should be the right operand.
Operands & output schema
- Two relations and join condition θ.
- All S rows; unmatched R columns padded with NULL.
ASCII aliases
right_joinright_outer_join><|Common mistakes
- Using ⟖ when you meant ⟕. Right outer join preserves S. Flip operands or choose ⟕ to keep the other side.
Full Outer Join
Unicode: R ⟗[θ] S
ASCII: R full_join[condition] S
When to use it
Full coverage when you need unmatched rows from both operands.
Operands & output schema
- Two relations and join condition θ.
- Union of left and right outer join results; NULLs on either non-matching side.
ASCII aliases
full_joinfull_outer_join|><|*Common mistakes
- Expecting inner join row count. Full outer join can return more rows than ⋈ because it keeps non-matching tuples from both sides.
Set Union
Unicode: R ∪ S
ASCII: R union S or R || S
When to use it
Merge compatible lists (e.g., names drawn from two relations).
Operands & output schema
- R and S must be union-compatible: same arity and pairwise compatible attribute types.
- Shared column names from operands; set of distinct tuples from either side.
ASCII aliases
unioncupU||UNIONCommon mistakes
- Mismatched schemas. π name ( Employees ) ∪ π dept_name ( Departments ) fails—project matching columns first.
- Expecting duplicate rows. RAT uses set union: duplicate tuples appear once in the result.
Set Intersection
Unicode: R ∩ S
ASCII: R intersect S
When to use it
Overlap queries (“students in both courses”).
Operands & output schema
- Union-compatible R and S.
- Distinct tuples appearing in both operands.
ASCII aliases
intersectcap^INTERSECTCommon mistakes
- Using ∩ on unrelated schemas. Intersect only after projecting to the same columns and types.
Set Difference
Unicode: R − S
ASCII: R - S or R minus S
When to use it
Anti-join patterns and “rows in A but not B”.
Operands & output schema
- Union-compatible R and S.
- Tuples in R that are not members of S (set difference).
ASCII aliases
minusdiffdifference\EXCEPT-Common mistakes
- Non-commutative order. R − S ≠ S − R. The left operand is the minuend.
Relational Division
Unicode: R ÷ S
ASCII: R div S or R / S
When to use it
“For all” queries—students who took every required course, suppliers who ship every part, etc.
Operands & output schema
- Divisor attributes must be a subset of dividend attributes; quotient schema is A − B where A is R’s attributes and B is S’s.
- Tuples x in π_{A−B}(R) such that every tuple in S appears paired with x in R.
ASCII aliases
dividediv/Common mistakes
- Divisor not a subset of dividend. Every attribute of S must appear on R with compatible types.
- Treating division like arithmetic. R ÷ S is relational “for all”, not numeric division. Empty divisor returns π_{A−B}(R).
Executable examples
Each example loads the matching bundled dataset in the sandbox. Your current expression and schema edits are preserved until you confirm replacing them.