Predicates & Joins

1. SQL does not have a natural join equivalent. However, natural join can be expressed in terms of a Cartesian product, selection and projection.

2. For the relational algebra expression

   

   we can write in SQL

   

3. The predicates can be more complicated, and can involve
   • Logical connectives and, or and not.
   • Arithmetic expressions on constant or tuple values.

   • The between operator for ranges of values.
   • String matching operators % (any substring) and _

     (underscore, matching any character).

   • Backslash overrides the special meaning of these symbols.

   • The like operator to use string matching.
   • We can use not like for string mismatching.
4. Some examples:
   1. Find name and city of all customers having a loan at the SFU branch.

      

   2. Find account number of accounts with balances between $90,000 and $100,000.

      

   3. Find all customers whose street includes the substring ``Main''.