Difference between full join and cross join-oracle sql plsql

 Difference between full join and cross join in sql:-

They behave differently to combine the rows from the two tables.

FULL OUTER JOIN:

It is also called as the full join .It will return all the rows when there is either match in the left table or right table. If there is no match, it will return NULL for the rows of the columns which is lacking the matching rows.

Syntax:

select c.*,d.*  from tableC c FULL OUTER JOIN tableD d on c.id=d.id;

select e.ename,d.dname

from emp e

FULL OUTER JOIN dept d

ON e.deptno=d.deptno;

When you want to retain the unmatched rows i.e operations which is not present in the emp table and having deptno as 40.So,ename is appeared as NULL.

CROSS JOIN:

It returns the cartesian product of both the tables i.e. Every row of the table A is combined with every row of the table B.ON condition is not used due to not match rows.

Syntax:

select c.*.d.* from tableC c CROSS JOIN tableD d;

select e.ename,d.dname

from emp e

CROSS JOIN dept d;

ENAME	DNAME
SMITH	ACCOUNTING
ALLEN	ACCOUNTING
WARD	ACCOUNTING
JONES	ACCOUNTING
MARTIN	ACCOUNTING
BLAKE	ACCOUNTING
CLARK	ACCOUNTING
SCOTT	ACCOUNTING
KING	ACCOUNTING
TURNER	ACCOUNTING
ADAMS	ACCOUNTING
JAMES	ACCOUNTING
FORD	ACCOUNTING
MILLER	ACCOUNTING
SMITH	RESEARCH
ALLEN	RESEARCH
WARD	RESEARCH
JONES	RESEARCH
MARTIN	RESEARCH
BLAKE	RESEARCH
CLARK	RESEARCH
SCOTT	RESEARCH
KING	RESEARCH
TURNER	RESEARCH
ADAMS	RESEARCH
JAMES	RESEARCH
FORD	RESEARCH
MILLER	RESEARCH
SMITH	SALES
ALLEN	SALES
WARD	SALES
JONES	SALES
MARTIN	SALES
BLAKE	SALES
CLARK	SALES
SCOTT	SALES
KING	SALES
TURNER	SALES
ADAMS	SALES
JAMES	SALES
FORD	SALES
MILLER	SALES
SMITH	OPERATIONS
ALLEN	OPERATIONS
WARD	OPERATIONS
JONES	OPERATIONS
MARTIN	OPERATIONS
BLAKE	OPERATIONS
CLARK	OPERATIONS
SCOTT	OPERATIONS
KING	OPERATIONS
TURNER	OPERATIONS
ADAMS	OPERATIONS
JAMES	OPERATIONS
FORD	OPERATIONS
MILLER	OPERATIONS

CROSS JOIN	FULL JOIN
It combines each and every row from one table with another table. Specific condition or relationship is not required to match all the rows of two tables. It will generate possibility all the combination of rows. The number of rows present in the first table are multiplied with the number of rows present in the second table.	It combines rows from the two tables when there is matched on the basis of specified condition and includes unmatched rows from the both tables with NULL values.
It is known as cartesian product or cartesian join.	It is known as full outer join.
ON clause is not present.	ON clause is present. On condition is not satisfied for those rows, it will placed NULL values for unpopulated fields.
It will produce all the combinations of the rows.	It is the combination of left outer join and right outer join. It doesn’t generate all the possible combination like cross join.
Syntax: Select * from table1 CROSS JOIN table2 CROSS JOIN table3;	Syntax: Select * from table1 FULL OUTER JOIN table2 On table1.comm_column_name=table2.comm_column_name;
If two tables A & B are present and B is empty, then A*B=A*0=0	If two tables A & B are present and A& B are empty, then A*B=0*0=0.Otherwise,it will carry the value of the non-empty table.
If two joined tables contain C & D rows, it will produce C*D.	If two joined tables contain C & D rows, it will produce C+D.
Table D contain 20 rows and Table E contain 30 rows,10 rows are matching on specified columns, then result will be 20*30=600	Table D contain 20 rows and Table E contain 30 rows,10 rows are matching on specified columns, then result will be 10+10+20=40
Result set will be greater than equal to full outer join.	Result set will be less than equal to cross join.