WHERE

• cannot use column alias
• for non numerical data and date, ‘value’ side must be enclosed in single quotation marks
• comparision operators:
  • =, >, <, >=, <=, !=
    • of course works on numerical field
    • works on text field too, decided by alphabetical order
    • works on date field too, devided by chronological order
  • BETWEEN AND
  • IN
    • returns records that match a value in a specified list
    • values are seperated by commas
  • LIKE
    • ‘%’ can denote multiple chars
    • ‘_’ can denote one char
• 21-27 logical operators:
  • () -> NOT -> AND > OR
• 29-30 treatment of NULL:
• … WHERE shipdate IS NULL / … WHERE shipdate IS NOT NULL
• … WHERE shipdate = NULL DOES NOT WORK

ORDER BY

• can use column alias
• … ORDER BY colname DESC/ASC
• a column does not need to be SELECT’d to act like criteria
• default sequence ASC:
  • blank > special_char > numeric > uppercase > lowercase > NULL
• … ORDER BY colname NULLS FIRST;
  • will move NULL to first
• secondary sort
  • useful when exact match occurs between 2 or more rows in primary sort
  • … ORDER BY colname1, colname2, …;
  • can use column position number

Joining data

6-9 Cartesian join

• Created when omitting joining condition
• Created when using CROSS JOIN
• {1, 2, 3} x {4, 5, 6} -> {14, 15, 16, 24, …, 36}

10-22 Equality join = Inner join

• Link rows through equivalent data that exists in both tables
• Make sure that there are only a few columns in common that is supposed to be used as tool for joining
• WHERE
  • SELECT … FROM … WHERE …
  • Column qualifier: where the column belongs to
    • Column that exist in both tables must be qualified
  • Can also:
    • use other logical conditions
    • use table aliases
    • join multiple tables, need to chain them together
• JOIN
  • methods:
    • SELECT … FROM table1 NATURAL JOIN table2
      • Auto join 2 tables based on 2 columns with same name
      • Might not actually work because same name might not contain same info
      • cannot use column qualifier on common field
    • SELECT …, col, … FROM table1 JOIN table2 USING (col)
      • can use when tables have 1 or more columns in common
      • cannot use column qualifier on common field
    • SELECT … FROM table1 a JOIN table2 b ON a.col1 = b.col2
      • required when column names are different but want to join
      • most preferred
  • preferred over WHERE becayse WHERE should only be used for searching purpose
• Multiple joins
  • WHERE:
    • Select … FROM … WHERE pair1 AND pair2 AND pair3 …;
  • JOIN:
    SELECT … FROM table1 JOIN table2 ON pair1
    JOIN table3 ON pair2
    JOIN table4 ON pair3
    …;

23-24 Non equality join

• In WHERE clause, use any comparison operator other than =
• In FROM clause, use JOIN … ON with conditions other than =
  —————————————————————————-

25-29 Self join

• SELECT … FROM customers c, customers m WHERE c.id = m.refid
  • gets the info of the customer’s referer, which is in the same table

30, 32-34 Outer join

• SELECT … FROM table1 LEFT/RIGHT JOIN table2
  • LEFT JOIN matches everything in table1 with relavent info in table2, if nothing in table2, matched with NULL
  • RIGHT JOIN matches everything in table2 with relavent info in table1, if nothing in table1, matched with NULL

35-39 Set operators

• SELECT …
• UNION/INTERSECT/MINUS
• SELECT …;