JOINS and its types like equi join, outer join , inner join & Cartesian join

A JOIN can be recognized in SQL as select statement if it has more than one table and we want to display the combined data from both tables. This join condition is based on primary keys and foreign keys. There must be n-1 join conditions for n joins to tables. If join condition is omitted then the result is Cartesian product.

SYNTAX
SQL>SELECT "list of columns" FROM table1, table2 WHERE "condition";

TYPES OF JOINS
EQUI JOIN: It returns all rows from tables where there is a match. Tables are joined on columns that have the same datatype & size in table. It is also known as equality join or simple join or inner join.

SYNTAX:
SELECT field1,field2 FROM table1,table2 WHERE table1.field=table2.field;

NATURAL JOIN: The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only.

CARTESION JOIN: When the join condition is omitted the result is Cartesian join of two or more tables in which all the combinations of rows will be displayed. All the rows are joined to all rows of the second table.

SYNTAX
SQL>SELECT field1, field2 FROM table1, table2

OUTER JOIN: While using equi join we see that if there exists certain rows in one table which don’t have corresponding values in the second table thn those rows will not be selected. We can forcefully select those rows by outer join. The rows for those columns will have NULL values.

SYNTAX
SELECT table1.col, table2.col FROM table1, table2 WHERE table1.col (+) = table2.col;

EXAMPLE
SQL>SELECT empno, ename, emp.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno;

LEFT OUTER JOIN: In case of Left outer join,all the fields will appear on left side and only those field will appear on right where there is match.

SQL> select emp.name, dep.fname from emp left join dept on emp.id=dept.id;

RIGHT OUTER JOIN: In case of Right outer join,all the fields will appear on right side and only those field will appear on left where there is match.

SQL> select emp.name, dept.fname from emp right join dept on emp .id=dept.id;