Search code examples
sqlprojectionrelational-algebra

What would this projection return?

I'm new to sql, if I run this projection on a table, would it return nothing or an error of this projection is not allowed (syntax)? I put all infos of the table and the projection in this picture below to make it easier to read :)

my table and projection

Solution

  • In relational algebra, your set difference would yield an error because the two sets are not union-compatible:

    For set union and set difference, the two relations involved must be union-compatible—that is, the two relations must have the same set of attributes. from wikipedia

    In SQL, the set difference would work, because union compatibility is understood in a more lenient way, i.e. the tuple degrees and data types must match, but not the names. So this is valid SQL:

    SELECT X FROM A
EXCEPT
SELECT Z FROM A

    The result would be a table with a single column named X

    However, even in SQL, you couldn't project Y from the set difference, so πY is not possible, neither in SQL nor in relational algebra. This would be invalid SQL:

    SELECT Y -- Y is not defined here, only X
FROM (
  SELECT X FROM A
  EXCEPT
  SELECT Z FROM A
)