What are Oracle Joins (Sql Joins)?



Sometimes you need to display data from multiple tables. In order to perform that you need to link one table to another table.There are various ways in which we can obtain data from Multiple tables.Here I would be focusing on Oracle Joins.This is valid for in general any Sql Joins also

Oracle Joins

A Oracle Joins ( Sql Joins) is a query that combines rows from two or more tables.

Oracle performs a join whenever multiple tables appear in the FROM clause. In order to join two tables, you need to identify the common columns that relate the two tables. In the WHERE clause you define the relationship between the tables listed in the FROM clause.

Some Guidelines for  Oracle Joins ( Sql Joins)

1) when writing the select statement that joins tables, it is good practice to precede the column name with table name for clarity purpose.

2) To join m tables, we need at least m-1 conditions

3) We can use table alias for the tables name if the table name is lengthy. Table looks the code shorter and hence less memory

4)  If you are not using table_name or tables alias while selecting the column and If multiple tables are having same column name, then we should specify the table_name for the column which is common across the tables

Inner Join

The most common operator used to relate two tables is the equality operator (=). This is called an equality join or equijoin. This simple join also known as inner join

Oracle Joins (Sql Joins) -Inner Join

Inner Join Example

Syntax
Select tab1.col,tab2.col
from tab1 ,tab2
where tab1.col=tab2.col

SELECT EMP.EMPNO,EMP.ENAME, DEPT.DEPTNO,DEPT.DNAME
FROM EMP , DEPT
where EMP. DEPTNO= DEPT. DEPTNO;

To execute a join of three or more table, Oracle joins two tables based on the join condition, then joins the result to another table, based on join conditions and repeats until all tables are joined.

Oracle Joins (Sql Joins)-Inner Join example

Oracle Joins (Sql Joins) -Inner join example

Oracle Joins (Sql Joins) -Inner join example

Complex Joins

Have one or more conditions added in the WHERE clause. For example, if you are interested in all department and their locations outside the UK use

SELECT LOCATIONS.LOCATION_ID, CITY, DEPARTMENT_NAME
FROM LOCATIONS, DEPARTMENTS
WHERE LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID
AND COUNTRY_ID != ‘UK’;

Non –equijoin

A non-equijoin is the join condition other than equality operator.

It is an inner join statement that uses an unequal operation (i.e.: <>, >, <, =, BETWEEN, etc.) to match rows from different tables

SELECT e.ename, e.sal, s.grade
FROM  emp e, sal_grade s
WHERE e.sal BETWEEN s.low_sal AND s.high_sal;

Cartesian Products or Cartesian Join or Cross Join

-A Cartesian join (or Cartesian product)  (or Cross Join) occurs when data is selected from two or more tables and join condition is defined or no common relationship is defined in the WHERE clause

-If a join is performed and no join condition is specified, a Cartesian product results. A Cartesian product is a result set that is the product of the two tables’ total rows. If table “P” has 100 rows and table “Q” has 100 rows and a Cartesian product is developed, the resulting result set will have 10000 rows. If a query joins three or more tables, the optimizer may find a way to choose a join order that precludes a Cartesian product, but don’t count on it.

SELECT EMPNO,ENAME, DEPT.DEPTNO,DNAME  FROM EMP   , DEPT ;
or
SELECT EMPNO,ENAME, DEPT.DEPTNO,DNAME  FROM EMP   cross join DEPT ;

Oracle Joins (Sql Joins)- Cross Join example

Outer Joins

The inner joins we have seen, return the matching rows from two or more tables according the join condition. The Outer Join mechanism returns the data from one table even if there is no corresponding row in the joining table.

There are three outer joins

a) Left outer Join or Left Join

b) Right outer Join or Right join

c) Full outer join or full join

Left Outer Join

returns the rows matching from both tables as well as the unmatched rows from the table on the left of the join clause.

Oracle Joins (Sql Joins)-Left Outer Join

The LEFT OUTER JOIN would return the all records from left table and only those records from right table that intersect with right table

Example:
select empno,ename,emp.deptno,dname
from emp
LEFT OUTER JOIN dept
on emp.deptno=dept.deptno;

In  Oracle database ,we can also used  the plus sign syntax for left outer join.In this case plus join is on the right side of the equation.

Example:
select empno,ename,emp.deptno,dname
from emp ,dept where emp.deptno=dept.deptno(+) ;

Oracle Joins (Sql Joins)-Left outer join example

Right Outer Join

returns the rows matching from both tables as well as the unmatched rows from the table on the right of the join clause.

Oracle Joins (Sql Joins)- Right outer join example

Example:
select empno,ename,dept.deptno,dname
from emp
right OUTER JOIN dept
on emp.deptno=dept.deptno;

In  Oracle database ,we can also used  the plus sign syntax for RIGHT OUTER JOIN.In this case right join is indicated when the plus sign is on the left of the equation.

Example:
select empno,ename,dept.deptno,dname
from emp ,dept where emp.deptno(+)=dept.deptno ;

 Oracle Joins (Sql Joins)-Right outer Join example

Full Outer Join

This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met

Oracle Joins (Sql Joins) - full outer Join

select empno,ename,dept.deptno,dname
from emp
full OUTER JOIN dept
on emp.deptno=dept.deptno;

This query will return all rows from both tables. Using Oracle syntax using E.DEPT_ID (+) = D.DEPT_ID (+) is impossible because the (+) sign may reference only one table.  We have to use Union to achieve the same using + sign

select empno,ename,dept.deptno,dname
from emp ,dept where emp.deptno=dept.deptno(+)
union
select empno,ename,dept.deptno,dname
from emp ,dept where emp.deptno(+)=dept.deptno;

Oracle Joins (Sql Joins) - Full outer join example

Some RDBMS does not support the Full outer join clause ,then we can use below to cover that

select empno,ename,dept.deptno,dname
from emp
left OUTER JOIN dept
on emp.deptno=dept.deptno
union
select empno,ename,dept.deptno,dname
from emp
right OUTER JOIN dept
on emp.deptno=dept.deptno;


NATURAL JOIN

The natural join specifies that the join relate all columns with the same names on the two tables.

Basically it compares the common columns of both tables with each other. One should check whether common columns exist in both tables before doing a natural join.

Example

SELECT EMP_ID,EMP_NAME, DEPT_ID,DEPARTMENT_NAME  FROM EMP  NATURAL JOIN DEPT;

Here dept_id is the same column between the tables emp and dept

Natural join can join more than two tables

 




Leave a Reply