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.



OUTER JOIN - does not require each record in the joined tables to have a matching record

1. LEFT [OUTER] JOIN
2. RIGHT [OUTER] JOIN
3. FULL OUTER JOIN

• Left join

select * from emp left join dept on emp.deptno=dept.deptno
includes all rows from the left table emp even though there are no defined departments for

• Right join

select * from emp right join dept on emp.deptno=dept.deptno
includes all rows from the right table dept even though there are no defined employees for


NATURAL JOIN - joins tables with columns which have the same names
select * from emp natural join dept
The result is equivalent to select * from emp, dept where emp.deptno=dept.deptno (defined implicitly)


CROSS JOIN - returns cartesian product (декартово произведение)
select * from emp cross join dept
The result is the same as - select * from emp, dept