Joins and SubQueries – Classroom notes 5

yawn

Joins

The specification of table aliases in the from clause is necessary to refer to columns that have the same name in different tables. For example, the column DEPTNO occurs in both EMP and
DEPT. If we want to refer to either of these columns in the where or select clause, a table
alias has to be specified and put in the front of the column name. Instead of a table alias also
the complete relation name can be put in front of the column such as DEPT.DEPTNO, but this
sometimes can lead to rather lengthy query formulations.

select * from emp

EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
---------------------- ------------------------------ ---------- ---------------------- -------------------- ---------------------- ----------------------
100 pandian clerk 1000 1/3/2005 0:0:0 800 20
101 muruga writer 1002 12/5/2004 0:0:0 1200 20
102 bala security 1003 7/10/2006 0:0:0 800 10

select * from dept
DEPTNO DNAME LOC
---------------------- --------------- ---------------
20 services singapore
10 delivery india

select * from salgrade
GRADE LOSAL HISAL
---------------------- ---------------------- ----------------------
1 500 800
2 801 1000
3 1001 1500

Comparisons in the where clause are used to combine rows from the tables listed in the from
clause.

In the table EMP only the numbers of the departments are stored, not their
name. For each salesman, we now want to retrieve the name as well as the
number and the name of the department where he is working:

select emp.empno, emp.ename, dept.dname
from emp, dept
where emp.deptno=dept.deptno
EMPNO ENAME DNAME
---------------------- ------------------------------ ---------------
102 bala delivery
100 pandian services
101 muruga services

it can also be written as

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

E and D are table aliases for EMP and DEPT, respectively. The computation of the query result occurs in the following manner (without optimization):
1. Each row from the table EMP is combined with each row from the table DEPT (this operation
is called Cartesian product). If EMP contains m rows and DEPT contains n rows, we
thus get n x m rows.
2. From these rows those that have the same department number are selected (where
E.DEPTNO = D.DEPTNO
).

In this example the joining condition for the two tables is based on the equality operator “=”.
The columns compared by this operator are called join columns and the join operation is called
an equijoin.

yawn

Subqueries
———–
A query result can also be used in a condition of a where clause. In such a case the query is called a
subquery and the complete select statement is called a nested query.

A respective condition in the where clause then can have one of the following forms:
1. Set-valued subqueries
<expression> [not] in (<subquery>)
<expression> <comparison operator> [any|all] (<subquery>)
An <expression> can either be a column or a computed value.
2. Test for (non)existence
[not] exists (<subquery>)

List all employees who are working in a department located in Singapore

select empno, ename from emp where deptno in (select deptno from dept where loc='singapore')
EMPNO ENAME
---------------------- ------------------------------
100 pandian
101 muruga

The subquery retrieves the set of those deptnos whose location is Singapore. If the employee working in the selected set (in operator), this tuple belongs to the query result set.

Conditions of the form <expression> <comparison operator> [any|all] <subquery> are used
to compare a given <expression> with each value selected by <subquery>.

Retrieve all employees who are working in department 20 and who earn at
least as much as any (i.e., at least one) employee working in department 10:

select empno, ename, sal from emp where sal>=all (select sal from emp where deptno=10) and DEPTNO = 20;
EMPNO ENAME SAL
---------------------- ------------------------------ ----------------------
100 pandian 800
101 muruga 1200
102 bala 800

yawn

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s