Joins and sub-queries

1
Lecture 2

• Joins and sub-queries

2
Topics

• Joins
• Simple Outer
• Sub-queries
• aliases
• IN Operator
• NULL values
• Saving and running files

3
Joins

• Joins are how to connect together the tables in a
  relational database
• To join tables we must have a column in each
  table that contains the same information
• Two main types simple, outer.

4
Joins

• In our example tables (emp and dept), each
  contains a column deptno (the name does not have
  to be the same).
• This column contains the dept number for the
  employee in the emp table and the dept table has
  departmental information

5
(No Transcript)
6
(No Transcript)
7
Joins

• To join the two tables to select, say, the
  employee name and the name of their department we
  use a join-condition in the WHERE
  clauseSELECT ename, dname FROM emp,
  deptWHERE emp.deptno dept.deptno

8
(No Transcript)
9
Joins (example2)

• To find ALLENs location enter
• SELECT ename, locFROM emp, deptWHERE ename
  ALLENAND emp.deptno dept.deptno

10
(No Transcript)
11
Joins

• In generalSELECT columnsFROM table1, table2,
  WHERE join-condition
• The join-condition must join all the tables for
  two tables we need a single condition, for three
  we require two conditions etc

12
Multiple Table Joins

• SELECT columnsFROM tab1, tab2,
  tab3WHERE join-condition1 AND
  join-condition2

13
Simple Joins

• If a column appearing in SELECT has the same name
  in both tables, we MUST specify which one we
  requireSELECT ename, dname, dept.deptno
  FROM emp, deptWHERE emp.deptno dept.deptno

14
Outer joins

• Return rows from table which have no match in
  other tableSELECT columnsFROM table1,
  table2WHERE join-condition1
  join-condition2 ()

15
Outer joins

• SELECT dept.deptno, dname, sum(sal)FROM emp,
  deptWHERE emp.deptno () dept.deptnoGROUP
  BY dept.deptno, dnameORDER BY dept.deptno
• Append outer join symbol to table without
  matching rows

16
(No Transcript)
17
Subqueries

• This is when one of the parts of WHERE clause is
  a query itself.
• Consider the following question list the name
  and salary of all employees who earn greater than
  the average salary?
• We need to determine what the average salary is
  before we can ask who earns more than it.

18
Sub-queries

• To determine the average salarySELECT AVG(sal)
  FROM empnow who earns more than thisSELECT
  ename, sal FROM empWHERE sal gt (SELECT AVG(sal)
  FROM emp)

19
(No Transcript)
20
Sub-queries

• The inner query is executed just once, i.e. the
  average salary is found and the outer query
  determines who earns more than the average
  salary.
• Queries can be composed where the sub-query is
  executed once for each row in the outer query

21
Sub-queries

• List employees, department number and salary for
  those employees who earn more than their
  departmental average salary.SELECT ename,
  deptno, sal FROM empWHERE sal gt (average salary
  of candidate employees department)
• You also need a subquery that calculates the
  average salary of each candidate employees
  department

22

• SELECT AVG(SAL)
• FROM emp
• WHERE deptno (candidate rows value of
  DEPTNO)
• As the main query considers each candidate row,
  it must invoke the subquery and tell it the
  employees dept number.
• The subquery must then compute the average salary
  for that employees dept.
• The main query must then compare the employees
  salary to the departments average salary.

23
Subqueries

• How do we tie the department no in the inner
  query to the department no in the outer to get
  that individuals departments average salary?
• The trick is to alias the name of the table emp
  in the outer query

24
Sub-queries

• SELECT ename, deptno, salFROM emp aliasempWHERE
  sal gt (SELECT AVG(sal) FROM emp WHERE
  aliasemp.deptno deptno)

25
(No Transcript)
26
More aliasing

• Example for each manager list their
  staffSELECT manager.ename, worker.enameFROM
  emp manager, emp workerWHERE worker.mgr
  manager.empno
• Manager here does not mean job MANAGER

27
(No Transcript)
28
IN Operator

• Matches any one in list.
• Example list average salary of only CLERKs and
  ANALYSTsSELECT AVG(sal), job FROM empWHERE job
  IN (CLERK, ANALYST)GROUP BY jobORDER BY job

29
(No Transcript)
30
NULL values

• Care must exercise when performing calculations
  that involve NULL (empty) values. A useful
  function is NVL(column, 0)which converts NULL
  values to 0 for the calculation.

31
NULL values

• We can use NULL in SQL commandsSELECT FROM
  empWHERE comm IS NOT NULLINSERT INTO
  empVALUES (7256, GILES, CLERK, 7788,
  15-AUG-80, 1100, NULL, 20)

32
SQLplus

• Command filesInstead of typing commands directly
  in Oracle, they can be placed in a file (using
  Notepad).

33
SQLplus

• The sequence of commands in a file can be run
  with the commandSTART ltfilenamegt
• This means that complicated sequences of commands
  (like reports) can be written outside of SQLPlus
  and then run

34
SQLplus

• We can save the current SQL command to a file
  withSAVE ltfilenamegt
• To load a file but not run itGET ltfilenamegt

35
Summary

• Joins
• Simple Outer
• Sub-queries
• aliases
• IN Operator
• NULL values
• Saving and running files