After completing this session, you will be able to:
- Explain joins
- Describe different types of joins
- Write sub-query
- Write co-related sub-query
Understand the Join
A join is a query that combines rows from two or more tables, views, or materialized views ("snapshots"). Oracle performs a join whenever multiple tables appear in the query’s FROM clause. The query’s select list can select any columns from any of these tables. If any two of these tables have a column name in common, you must qualify all references to these columns throughout the query with table names to avoid ambiguity.
The following are the requirements for using joins:
- Normalization splits a complex table into multiple simple tables. Tables are joined temporarily using a table join strategy to produce a single resultant table for query purposes.
- As it is only for query purposes, data redundancy is completely avoided.
- Tables in a database can be related to each other with common keys (primary key and foreign keys). The purpose is to bind data together, across tables, without repeating all of the data in every table.
Types of joins:
- Equi Join
- NON Equi Join
- Outer Join
- Self Join
What is an Equi-join?
- Is defined as a join in which more than one tables are joined together with the help of a common column that exists in both the tables
- In this type of join the relationship between the tables are specified in the where clause, by using an equal (=) symbol
- EQUI Joins are also called simple joins and inner joins
SELECT loan_no,loan_amount,product_name FROM daily_pipeline_loan_fact loan, Product_dim product WHERE product.product_key= loan.product_key
When more than one column joined, and if one column exists in both the tables and that column needs to be displayed in the output, it must be preceded by the table name followed by a period (.) .Otherwise an ambiguity will occur because SQL will not understand from which table the said column to display
SELECT loan.product_id,loan_no, loan_amount,product_name FROM daily_pipeline_loan_fact loan, Product_dim product WHERE product.product_id=loan.product_id
- Should not be more than 30 characters long, but the shorter it is the better.
- The table alias should be substituted for the table name throughout the SELECT statement.
- Valid only for the current SELECT statement.
- Should be meaningful.
Non Equi Join
A non equi join is a join condition containing something other than a equal operator .Others condition such as <=,>=, <>, BETWEEN can be used.
SELECT loan.product_key,loan_no, loan_amount,product_name FROM daily_pipeline_loan_fact loan, Product_dim product WHERE product.product_key >=110
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. A simple join does not return such rows. 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, Oracle returns NULL for any select list expressions containing columns of B.
Outer join queries are subject to the following rules and restrictions:
- The (+) operator can appear only in the WHERE clause or, in the context of left correlation (that is, when specifying the TABLE clause) in the FROM clause, and can be applied only to a column of a table or view.
- If A and B are joined by multiple join conditions, you must use the (+) operator in all of these conditions. If you do not, Oracle will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.
- The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain a column marked with the (+) operator.
The following are the types of outer join:
- Left outer, means all rows of the table left to the Join condition will appear
- Right outer, means all rows of the table right to the Join condition will appear
- Full outer, means missing rows from both the tables being joined
Example of left outer join:
SELECT loan.product_key,loan_no, loan_amount,product_name FROM Product_dim product, daily_pipeline_loan_fact loan WHERE product.product_key=loan.product_key(+)
SELECT loan.product_key,loan_no, loan_amount,product_name FROM Product_dim product LEFT OUTER JOIN daily_pipeline_loan_fact loan ON product.product_key=loan.product_key
Example of right outer join:
SELECT loan.product_key,loan_no, loan_amount,product_name FROM Product_dim product, daily_pipeline_loan_fact loan WHERE product.product_key(+)=loan.product_key
SELECT loan.product_key,loan_no, loan_amount,product_name FROM Product_dim product RIGHT OUTER JOIN daily_pipeline_loan_fact loan ON product.product_key=loan.product_key
Example of full outer join:
SELECT loan.product_id,loan_no, loan_amount,product_name FROM Product_dim product FULL OUTER JOIN daily_pipeline_loan_fact loan ON product.product_key=loan.product_key
When one row of one table is to be compared with another row of the same table, then self join is used. Logical tables need to be created from the same table temporarily. The table, on which the self join will be used, appears twice in the FROM clause, and is followed by table aliases that qualify column names in the join condition.
The following query uses a self join to return the name of each employee along with
The name of the employee's manager:
SELECT e1.ename||' works for '||e2.ename "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;
The Natural Join clause is based on all columns in the two tables that have the same name. It select rows from the two tables that have equal values in all matched column. If the columns having the same names have different data types , then an error is returned .
SELECT loan.product_id,loan_no, loan_amount,product_name FROM Product_dim product, daily_pipeline_loan_fact loan WHERE NATURAL JOIN product_dim;
A subquery is a SELECT statement that is nested within another SELECT statement .In subquery the result of the inner one is passed as an argument to the outer one. You can place the subquery in a number of SQL clauses, including:
- The WHERE clause
- The HAVING clause
- The FROM clause
Subqueries may be:
- Single row subquery (Single row comparison operators can be used for example =, <, >, <=, >=, and so on)
- Multiple row subquery (Multi row comparison operator for example IN, SOME/ANY, or ALL operators)
Guidelines for using subqueries:
- A subquery must be enclosed in parentheses
- Place the subquery on the right side of the comparison condition for readability.
SELECT loanno,loan_status,loan_type FROM daily_pipeline_loan_fact WHERE SAL=(SELECT MAX(loan_amount) FROM daily_pipeline_loan_fact);
EXISTS operator: Exists returns a value TRUE if the subquery that follows it returns at least one row.
SELECT loanno, loan_amount FROM daily_pipeline_loan_fact WHERE EXISTS (SELECT COUNT(*) FROM daily_pipeline_loan_fact WHERE loan_status=‘FUNDED’ HAVING COUNT(*) > 5) ORDER BY loanno;
ANY operator: The ANY operator compares the main query with any of the values returned by the inner query.
SELECT loanno, loan_amount,loan_status FROM daily_pipeline_loan_fact WHERE loan_amount > ANY (SELECT loan_amount FROM daily_pipeline_loan_fact WHERE loan_status =‘FUNDED’);
ALL operator: The ALL operator compares a value to every value return by the sub query.
SELECT loanno, loan_amount,loan_status FROM daily_pipeline_loan_fact WHERE loan_amount > ALL (SELECT loan_amount FROM daily_pipeline_loan_fact WHERE loan_status =‘FUNDED’);
Correlated sub queries are used for row by row processing. Each sub query executed once for every row of outer query.
Steps of execution of Correlated Sub queries:
- Get candidate row (fetched by outer query)
- Execute Inner query using candidate row’s value.
- Use Value(s) resulting from inner query to qualify or disqualify candidate.
- Repeat until no candidate row remains
SELECT column1,column2 FROM table1 outer WHERE column1 operator (SELECT column1,column2 FROM table2 WHERE expr1=outer.expr2
Example: Find all the loans having loan amount more then the average loan amount in it’s product category.
SELECT loanno,loan_amount,product_key FROM daily_pipeline_loan_fact outer WHERE loan_amount >(SELECT avg(loan_amount) FROM daily_pipeline_loan_fact WHERE product_key=outer.product_key)
Correlated UPDATE: Use a correlated sub query to update rows in one table based on rows from another table.
UPDATE table1 alias1 SET column=(SELECT expression FROM table2 alias2 WHERE alias1.column=alias2.column)
UPDATE daily_pipeline_loan_fact outerloan SET loan_status=(SELECT loan_status FROM loan_status_dim status WHERE outerloan.loan_key= status.loan_key)
Correlated DELETE: Use a correlated sub query to delete rows in one table based on rows from another table.
DELETE table1 alias1 SET column=(SELECT expression FROM table2 alias2 WHERE alias1.column=alias2.column)
DELETE daily_pipeline_loan_fact outerloan SET loan_status=(SELECT loan_status FROM loan_status_dim status WHERE outerloan.loan_key= status.loan_key)
Using hierarchical queries, you can retrieve data based on a natural hierarchical relationship between rows in a table .A relational database does not store records in a hierarchical way. However where a hierarchical relationship exists between the rows of a single table, a process called tree walking enables the hierarchy to be constructed. A hierarchical query is a method of reporting, in order, the branches of a tree.
SELECT [LEVEL],column,expr,… FROM table [WHERE condition(s)] [START WITH condition(s)] [CONNECTED BY PRIOR condition(s)]
LEVEL: For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row and 2 for a child of a root, and so on.
START WITH: Specifies the root rows of the hierarchy (where to start). This clause is required for a true hierarchical query.
CONNECT BY: Specifies the columns in which the relationships PRIOR between parent and child rows exist. This clause is required for a hierarchical query.
SELECT employee_id,last_name,job_id, manager_id FROM employees START WITH employee_id=101 CONNECT BY PRIOR manager_id=employee_id
- A join is a query that combines rows from two or more tables, views or materialized views (snapshots). Oracle performs a join whenever multiple tables appear in the query's FROM clause.
- In Oracle three types of joins are permitted: Equi Join, Outer Join, and Self Join.
- A sub-query is a select statement that is nested within another select statement, where the result of the inner one is passed as an argument to the outer one.
- In correlated sub-query, each execution of the outer query will ensure the inner query to be executed for all of its values
- A hierarchical query is a method of reporting ,in order, the branches of a tree
Test Your Understanding
- What are the different types of join?
- What is a Non equijoin?
- What are the different types of outer join?
- What is natural join?
- What is a subquery?
- What is a correlated subquery?
- What is a Hierarchical subquery?