Skip to main content

JOINS

What is join ?
  •  A join is a query that combines rows from two or more tables, views or materialized views.
  •  A join is performed whenever multiple tables appear in the queries FROM clause.
  •  The queries SELECT list can select any columns from any of these tables.
  •  The common column names within the tables should quality all references to these columns.
  •  When writing a SELECT statement that joins tables, precede the column name with the table name for clarity and enhance database access.
  •  If the same column name appears in more than one table, the column name must be prefixed with the table name.
  •  To join ‘n’ tables together, we need a minimum of ‘n-1’ join conditions.
  •  The oracle optimizer determines the order in which ORACLE should join the tables based on…
o   Given join conditions.
o   INDEXES upon the tables.
o   STATISTICS for the tables.

Join Condition :

§  Many join queries contain WHERE clause, which compares two columns, each from a different table.The applied condition is called JOIN CONDITION.
§  To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the JOIN condition evaluates to TRUE.
§  The columns in the join condition need not be part of the SELECT list.
§  The WHERE clause of join query can also contain other conditions that refer to columns of only one table.
§  To execute a join of 3 or more table…,
Oracle first joins two of the tables based on the join conditions comparing these columns and then jon’s the result to another table.

Syntax : WHERE table1.Column1=Table2.Column2
Example : -

SQL> SELECT empno, ename, dname, loc FROM emp,dept;
SQL> SELECT empno, ename, losal, grade FROM emp,salgrade;
SQL> SELECT empno, ename, dname, loc, grade FROM emp,dept,salgrade;


Things to Remember About Joins : 
  • The columns specified in the join condition need not be specified in the SELECT list. 
  • Usually the join condition is specified on the foreign key columns of one table and the primary key or unique key columns of another table. However, you can specify other columns as well. Each join condition involves columns that relate two tables.
  • A join condition may involve more than one column. This is usually the case when a foreign key constraint consists of multiple columns.
  • The total number of join conditions is always equal to the total number of tables less one.
  • A join condition must involve columns with compatible datatypes. Note that the datatype of the columns involved in a join condition need to be compatible, not the same. Oracle performs automatic datatype conversion between the join columns, if required.
  • It is not necessary that a join condition involve the equal to (=) operator. A join condition may contain other operators as well. 

Comments

Post a Comment

Popular posts from this blog

About Oracle Buffer

About Oracle Buffer :- All Commands of SQL are typed at the SQL prompt. Only one SQL statement is managed in the SQL buffer. The current SQL statement replaces the previous SQL statement in the buffer. The SQL statements can be divided into different lines within the SQL buffer. Only one line i.e, the current line can be active at a time in the SQL buffer. At SQL prompt, editing is possible only in the current SQL buffer line. Every statement of SQL should be terminated using semi colon " ; ". One sql statement can contain only one semi colon. To run the previous or current SQL statement in the bufer type " / " at SQL prompt. To open the SQL editor type " ED " at SQL prompt.
Post a Comment
Read more

Oracle Object Oriented Concepts

An Object is a reusable application component that developers need to be aware of, rather than how it works. Object are basic entities in a system. They could represent a person, place, bank account, or any item that is handled by program. Every object consists of an attribute and one or more methods. An attribute could be any property of the object. Class:           It is a collection of attributes and functions (method) to plan the object. Object Table : ·    Object table are created by using the user defined data types. ·        In an object table each row or record is treated as an object. ·        Each row in an object table has an object Identified (OID), which is unique through out       the   database. ·       The rows or objects of an object table can be referenced by other objects with in the  database. ·    ...
Post a Comment
Read more

Filtering and Sorting data using SQL statements

  Filtering & Sorting Data using SQL     Once you know the basics ( SELECT , FROM , WHERE ), filtering and sorting helps you get the exact rows from tables .   1. Filtering with Conditions ( AND , OR , NOT )  - Use WHERE to filter rows. - Combine with logical operators :     • AND: all conditions true      • OR: any condition true      • NOT: negate condition   - - All conditions true (AND) SELECT *  FROM employees WHERE department = 'Sales'    AND salary > 50000; -- Any condition true (OR) SELECT *  FROM employees WHERE department = 'Sales'     OR department = 'Marketing'; -- Negate condition (NOT) SELECT *  FROM employees WHERE NOT department = 'HR';   2. Pattern Matching with LIKE   - % = any sequence, _ = single char     • Names starting with 'J'      • Names with 4 letters starting with 'J'   - - N...
Post a Comment
Read more