Joins & Sub-queries

1. Joins & Sub-queries

2. Oracle recognizes that you may want data that resides in multiple tables drawn together in some meaningful way. One of the most important features of the SQL is that it allows establishing relationship among multiple tables and helps to retrieve information from multiple tables. With joins, the information from any number of tables can be related. Rows in one table can be joined to rows in another table according to common values existing in corresponding columns, that is, usually primary and foreign key columns.

3. A primary key is used in a table to identify the uniqueness of each row in a table. The table in which the column appears as a primary key is referred to as the parent table, while the column that references the other table in the relationship is often called the child table. The column in the child table relates to the parent table is called a foreign key. A "Join" can be recognized in a SQL SELECT statement if it has more than one table after the FROM keyword.

4. For example: SELECT "list-of-columns"FROM table1, table2[WHERE "search-condition(s)"] When writing the SELECT statement that joins tables, precede the column name with the table name for clarity and to enhance the database access. If the same column appears in more than one table, the column name must be prefixed with the table name. To join n tables, we require a minimum of n-1 join conditions. Therefore to join three tables a minimum of two joins are required, This rule does not apply if your table has a concatenated primary key, in which case more than one column is required to uniquely identify each row. In a join, the table names are listed in FROM clause, separated by comma.

5. Joins can be explained easier by demonstrating what would happen if you worked with one table only, and didn't have the ability to use "joins". This single table database is also sometimes referred to as a "flat table". Let's say you have a one-table database that is used to keep track of all your employees and departments: EMPNO ENAME JOB SAL COMM DEPTNO DNAME LOC  Every time a new row is inserted into the table, all columns will be updated, thus resulting in unnecessary "redundant data".

6. Types of Join There are four types of Joins. These are: ·Equi Join ·Cartesian Join ·Outer Join ·Self Join

7. Equi Join or Inner Join The Inner Join or Equi Join returns all rows from both tables where there is a match. Or in other words, if the query is relating two tables using an equality operator (=), it is an equality join, also known as an inner join or an Equi Join. Syntax: SELECT field1, field2, field3FROM first_table ,second_table WHERE first_table.keyfield = second_table.foreign_keyfield; Example:

8. ·List the employee name with their department names. SQL>SELECT ename, dname FROM Emp,dept where emp.deptno=dept.deptno ;  The Output is: ENAME DNAME

9. We have seen that we have to type the names of the table before the common columns between the tables. Just as a column alias gives a column another name, a table alias gives table another name. Table aliases help to keep SQL code smaller, therefore it uses less memory. We can use table aliases in the FROM clause. These aliases are valid only for the current SELECT statement. Table aliases can also be used in the SELECT clause and can be of maximum 30 characters long. The table aliases should be meaningful. We rewrite the above query as:

10. SQL>Select ename, e.deptno, dname From emp e, dept d Where e.deptno = d.deptno;

11. Cartesian Join When the join condition is omitted, the result is the Cartesian join of two or more tables in which all combinations of rows will be displayed. All the rows of the first table are joined to all rows of the second table. This kind of join tends to generate a large number of rows, as it involves no condition. For example, if the first table has five records and it is joined to the second table, which has three rows, then the result is a table consisting of fifteen records. This join is useful in finding all the possible combination of rows from different tables. This join does not require the tables to have common column between them.

12. Syntax:  Select columnname1, columnname2……… From tablename1, tablename2…;  Example:  SQL>Select ename, dname from emp,dept;

13. Outer Join   While using the equi join we have seen that if there exists certain records in one table which do not have corresponding values in the second, then those rows will not be selected. We can forcefully select such rows by using the outer join symbol (+). The corresponding rows for those columns will have NULL values .The outer join symbol is placed on the side of the join that is deficient in information. This operator has the effect of creating one or more null rows, to which one or more rows from the no deficient table can be joined. For example, to write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer-join operator (+) to all columns of B in the join condition. For all rows in A that have no matching rows in B, the query returns NULL values for the columns in B.

14. Syntax:  Select table1.column, table2.column,………. From table1, table2 Where table1.column (+) = table2.column; Or Select table1.column, table2.column,…………… From table1, table2 Where table1.column = table2.column (+);  In the syntax table1.column = is the condition that joins the tables together. table2.column (+) is the outer join symbol, which can be placed on either side of the where clause condition.

15. Example In the emp table, no record of the employee belongs to the department 40. Therefore, in case of equi join, the row of department 40 from the dept table will not be displayed. In order to include that row in the output Outer join is used. ·Display the list of employees working in each department. Display the department information even if no employee belongs to that department.

16. SQL>SELECT empno, ename, emp.deptno, dname, loc FROM emp, dept WHERE emp.deptno (+) = dept.deptno;

17. 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 column.

18. Rules to Place (+) operator ·The outer join symbol (+) cannot be on both sides. ·We cannot “outer join” the same table to more than one other table in a single SELECT statement. ·A condition involving an outer join may not use the IN operator or be linked to another condition by the OR operator.

19. Self Join The self-join can be seen 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. The syntax of command for joining a table to itself is almost the same as that for joining two different tables. In order to differentiate the column from one another aliases are used, since both the tables have the same name.

21. ¨To list out the names of the manager with the employee record one will have to join EMP itself. SQL>SELECT WORKER. Ename “Ename”, MANAGER.ename “Manager” FROM emp WORKER, emp MANAGER WHERE WORKER.mgr=MANAGER.empno; Where WORKER and MANAGER are two aliases for the EMP table and acts as virtual tables.

24. Nested Queries SQL has ability to nest queries within one another. A subquery is a SELECT statement, which is nested within another SELECT statement. One can build powerful statements out of simple ones by using sub queries. The inner queries generate values, which are tested to tell the outer query. SQL first evaluates the nested query within the where clause. The return value of inner query is then substituted in the condition of the outer query. One can use the subquery with the number of SQL clauses. ·Where clause ·Having clause ·From clause

25. There are certain rules, which are to be followed while writing the nested queries. The nested queries must be enclosed in the parenthesis and it must appear on the right side of the comparison operator. One cannot use the order by clause within the subquery, and if used, it should be used as the last clause with the main select statement. The subquery uses number of comparison operators, which falls into different categories, the single-row comparison operators (>, <,>=, <=, <>, =) and multiple row comparison operators (in, any, all). There are number of advantages of the nested queries. They allow the developer to build powerful commands out of simple ones. Sub queries can be very useful when you want to select rows from a table with a condition, which depends on the data from the table itself.

27. A single–row subquery returns one row from the inner nested query. These type of subqueries uses single-row operators (>, <,>=, <=, <>, =). • Example: • List the employees belonging to the department of MILLER. • In this query, if we analyze closely, we see that we do not know the number of department in which MILLER works. So, first we have to determine the number of department of MILLER and then we can find out the employees working in that department. Let’s, first write two separate queries and then we will combine them.

29. List the name of the employees who do the same job as that of an employee number 7369. SQL>Select ename, job from emp Where job = (select job From emp Where empno = 7369);

30. List the name and job of the employee who do the same job as empno 7369 and gets salary greater than the salary of empno 7876. SQL>Select ename, job from emp Where job = (select job from emp Where empno = 7369) And sal > (select sal from emp Where empno = 7876);

31. Using GROUP functions in a subquery • Aggregate functions produce a single value for any number of rows. One can display data from a main query by using a group function in a subquery to return a single value. • Example: • List the name and salary of the employee who gets salary greater than the minimum salary in the employee table. SQL>Select ename, sal from emp where sal > (select min (sal) From emp);

32. List the employee name and salary of the employee whose salary is greater than the average salary of employees whose hiredate is before 01-jan-81. SQL>Select ename, sal from emp where sal > (select avg (sal) from emp where hiredate < ‘01-jan-81 ‘);

33. Using DISTINCT clause in subquery • Distinct clause is used in some cases to force a subquery to generate a single value. • Example: • List the details of the department whose manager’s empcode is 7698. SQL>Select * from dept From emp Where deptno = (select distinct deptno From emp Where mgr = ‘7698 ‘);

34. Problems with single-row subqueries   One common error with subqueries is more than one row returned for a single-row subquery.  SQL> Select empno, ename From emp Where sal = (select min(sal) from emp Group by deptno); ERROR at line 2: ORA-01427: single-row subquery returns more than one row

35. Multiple-Row queries Subqueries that return more than one row are called multiple row subqueries. We use a multiple-row operator, instead of a single-row operator, with a multiple-row subquery. The multiple-row operator accepts one or more values.

37. Using ANY Operator in Multiple-Row Subqueries •  The ANY operator (and its synonym SOME operator) compares a value to each value returned by a subquery. • Display employees whose salary is less than any clerk and who are not clerks. • The maximum salary that a clerk earns is Rs.1300. The SQL statement displays all the employees who are not clerks but earn less than Rs. 1300. •  Here <ANY: means less than the maximum • >ANY: means more than the minimum • =ANY: is equivalent to IN.

38. SQL> SELECT empno, ename, job FROM emp WHERE sal < ANY(SELECT sal FROM emp WHERE job=’CLERK’) AND job<>’CLERK’;

39. Using ALL Operator in Multiple-Row Subqueries  The ALL operator compares a value to every value returned by a subquery. ·Display employee number and name whose salary is greater than the average salary of all the departments. The highest average salary of a department is Rs.2916.66, so the query returns those employees whose salary is greater than Rs.2916.66.  Here: >ALL: means more than the maximum <ALL: means less than the minimum The NOT operator can be used with IN, ANY and ALL operators.

41. Using HAVING clause in subqueries There can be a HAVING clause within the subquery. The Oracle server executes the subquery and the results are returned into the having clause of the main query. These queries can use their own aggregate functions as long as they do not produce multiple values or use GROUP BY or HAVING themselves. List the job with highest average salary. SQL> Select job, avg (sal) From emp Group by job Having avg (sal) = (select max (avg (sal)) From emp Group by job);

42. Multiple-column Sub-queries  So far we have written single-row subqueries where only one column was compared in the WHERE clause or HAVING clause of the SELECT statement. If you want to compare two or more columns, we must write a compound WHERE clause using logical operators. Multiple-column subqueries enable us to combine duplicate WHERE conditions into a single WHERE clause.  Syntax: SQL>SELECT column, column,……….. FROM table WHERE (column, column, ……..) IN (SELECT column, column,.. FROM table WHERE condition);

44. Correlated Subqueries Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement. Select the highest-paid employee of each department. SQL> SELECT deptno, ename, sal FROM emp e1 WHERE sal = (SELECT MAX(sal) FROM emp WHERE deptno = e1.deptno) ORDER BY deptno;

46. ·To display name of top five highest paid employees from emp table. SQL>SELECT E.ENAME,E.SAL FROM EMP E WHERE 5> (SELECT COUNT(*) FROM EMP WHERE E.SAL<EMP.SAL) ORDER BY SAL DESC;

47. Query to display name of five employees who are getting minimum pay from emp table SQL>SELECT E.ENAME,E.SAL FROM EMP E WHERE 5> (SELECT COUNT(*) FROM EMP WHERE E.SAL>EMP.SAL) ORDER BY SAL DESC

48. Special operators in Sub-queries Some special operators used in subqueries are: ·EXISTS ·ANY ·SOME ·ALL

49. EXISTS Operator • The EXISTS operator is frequently used with correlated subqueries. It tests whether a value is there or not (NOT EXISTS ensure for non-existence of values). If the value exists it returns TRUE, if it does not then it returns FALSE. NOT EXISTS operator is more reliable if the subquery returns any NULL values. • Examples: • List all the employees who have at least one person reporting to them.

50. ·Display the order id, product id and quantity of items in the item table that match both the product id and quantity of an item in order 605. SQL> SELECT ordid, prodid,qty FROM item WHERE (prodid, qty) IN (SELECT prodid, qty FROM item WHERE ordid=605) AND ordid<>605;