Joining Tables and Subqueries

Data is stored in more than one table for the purpose of organising it in a better manner. For retrieving data from multiple tables, they have to be joined. Once we retrieve data from one or more tables based on same condition, then it is called a query. If the query is used as a part of another query then the former becomes the subquery.

Multi Table Queries

You don't need to manage a single table. You might as well buy yourself an inexpensive, flat file manager and run it on a basic clone computer, and you've saved yourself a lot of money. However, many times, one needs to use quires which request data from two or more tables in the database. For example, if you want to get a list of the sales people in the marketing division and their location, you need to query both the ENT and DEPT table

The process of forming rows from two or more tables by comparing the contents or related columns is called joining tables. The resulting table is called a JOIN between the tables. The different types of joins that can be made between tables are as follows:

Equi Join: A join that is formed as a result of an exact match between two columns an equi join or a simple join.

Non Equi Join: A join that is formed based on the comparison operators(except for the equality operator) is called a non equi join.

Self join : a join that relates a table to itself is called a self join

Joins

joins are the foundation of multi table query processing in SQL. All of the data in a relational database is stored in its columns as explicit data values, so that all possible relationships between tables can be formed by matching the contents or related columns. joins thus provide a powerful facility for exercising the data relationships in a database. Equi Joins A join based on an exact match between two columns is more precisely is called an equi join. This is because the comparison operator in the join condition is = (equals). joins can also be based on other kinds of comparison operators.

For example, if we want to know the location of any of the personnel, then we have to make a join between the DEPT and EMP tables. Suppose we want to find the location of the offices where Mr. Sudhakar works, then enter:

SQL >SELECT ENAME, JOB
2 FROM EMP, DEPTS
3 WHERE
4 ENAME ='SUDHAKAR'AND EMRDEPTNO
DEPTS.DEPTNO;

Using Aliases

When using a join, it is a good idea to identify all columns by using their table names. However, typing long table names in command statements that span over several lines can be bothersome sometimes. To overcome this problem, you can define short name also for tables, just as we had done for columns. For example if you want to list information about employees who work in Andheri enter.

SQL > SELECT D. ,ENAMEJOB
2 FROM EMP, P DEPTS D
WHERE P. DEPTNO D. DEPTNO
AND D.SITE='ANDHERI'
ORDER BY JOB;

In the above query, line 2 is the key to the command statement. We have given the letter P as an alias for table ENT, and the letter d for the table DEPTS. Notice the use of D*. in the column names to retrieve all the columns of the DEPTS table.

Non Equal Joins

The joins which employ comparison operators other than (equals) are called non equi joins. Consider the following table, GRADES, which gives the grade division of the employee of the organization based on the salaries.

Suppose now, you want to find the salary grade of each employee, then we have to join the EMP GRADES tables:

SQL > SELECT GRADE,ENAMEJOB,PAY
2 FROM EMP, GRADES
3 WHERE PAY BETWEEN LOW AND HIGN
4 ORDER BY GRADE, JOB;

In the above query, a BETWEEN ... AND comparison has been used for the join. The PAY field in each raw of the EMT table is compared to the LOW and HIGH values in each row of the table GRADES. If the value falls between them, then the rows are joined.

Self joins

Some multi table queries involve a relationship that a table has with itself Towards this end, we make use of table aliases to join a table to itself as though it were two separate tables. This is useful if you want to Join one row in a table to another row in the same table.

For example, suppose you want to find out the names, jobs and salaries of those employees in the organization who are paid the same or more than employee RAMAKRISHNAN, then the query would be as:

SQL > SELECT X.ENAME, X..PAY, X.JOB,
Y.ENAME, Y.PAY
2 FROM EMP X EMP Y
3 WHERE X.RAY > = Y.PAY
4 AND Y.ENAME ='RAMAKRISHNAN'

In the above query, the EMP tables has been joined itself using the comparison operator > =. The query treats EMP as if it were two separate tables, X any Y. Each row in X is appended to RAMAKRISHNAN's row in Y if the pay is greater than or equal to RAMAKRISHNAN's.

Outer joins

The SQL join operation combined information from two tables by forming pairs of related rows from the two tables. The row pairs that make up the joined table are those where the matching columns in each of the two tables have the same value. If one of the rows of a table is unmatched in this process, the join can produce unexpected results.

For example, suppose the DEPT table has an entry for the FIN Department. There are no corresponding entries for the same in the EMP table. If we join the tables, then the entries corresponding to the FIN division will not be shown as we have seen in the examples so far. If now, we want to display the FIN Department rows, we have to use the outer join operator, which is a plus sign enclosed in (+).

To join EMP and DEPT and list departments 4 and 5 (i.e., HRD, FIN), with or without employees, enter:

SQL > SELECT D.DEPTNODNAME,EMPNOENMAE
2 FROM DEPTS D, EMP P
3 WHERE D > DEPTNO = P.DEPTNO(+)
4 AND D.DEPTNO IN (4,5)
ORDER BY D.DEPTNO;

In the above query, the outer join symbol has been used after P.DEPTN0 in the WHERE clause. This symbol tells SQL*Plus to treat the EMT table as though it contained an extra row containing a null value in every column. SQL*Plus joins this null row of EMT to any row of DEPT that cannot be joined to real row of EMT.

The plus says, effectively, "if a row exists in the DEPT table that doesn't correspond to arrow in the EMT table, add one to make the match"

What is Subquery?

The SQL subquery feature lets you use the results of one query as part of another query. Putting it simply, a subquery is a query that is used in a clause of another SQL or SQL*Plus command.

The subquery feature is less well known than SQL's join feature, but it plays an important role in SQL for three reasons:

1. An SQL statement with a subquery is often the most natural way to express a query, because it most closely parallels the English language description of the query.

2. Subqueries make it easier to write SELECT statements, because they let you break a query "down into pieces" and then" put the pieces together". There are some queries that cannot be expressed in SQL without using a subquery.

When to use Subqueries?

Subqueries are used in the WHERE clause of another SQL statement (the main query, also called outer query). Subqueries provide an efficient, natural way to handle query requests that are themselves expressed in terms of the results of other queries. If you have a query Re. "Find all the employees who have the same job as Kannan." Normally you would find the result by using two queries: one to fine Kannan's job and another to find all other and another to fine all others who have the same job.

SQL > SELECT JOB FROM EMP WHERE
ENAME 'Kannan'; JOB ANALYST
SQL > SELECT ENAME, JOB FROM EMP
2 WHERE JOB ='ANALYST;
ENAME JOB
ARUN ANALYST
Kannan ANALYST
You can get the same result by using a subquery in the WHERE clause as

SQL > SELECT ENAME, JOB FROM EMP
2 WHERE JOB=
3 (SELECT JOB FROM EMP
4 WHERE ENAME = 'Kannan');

The above example shows the most basic use of subquery. In addition to the above, SQL offers these subquery search conditions.

Subquery comparison test. Compares the value of a expression to value produced by a subquery This test resembles the simple comparison test we saw in the above example.

Subquery set membership test. Checks whether the value of an expression matches one of the set values produced by a subquery.

Quantified comparison test. Compares the value of. an expression to each of the set of values produced by a subquery. Existence test. Tests whether a subquery produces any rows of query results.

Subqueries that return multiple values

If you want a subquery to return more than one value, you must specify how the returned values should be used in the WHERE clause. Insert ANY or ALL between the comparison operator > , < or < =) and the subquery. Subqueries which use ANY and ALL are called quantified tests.

Suppose you want to list those employees who earn more than any employee in department 3. Your query must do two things: find the salaries in Department 3, and then select all employees who earn more than the lowest salary.

SQL > SELECT ENAME, JOB,PAY,DEPTNO
2 FROM EMP
3 WHERE PAY> ANY
4 (SELECT PAY FROM EMP WHERE DEPTNO = 3)
5 ORDER BY PAY DESC;

Consider the lowest salary in Department 3 is 2,850 (for Joshua), the main query returns all the employees who earn more than 2,850. If now, you use ALL after the comparison operator, the query will select rows in which PAY is greater than all the values returned by the subquery. i.e., it will select employees with salaries greater than the highest salary in Department 3 (ARUN, 15000).

SQL > SELECT ENAME, JOB, PAY, DEPTNO
2 FROM EMP
3 WHERE PAY> ALL
4 (SELECT PAY FROM EMP WHERE DEPTNO = 3)
5 ORDER BY PAY DESC;

SQL*Plus also recognise abbreviations for ANY and ALL. You may substitute In for = ANY, and NOT IN for! = ALL. The comparison tests of this type are called set membership tests. Another example, to list employees in Department 1 with common jobs in Department 3, enter:

SQL > SELECT ENAME, JOB FROM EMP
2 WHERE DEPTNO = I AND JOB IN
3 (SELECT JOB FROM EMP WHERE DEPTNO = 3);

Now, to list the jobs which are not common in Department 1 & 3, enter.

SQL > SELECT ENAME, JOB FROM EMP
2 WHERE DEPTNO = 1 AND JOB NOT
IN 3 (SELECT JOB FROM EMP WHERE DEPTNO = 3);

When a subquery returns more than one colurmn', you must put parentheses around the list of columns on the other side of the comparison operator.

For example, suppose you want a list of employees who have both the same job and the same salary as Vikram. The query would be as follows:

SQL > SELECT ENAME, JOB, PAY
2 FROM EMP WHERE (JOB, PAY)=
3 (SELECT JOB, PAY FROM EMP
4 WHERE ENAME = Vikrarn);

The existence test (EXISTS) checks whether a subquery produces any rows of query results. There is no simple comparison test the resembles the existence test; It is used only with subqueries. The logical expression EXISTS is "true" if the subquery returns at least one row, and "false" it not. eg., to display information about employees who have at least one other employee reporting to them

SQL > SELECT JOB, ENAME, EMPNO, DEPTNO 2 FROM EMP X WHERE EXISTS
3 (SELECT *FROM EMP WHERE
4 X.EMPNO = MGR)

5 ODER BY JOB, ENAME;

Conceptually, SQL*Plus processes this query by going through the EMP table and performing the subquery for each EMPNO that appears in the MGR column of the EMP table.

Multiple Subqueries

The WHERE clause of a query may contain any combination of ordinary conditions and subqueries. In particular, it may contain any number of conditions with subqueries, connected by the operators AND and OR. For example, if you want to list those employee who have the same job as Naweed, or a salary less than or equal to Swamy in order by job, then enter:

SQL > SELECT ENAME, JOB,DEPTNO, PAY

2 FROM EMP WHERE JOB =
3 (SELECT JOB FROM EMP
4 WHERE ENAME 'Naweed')
5 OR PAY < =
6 (SELECT PAY FROM EMP
7 WHERE ENAME = 'Swamy')
8 ORDER BY JOB,. PAY,

Subqueries and joins

Subqueries can also be used to retrieve information from more than one table. For example, suppose you want to find the employees who have the same jobs as the employees who are located at Fort. Since the department numbers of each of the employees is stored in the EMP table and locations of each of the departments is given in the DEPT table, you must join the ENT and DEPT table to get the desired result:

SQL > SELECT ENAME, JOB FROM EMP
2 WHERE JOB IN
3 (SELECT JOB FROM EMP, DEPTS
4 WHERE SITE = 'FORT'
5 AND EMRDEPTNO = DEPTS.DEPTNO)
6 ORDER BY JOB, ENAME;

The subquery in the above example has to join ENT and DEPT to select JOB on the basis of SITE.

Correlated Subqueries

In concept, SQL*Plus performs a subquery repeatedly once for every row of the main query. For may subqueries, however, the subquery produces the same results for every row or row group. For example, suppose you want to find employees who earn more than the average salary in their own department. You need a main query to select them from the E_MP table:

SELECT ENAME, PAY, DEPTNO FROM EMP WHERE PAY > (average pay of candidate employee's department) You also need a subquery that calculates the average salary of each candidate employee's department. (SELECT AVG(PAY) 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 department number. Then the subquery must calculate the average salary for that employee's department. Finally, the main query must compare the employee's salary to the department's average salary.

This kind of query is called a correlated subquery, because each execution of the subquery is correlated with the value of a field in one of the main query's candidate rows. For the same reason, an outer reference is sometimes called a correlated reference.

To find all employees who earn more than the average salary than the average salary of employees in their departments, and list them ordered by their departments, enter

SQL > SELECT ENAME, PAY, DEPTNO
2FROM EMP X WHERE PAY >
3(SELECT AVG(PAY) FROM EMP
4WHERE X. DEPTNO DEPTNO)
5ORDER BY DEPTNO, PAY,

The key to understanding the correlated subquery as shown in the above example is the table alias, X, which appears in the main query (FROM EMP X) And in the subquery (WHERE X. DEPTNO TEPTNO).

Many users find the notation. for a correlated subquery a little difficult to understand, but it is easy once you grasp the basic central concepts: A correlated subquery refers to a column selected by the main query (outside the subquery).

40 If the subquery performs a select from the same table as the main query, the main query must define an alias for the table name, and the subquery must use the alias to refer to the column's value in the main query's candidate rows.

Subqueries in the having clause

Subqueries are most often used with the WHERE clause, but they can also be used in the HAVING clause of 'a' query. When a subquery appears in the HAVING clause, it works as part of the row group selection performed by the HAVING clause.

Consider the following query to list those employee positions whose compensation is more than the average salary of employees of department 3:

SQL > SELECT JOB, AVG(PAY) FROM EMP
2 GROUP BY JOB
3 HAVING AVG(PAY) >
4 (SELECT AVG(PAY) FROM EMP
5 WHERE DEPTNO 3);

The subquery calculates the average salary of the employees in Department 3. It is a simple subquery and contains on outer references, so SQL*Plus can calculated the average once and then use it repeated in the HAVING clause.

Domain Name Search