Lecture 2 Joins and sub-queries
Topics • Joins • Simple; Outer • Sub-queries • aliases • IN Operator • NULL values • Saving and running files
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.
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
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 clause:SELECT ename, dname FROM emp, deptWHERE emp.deptno = dept.deptno;
Joins (example2) To find ALLEN’s location enter: SELECT ename, locFROM emp, deptWHERE ename = ‘ALLEN’AND emp.deptno = dept.deptno;
Joins • In general:SELECT 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
Multiple Table Joins • SELECT columnsFROM tab1, tab2, tab3WHERE join-condition1 AND join-condition2;
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;
Outer joins • Return rows from table which have no match in other tableSELECT columnsFROM table1, table2WHERE join-condition1 = join-condition2 (+);
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
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.
Sub-queries • To determine the average salary:SELECT AVG(sal) FROM emp;now who earns more than thisSELECT ename, sal FROM empWHERE sal > (SELECT AVG(sal) FROM emp);
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
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 > (average salary of candidate employee’s department); • You also need a subquery that calculates the average salary of each candidate employee’s department
SELECT AVG(SAL) FROM emp WHERE deptno = (candidate row’s value of DEPTNO) • As the main query considers each candidate row, it must invoke the subquery and ‘tell’ it the employee’s dept number. • The subquery must then compute the average salary for that employee’s dept. • The main query must then compare the employee’s salary to the department’s average salary.
Subqueries • How do we tie the department no in the inner query to the department no in the outer to get that individuals department’s average salary? • The trick is to alias the name of the table emp in the outer query
Sub-queries • SELECT ename, deptno, salFROM emp aliasempWHERE sal > (SELECT AVG(sal) FROM emp WHERE aliasemp.deptno = deptno);
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’
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
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.
NULL values • We can use NULL in SQL commands:SELECT * FROM empWHERE comm IS NOT NULL;INSERT INTO empVALUES (7256, ‘GILES’, ‘CLERK’, 7788, ‘15-AUG-80’, 1100, NULL, 20);
SQLplus • Command filesInstead of typing commands directly in Oracle, they can be placed in a file (using Notepad).
SQLplus • The sequence of commands in a file can be run with the command:START <filename> • This means that complicated sequences of commands (like reports) can be written outside of SQL*Plus and then run
SQLplus • We can save the current SQL command to a file with:SAVE <filename> • To load a file but not run it:GET <filename>
Summary • Joins • Simple; Outer • Sub-queries • aliases • IN Operator • NULL values • Saving and running files