SQL 5:

1. JOIN：ON indicates how the two tables (the one after the FROM and the one after the JOIN) relate to each other.

如：ON teams.school_name = players.school_name

.* to return all the columns

2. INNER JOIN: eliminate rows from both tables that do not satisfy the join condition set forth in the ON statement;

In mathematical terms, an inner join is the intersection of the two tables.

The results can only support one column with a given name.

3. OUTER JOIN: in an outer join, unmatched rows in one or both tables can be returned.

There are a few types of outer joins:

LEFT JOIN returns only unmatched rows from the left table;

RIGHT JOIN returns only unmatched rows from the right table;

FULL OUTER JOIN returns unmatched rows from both tables.

4. WHERE OR ON:

在INNER JOIN中，ON......后可以加and(后加限定的内容，相当于JOIN的条件)，与INNER JOIN后加WHERE限定是无差别的（都可以）；

在LEFT JOIN中是不同的，因为在and后先filter，然后再join；在WHERE后，是先join，再filter

5. UNION: allows you to stack one dataset on top of the other, and allows you to write two separate SELECT statements,you can treat them differently before appending.

UNION only appends distinct values;

UNION ALL append all the values from the second table 

UNION ALL < UNION?

6. 用ON做JOIN的连接词，可以加任何conditional statement, 如ON..... AND.....运算或>,<,!=等条件

7. SELF JOIN: join a table to itself

the same table can easily be referenced multiple times using different aliases