SQL revise (part 2)

Union/Union all

The UNION operator returns results from both queries after eliminating duplications.

The UNION ALL operator returns results from both queries, including all duplications.

The INTERSECT operator returns rows that are common to both queries.

The MINUS operator returns rows in the first query that are not present in the second query.

•

SQL revise

INNER JOIN - find pair of rows of tables satisfied to join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

1.  explicit
2.  implicit

• Explicit

select * from emp inner join dept on (emp.deptno=dept.deptno)

• Impicit

select * from emp, dept where emp.deptno=dept.deptno
The result of both queries is the same.