• Loading
    • SQL Joins And Sub Queries

      Learning Objectives
      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


      Equi 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


      Example:
      Code:
      SELECT loan_no,loan_amount,product_name 
      FROM daily_pipeline_loan_fact loan, 
      Product_dim product 
      WHERE product.product_key= loan.product_key

      Removing Ambiguity

      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
      Example:
      Code:
      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

      Table Aliases

      • 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.
      Example:
      Code:
      SELECT loan.product_key,loan_no, 
      loan_amount,product_name 
      FROM daily_pipeline_loan_fact loan, 
      Product_dim product 
      WHERE product.product_key >=110

      Outer Join


      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:
      Code:
      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(+)
      Code:
      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:

      Code:
      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
      Code:
      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:
      Code:
      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

      Self Join


      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.
      Example:
      The following query uses a self join to return the name of each employee along with
      The name of the employee's manager:
      Code:
      SELECT e1.ename||' works for '||e2.ename 
      "Employees and their Managers" 
      FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;

      Natural Join


      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 .
      Example:
      Code:
      SELECT loan.product_id,loan_no, 
      loan_amount,product_name 
      FROM Product_dim product, 
      daily_pipeline_loan_fact loan 
      WHERE NATURAL JOIN product_dim;

      Understand Subquery


      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.


      Example:
      Code:
      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.


      Example:
      Code:
      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.
      Example:
      Code:
      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.
      Example:
      Code:
      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 Subquery


      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


      Syntax:
      Code:
      SELECT column1,column2 
      FROM table1 outer 
      WHERE column1 operator (SELECT column1,column2 
      FROM table2 
      WHERE expr1=outer.expr2
      The sub query references a column from a table in the parent query.

      Example:
      Find all the loans having loan amount more then the average loan amount in it’s product category.
      Code:
      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.
      Code:
      UPDATE table1 alias1 
      SET column=(SELECT expression 
      FROM table2 alias2 
      WHERE alias1.column=alias2.column)
      Example:
      Code:
      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.
      Code:
      DELETE table1 alias1 
      SET column=(SELECT expression 
      FROM table2 alias2 
      WHERE alias1.column=alias2.column)
      Example:
      Code:
      DELETE daily_pipeline_loan_fact outerloan 
      SET loan_status=(SELECT loan_status 
      FROM loan_status_dim status 
      WHERE outerloan.loan_key= 
      status.loan_key)


      Hierarchical Subquery


      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.
      Code:
      SELECT [LEVEL],column,expr,… 
      FROM table 
      [WHERE condition(s)] 
      [START WITH condition(s)] 
      [CONNECTED BY PRIOR condition(s)]
      Hierarchical queries can be identified by the presence of the CONNECT BY and START WITH Clauses.

      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.

      Example:
      Code:
      SELECT employee_id,last_name,job_id, 
      manager_id 
      FROM employees 
      START WITH employee_id=101 
      CONNECT BY PRIOR manager_id=employee_id

      Summary

      • 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?
    • Enter your email address:

      Delivered by FeedBurner

    • Categories




    Disclaimer: Users of techforum4u.com are responsible for ensuring that any material they post (article, blog posts, images or other mulitimedia content) does not violate or infringe upon the copyright, patent, trademark, or any personal or proprietary rights of any third party, and is posted with the permission of the owner of such rights.Anyone who violates these rules may have their access privileges removed without warning.