What are Oracle Joins, Set and Subqueries

joins,set and subqueries

Sometimes you need to display data from multiple tables. In order to perform that you need to link one table to another table.

There are various ways in which we can obtain data from Multiple tables.I would be giving details on  Joins, Set and Subqueries

Join

A Join is a query that combines rows from two or more tables.

Oracle performs a join whenever multiple tables appear in the FROM clause. In order to join two tables, you need to identify the common columns that relate the two tables. In the WHERE clause you define the relationship between the tables listed in the FROM clause.

The most common operator used to relate two tables is the equality operator (=). This is called an equality join or equijoin. This simple join also known as inner join

Some Guidelines

1) when writing the select statement that joins tables, it is good practice to precede the column name with table name for clarity purpose.

2) To join m tables, we need at least m-1 conditions

3) We can use table alias for the tables name if the table name is lengthy. Table looks the code shorter and hence less memory

4)  If you are not using table_name or tables alias while selecting the column and If multiple tables are having same column name, then we should specify the table_name for the column which is common across the tables

Syntax

Select tab1.col,tab2.col from tab1 ,tab2 where tab1.col=tab2.col

SELECT EMP_ID,EMP_NAME, DEPT_ID,DEPARTMENT_NAME  FROM EMP  , DEPT where EMP. DEPT_ID= DEPT. DEPT_ID;

 

To execute a join of three or more table, Oracle joins two tables based on the join condition, then joins the result to another table, based on join conditions and repeats until all tables are joined.

 

Complex Joins – Have one or more conditions added in the WHERE clause. For example, if you are interested in all department and their locations outside the UK use

SELECT LOCATIONS.LOCATION_ID, CITY, DEPARTMENT_NAME FROM LOCATIONS, DEPARTMENTS WHERE LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID AND COUNTRY_ID != ‘UK’;

Non –equijoin

A non-equijoin is the join condition other than equality operator.

It is an inner join statement that uses an unequal operation (i.e.: <>, >, <, =, BETWEEN, etc.) to match rows from different tables

Example

SELECT e.ename, e.sal, s.grade

FROM  emp e, sal_grade s

WHERE e.sal BETWEEN s.low_sal AND s.high_sal;

Cartesian Products or Cartesian Join

-A Cartesian join (or Cartesian product) occurs when data is selected from two or more tables and join condition is defined or no common relationship is defined in the WHERE clause

-If a join is performed and no join condition is specified, a Cartesian product results. A Cartesian product is a result set that is the product of the two tables’ total rows. If table “P” has 100 rows and table “Q” has 100 rows and a Cartesian product is developed, the resulting result set will have 10000 rows. If a query joins three or more tables, the optimizer may find a way to choose a join order that precludes a Cartesian product, but don’t count on it.

 

SELECT EMP_ID,EMP_NAME, DEPT_ID,DEPARTMENT_NAME  FROM EMP   , DEPT ;

Outer Joins

The inner joins we have seen, return the matching rows from two or more tables according the join condition. The Outer Join mechanism returns the data from one table even if there is no corresponding row in the joining table. In Oracle syntax, enter a (+) sign next to the column name of the table where there may not be a corresponding row.

The two operators that can be used in an outer join condition are ‘=’ and ‘AND’.

 

If you want to list all emp and dept info  even if there is no data corresponding from the dept  table then

select empno,ename,job,dname,loc from emp e,dept d where

e.deptno=d.deptno(+);

NATURAL JOIN

The natural join specifies that the join relate all columns with the same names on the two tables.

Basically it compares the common columns of both tables with each other. One should check whether common columns exist in both tables before doing a natural join.

 

Example

SELECT EMP_ID,EMP_NAME, DEPT_ID,DEPARTMENT_NAME  FROM EMP  NATURAL JOIN DEPT;

 

 

Here dept_id is the same column between the tables emp and dept

Natural join can join more than two tables

Cross Join

The cross join produces the cross-product of the two tables

SELECT EMP_ID,EMP_NAME, DEPT_ID,DEPARTMENT_NAME  FROM EMP   CROSS JOIN DEPT ;

 

Outer joins in ANSI SQL

They are represented by LEFT OUTER JOIN and RIGHT OUTER JOIN.

Left Outer Join – returns the rows matching from both tables as well as the unmatched rows from the table on the left of the join clause.

Example:

select empno,ename,job,dname,loc from emp e  left outer join dept d on e.deptno=d.deptno;

Left outer join is indicated when the plus sign is on the right side of the equation.

 

Right Outer Join – returns the rows matching from both tables as well as the unmatched rows from the table on the right of the join clause.

 

Example:

select empno,ename,job,dname,loc from emp e  right outer join dept d on e.deptno=d.deptno;

RIGHT OUTER JOIN is indicated when the plus sign is on the left of the equation.

 

 Full Outer Join

Using the ANSI SQL, a full outer join between two tables can be achieved

SELECT E.EMP_ID, E.LAST_NAME, D.DEPT_NAME FROM EMP E FULL OUTER JOIN DEPTS D ON E.DEPT_ID = D.DEPT_ID;

 

This query will return all rows from both tables. Using Oracle syntax using E.DEPT_ID (+) = D.DEPT_ID (+) is impossible because the (+) sign may reference only one table.

Using Set Operators

Set operators can be used to select data from multiple tables. They combine the results of two or more queries. When using the Set operators each column the SELECT clause must match the columns in the first SELECT.

There are 4 Set operators:

UNION It returns all unique rows returned from both queries
UNION ALL It returns all rows including duplicates
INTERSECT It returns only the rows returned from both queries
MINUS It returns unique rows selected by first query but not the rows selected from second query

 

Examples

Union SELECT FIRST_NAME FROM EMP_DATA WHERE DEPT_ID = 7865 UNION SELECT FIRST_NAME FROM EMP_DATA WHERE ENAME LIKE ‘JAT%’;

 

This query will return unique rows of all employees that work in department 7865 and all employees who’s name begins with JAT

 

Union all SELECT FIRST_NAMEFROM EMP WHERE DEPT_ID = 8876 UNION ALL SELECT FIRST_NAMEFROM EMP WHERE ENAME LIKE ‘JAT%’;

 

This query will return all employees that work in department 8876 and all employees who’s name begins with JAT.

 

INTERSECT SELECT FIRST_NAME FROM EMP WHERE DEPT_ID = 900 INTERSECT SELECT FIRST_NAME FROM EMP WHERE ENAME LIKE ‘JAT%’;

This query will return all employees that work in department 900 who’s name begins with JAT

 

MINUS SELECT FIRST_NAMEFROM EMP WHERE DEPT_ID = 900 MINUS SELECT FIRST_NAMEFROM EMP WHERE ENAME LIKE ‘JAT%’;

 

This query will return the employees that work in department 900 but not employees who’s name begins with JAT.

 

 

 

 

Sub Queries

 

A Subquery or Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is a query within a query

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries answer the queries that have multiple parts. The parent query answers a part and the sub query answers other part

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

Using subqueries in a FROM clause is known as an inline view.

Using subqueries in the WHERE clause is called a nested subquery. Up to 255 nested queries are allowed.

Some Guidelines

1) We need to put sub queries in parenthesis alwayss

2) We need to place subqueries on the right side of the comparison operator

3) Use single row operator with single row subqueries and multiple row operator with multiple row subqueries

 

General Syntax

SELECT col1, col2

FROM   table1

WHERE  col1  OPERATOR

(SELECT col1

FROM table2

[WHERE])

Single Row Subquery

It returns only one row of results and uses a single row operator (most common is the equal operator (=)).  The other operators are  > ,< ,>=  ,=<

Single row subqueries can select data from the same table or from another table

 

SELECT ENAME FROM EMP WHERE SALARY = (SELECT MIN(SALARY) FROM EMP);

SELECT ENAME FROM EMP WHERE DEPT_ID = (SELECT DEPT_ID FROM DEPT WHERE DNAME = ‘Admission’);

 

 

Multiple Row Subquery

It returns several rows of results from the subquery, uses the IN operator. In the previous query, if there was more than one accounting department, the query would have failed. Example of returning more than one row in the subquery

The other operator which are used is any and all

 

SELECT ENAME, DEPT_ID FROM EMP WHERE DEPT_ID IN (SELECT DEPT_ID FROM DEPT WHERE DNAME LIKE ‘TA%’);

 

Correlated Subquery

A correlated subquery is a subquery that relies on columns from the parent query. A correlated subquery is evaluated for each row processed by the parent query. The parent statement can be a SELECT, UPDATE or DELETE.

 

SELECT LAST_NAME,FIRST_NAME,SALARY FROM EMPLOYEES E1 WHERE SALARY = (SELECT MAX(SALARY)

FROM EMPLOYEES E2 WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID);

 

 

 

Scalar Subqueries

It returns exactly one value from one row, used most often in the VALUES clause of an INSERT statement, in and ORDER BY or WHERE clause and in a SELECT clause:

 

SELECT LAST_NAME, FIRST_NAME,

DEPARTMENT_ID, (SELECT MAX(SALARY)

FROM EMPLOYEES E2

WHERE E2.DEPARTMENT_ID = E1.DEPARTMENT_ID) HIGH_SAL

FROM EMPLOYEES E1

WHERE FIRST_NAMELIKE ‘J%’;

 

 

 

The sub query executes once for each execution of the master query. A single-row subquery can only be used with single-row operators

 Multiple-Column Subqueries

The query can have  more than one column in the SELECT clause of a subquery or in UPDATE statements

 

SELECT CITY,CITY_CODE,CITY_DESCRIPTION

FROM LOCATIONS

WHERE (LOCATION_ID, COUNTRY_ID)

IN (SELECT LOCATION_ID, COUNTRY_ID

FROM LOCATIONS

WHERE STATE_PROVINCE = ‘NEWYORK’);

 

 

Subqueries in other DML Statements

Subqueries can be used in UPDATE, DELETE and INSERT statements

 

UPDATE EMP

SET SALARY = (SELECT SALARY FROM EMP

WHERE EMPLOYEE_ID = 112408)

WHERE EMPLOYEE_ID = 193711;

 

INSERT INTO DEPT (DEPT_ID, DEPT_NAME)

VALUES ((SELECT MAX(DEPT_ID)

FROM DEPT), ‘NEW DEPT’);

 

DELETE FROM CUSTOMERS

WHERE AGE IN (SELECT AGE FROM CUST_BACK

WHERE AGE > 40 );

Hope you like the details on Joins, Set and Subqueries.  Looking for your feedback on this