SQL join (cross join, inner join, outer join ) :

Join is a binary operation that is used to combine data from two tables. We can combine more than two tables by joining them one after another. In a relational database mainly the relation between two tables is used to join tables, in other words the common values of common attributes are used for joining two tables.

There are three types of join.
  • cross join or Cartesian product
  • inner join
  • outer join

cross join or Cartesian product:

Cross join or Cartesian product takes each and every possible combination from two tables. It means if we have two tables, table1(A, B) with 10 rows and table2(C, D) with 5 rows and we perform a Cartesian product then we will get a resultant data-set with (A, B, C, D) columns and 10*5=50 rows. This is a mathematical concept and we do not use it in practical day to day database operations.

For example:

create table table1 (col1 number(2), col2 number (2) );
create table table2 (col3 number(3), col4 varchar2 (2) );

insert into table1 values (1,17);
insert into table1 values (1,16);
insert into table1 values (1,15);
insert into table1 values (1,14);
insert into table1 values (1,13);

select * from table1;

COL1COL2
117
116
115
114
113

insert into table2 values (1,'Sm');
insert into table2 values (1,'Va');

select * from table2;

COL3COL4
1Sm
1Va

select * from table1 cross join table2;

COL1COL2COL3COL4
1171Sm
1161Sm
1151Sm
1141Sm
1131Sm
1171Va
1161Va
1151Va
1141Va
1131Va

Inner join:

The inner JOIN returns rows from both tables which satisfy the given condition or predicate. This is the most popular join we use in the database joins. The result of the join can be defined as the outcome of first taking the Cartesian product of all rows in the tables and then returning all rows that satisfy the join condition or predicate.

Equi join:

When equality is used as a join predicate then this type of join is called Equi join.

Natural join: 

The natural join is a special case of equijoin. In this type of join, the column or attributes have the same name and domain for the common attribute between two relations. Equality is used for selecting rows from both the tables and also eliminates the duplicate attributes.

Examples(executable in oracle live sql):
Equi join:

select emp.first_name,dep.department_name from hr.employees emp inner join hr.departments dep on emp.department_id=dep.department_id fetch first 3 rows only;

FIRST_NAMEDEPARTMENT_NAME
JenniferAdministration
MichaelMarketing
PatMarketing

Inner join which is not an equi join:

select emp.first_name,dep.department_name,emp.department_id,dep.department_id from hr.employees emp inner join hr.departments dep on emp.department_id<>dep.department_id fetch first 3 rows only;

FIRST_NAMEDEPARTMENT_NAMEDEPARTMENT_IDDEPARTMENT_ID
StevenAdministration9010
StevenMarketing9020
StevenPurchasing9030

Natural join (all common atrributes department_id,manager_id participated injoin ):

select emp.first_name,dep.department_name from hr.employees emp natural join hr.departments dep fetch first 3 rows only;

FIRST_NAMEDEPARTMENT_NAME
PatMarketing
AlexanderPurchasing
ShelliPurchasing

Outer join:

Outer join can combine rows even if there does not exist any matching record in other words the join can retain rows from a table even though there are no matching records that exist in the other table. Depending on which table's rows are retained, Outer join can be subdivided further into left outer joins, right outer joins, and full outer joins.

LEFT Outer Join:

The LEFT JOIN of two tables returns all the rows from the left table if the join-condition does not find any matching row in the right table. Where no matching record found in the table on the right, NULL is returned.

SELECT *

FROM hr.employees emp

LEFT OUTER JOIN hr.departments dep ON emp.Department_id = dep.department_id;

RIGHT Outer join:

RIGHT outer JOIN is the opposite of LEFT JOIN. It returns all the columns from the right side table even if the join-condition does not find any matching row in the left table. Where no matches have been found in the table on the left, NULL is returned.

SELECT *

FROM hr.employees emp

RIGHT OUTER JOIN hr.departments dep ON emp.Department_id = dep.department_id;

Full Outer Join:

In a full outer join, all rows from both tables are included in the result, irrespective of the matching condition.

SELECT *

FROM hr.employees emp

FULL OUTER JOIN hr.departments dep ON emp.Department_id = dep.department_id;