Oracle Sql Joins

A join combines two or more tables or views for querying. Table or View names will be specified in the from clause. Any columns from those tables can be specified in select clause.

Join Condition

Condition specified in the where clause to evaluate the joins is called Join Condition. Most of the queries contain Join Conditions that compare two or more columns.

Example :-

Consider the below join query

SQL> select deptname,empname from dept d,emp e where d.deptno = e.deptno;

Here “d.deptno = e.deptno” is the join condition.

For joins having three tables, Oracle joins first two of them based on the join condition and then joins with the third one based on the join condition containing third table. Oracle does this process until all tables are joined.

Cartesian Products

If two or more tables are joining without join condition will result into Cartesian products.

If table A has 2 rows and table B has 4 rows then Cartesian product between A and B will return 8 rows ( 2 multiply by 4 )

Example :-

SQL> select count(*) from dept;

COUNT(*)
———-
5

SQL>

SQL> select count(*) from emp;

COUNT(*)
———-
15

SQL>

SQL> select count(*) from dept e, emp e;

COUNT(*)
———-
75

Note :- So always remember to include a join condition. Generally for large tables cartesian products are expensive.

There are different types of Joins available in Oracle

Equi Joins

Equi join contains an equality operator.

Example :-

SQL> select deptname,empname from dept d,emp e where d.deptno = e.deptno
and e.salary > 10000 ;

Self Joins

Self join used to join the table with the same table.

Example :-

SQL> select e.empno,e.empname as employee,m.empname manager from emp e,emp m
where e.empno = m.manager;

Note that all the tables must be aliased properly.

Inner Joins

An inner join (or simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.

Example :-

SQL> select deptname,empname from dept d,emp e where d.deptno = e.deptno;

Outer Joins

Outer Join is used to join tables with sparse data in one or more table for the columns used in the join condition. Outer join returns all rows which satisfies the join condition plus other records from one table and no records from other table. The column will be nullified for the records has no data.

Outer join can be further classified into left outer join, right outer join and full outer join

Left outer join

Left outer join of tables A and B and returns all rows satisfy the join condition plus all records from A which doesn’t have correspondent records in B.

SQL > select * from dept e,emp e where d.deptno = e.deptno(+);

Right outer join

Right outer join of tables A and B and returns all rows satisfy the join condition plus all records from B which doesn’t have correspondent records in A.

SQL > select * from dept e,emp e where d.deptno(+) = e.deptno;

Left outer join

Left outer join of tables A and B and returns all rows satisfy the join condition plus all records from A which doesn’t have correspondent records in B.

SQL > select * from dept e,emp e where d.deptno = e.deptno(+);

Full Outer Join

If you combine left and right outer join forms a full outer join.

Antijoins

Anti join used to find records from a table which doesn’t have a correspondent records in second table.

Example :-

select * from emp where deptno not in (select deptno from dept where deptno in (1,4));

Conclusion :-

SQL is the integral part of database programming and to write efficient SQLs you need to know how and where to use which joins. Out of the above joins, outer joins are more complex and we will examine it with more examples in another post.