Joins

Joins

One of the most important features of SQL is the ability to define relationships between multiple tables and draw information from them in terms of these relationships, all within a single command.

With joins we can combine columns from different tables. The connection between tables is established through the WHERE clause

Table and column alias names

The full name of a column of a table actually consists of the table name followed by a dot and then the column name e.g. emp.empno, emp.ename etc

User can omit the table names if one is querying only single table at a time. Even when querying on multiple tables one can still be able to omit the table names provided that all the column names are different.

Types of Joins

There are five types of joins, they are

1. Equi Join ( = )

2. Non Equi Join ( < , > , >=, <=, !=)

3. Outer Join ( + )

4. Self Join

5. Cross Join (Introduced in Orace 9i)

General Syntax :

Select < select list > from <table1>,<table2>,-----, <table N>

Where <table1.column1>=<table2.column2> and ------

Equi Join

When two tables joined together using equality operator, then it is called Equi Join. Table prefixes are utilized to prevent ambiguity and the Where clause specifies the columns being joined

Example :

List the employee numbers, names, department numbers and dept names

• Select empno,ename,emp.deptno,dname from emp,dept

where emp.deptno=dept.deptno;

Here, the deptno column exists in both the tables. To avoid ambiguity, the column name should be qualified with the table name ( or with an alias of table name)

Both the table names need to be specified(emp and dept) the where clause defines the joining condition ie joining the deptno of emp table to the deptno of dept table. Here, it checks for the equality of values in these columns

Using Table Aliases

It can be very tedious to type table names repeatedly. Temporary labels ( or aliases) can be used in the FROM clause. These temporary names are valid only for the current select statement. Table aliases should also be specified in the select clause. Table aliases can be up to 30 characters in length, but the shorter they are the better.

Example : List Employee Numbers, Names, Department Numbers, And Department Names From Emp And Dept Tables Using Aliases

• select e.empno, e.ename, e.deptno, d.dname from emp e, dept d

where e.deptno=d.deptno;

Non Equi Join

Using this we can retrieve data from two or more tables by specifying a condition on the common column with any non-equi join operators (>, <, >=, <=,!=)

Example :

Write a query to display those employees details whose salary of emp1 table >= salary of emp table employees

• select eno,ena,emp1.sal,emp.sal from emp1,emp where emp1.sal>=emp.sal

Outer Join

It is used to retrieve the common data from both tables and all values from the table having outer join operator (+)

There are 2 types of outer joins

1. Left Outer Join
2. Right Outer Join

Left Outer Join

It is used to retrieve all rows from the table having the outer join

operator which is left side to the outer join and common values from another

table

Example :

Display the list of employees working in each department. Display the department information even if no employee belongs to that department

• select empno,ename,sal,emp.deptno,dname,loc from emp, dept where

emp.deptno(+)=dept.deptno

Right Outer Join

It is used to retrieve all rows from the table having the outer join

operator which is right side to the outer join and common values from another

table

Example :

Display the list of employees working in each department. Display the employee information even if no such department belongs to the dept table

• select empno,ename,sal,emp.deptno,dname,loc from emp, dept where

emp.deptno=dept.deptno(+)

if the symbol (+) is placed on the other side of the equation then all the employee details with no corresponding department name and location , will be displayed with NULL values in Dname and Loc columns

Rules to place ( + ) Operator

1. The outer join symbol (+) can not be on both the sides

2. We can not outer join the same table to more than one other table in a single Select statement

3. A condition involving an outer join may not use the IN operator or be linked to another condition by the OR operator

Self Join

To join a table to itself means that each row of the table is combined with itself with every other row of the table. The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.

Example : list out the names of the manager with the employees in the emp table

• Select worker.ename, manager.ename ‘Manager’ from emp worker, emp manager where worker.mgr=manager.empno;

Cross Join (oracle 9i concept)

A cross joins returns what’s known as a Cartesian product. This means that the join combines every row from the left table with every row in the right table. This type of join can be used in situations where it is desired, to select all possible combinations of rows and columns from both tables. This kind of join is usually not preferred as it may run for a long time and produce a huge result set that may not be useful

Syntax

Select <select list > from table1 alias cross join table2 alias

Example :

Display the list of employees working in each department. Display the employee information even if no such department belongs to the dept table and also display the department details even if no employee belongs to that department

• select empno,ename,sal,emp.deptno,dname,loc from emp Cross join dept