table join(left,right,inner)

Three different types of joins(三种不同方式的连接)
inner joins - Return a row only when the columns in the join contain values that satisfy the join condition.This means that if a row has a null value in one of the columns in the join condition, that row isn't returned.

Outer joins - Can return a row even when one of the columns in the join condition contains a null value.

Self joins - Return rows joined on the same table.

Performing Inner Joins on Multiple Columns Using SQL/92
If your join uses more than one column from the two tables, you provide those columns in your ON clause along with the AND operator.

1 SELECT ...
2 FROM table1 INNER JOIN table2
3 ON table1.column1 = table2.column1
4 AND table1.column2 = table2.column2;

You can further simplify your query though the USING clause, but only

if you're performing an equijoin and the column names are identical.

For example, the following query rewrites the previous example with

the USING clause:

1 SELECT ...
2 FROM table1 INNER JOIN table2
3 USING (column1, column2);

An outer join retrieves a row even when one of the columns in the join

contains a null value.
You perform an outer join by supplying the outer join operator in the

join condition.
The outer join operator is a plus character in parentheses (+).
The outer join operator (+) is on the column that contains the null

value.

  A   left   join   B   的连接的记录条数与A表的记录条数相同 
  A   right   join   B   的连接的记录条数与B表的记录条数相同   
  A   left   join   B   与B   right   join   A 相同

1、将子查询转换为JOIN

1 SELECT * FROM EMP E
2 WHERE E.SAL = (SELECT MAX(SAL) FROM EMP)

2、LEFT JOIN中添加WHERE条件

1 SELECT *
2   FROM DEPT
3   LEFT JOIN EMP
4     ON DEPT.DEPTNO = EMP.DEPTNO
5  WHERE DEPT.DEPTNO IN (SELECT DEPTNO FROM DEPT)

3、左连接(以左边的记录为标准，右边不存在的显示为NULL)

1 SELECT * FROM DEPT LEFT JOIN EMP ON DEPT.DEPTNO = EMP.DEPTNO

4、右连接(以右边的记录为标准，左边不存在的显示为NULL)

1 SELECT * FROM DEPT RIGHT JOIN EMP ON DEPT.DEPTNO = EMP.DEPTNO

5、内连接

1 SELECT * FROM DEPT INNER JOIN EMP ON DEPT.DEPTNO = EMP.DEPTNO

转载于:https://www.cnblogs.com/caroline/archive/2012/02/11/2346887.html