An “upsert” in the combination of an INSERT and UPDATE statement, built into a single clause. The Upsert model is especially useful in data warehouses where you need the following logic:

IF FOUND

THEN UPDATE

ELSE

INSERT;

Upserts are great for processes that you normally require multiple insert as select statements IAS) statements. This is because upserts remove the need for row-at-a-time processing and enable the entire transaction as a single set.

Let’s illustrate upserts with a simple example. Let’s assume that we need to take a NEW_CUSTOMERS table and spread the row into two other tables. The RICH_CUSTOMERS table is populated by selecting only those customers with a credit_limit > 100000, and all tables are moved into the CUSTOMER table.

In Oracle8i, this operation required two statements:

INSERT INTO

rich_customers

(cust_id,cust_credit_limit)

SELECT cust_id, cust_credit_limit

FROM new_customers

WHERE credit_limit >=100000;



INSERT INTO customers SELECT * FROM new_customers;

In Oracle9i, an UPSERT can accomplish this task in a single statement:

INSERT

FIRST WHEN

credit_limit >=100000

THEN INTO

rich_customers

VALUES(cust_id,cust_credit_limit)

INTO customers

ELSE

INTO customers SELECT * FROM new_customers;

We have "alter table" syntax from Oracle to add data columns in-place in this form:

alter table
table_name
add
(
column1_name column1_datatype column1_constraint,
column2_name column2_datatype column2_constraint,
column3_name column3_datatype column3_constraint
);

Here are some examples of Oracle "alter table" syntax to add data columns.

alter table
cust_table
add
cust_sex varchar2(1) NOT NULL;

Her is an example of Oracle "alter table" syntax to add multiple data columns.

ALTER TABLE
cust_table
ADD
(
cust_sex char(1) NOT NULL,
cust_credit_rating number
);

This statement shows how the employees table owned by the sample human resources (hr) schema was created. A hypothetical name is given to the table and constraints so that you can duplicate this example in your test database:

CREATE TABLE employees_demo
   ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
        CONSTRAINT emp_last_name_nn_demo NOT NULL
    , email          VARCHAR2(25)
        CONSTRAINT emp_email_nn_demo     NOT NULL
    , phone_number   VARCHAR2(20)
    , hire_date      DATE  DEFAULT SYSDATE
        CONSTRAINT emp_hire_date_nn_demo  NOT NULL
    , job_id         VARCHAR2(10)
       CONSTRAINT     emp_job_nn_demo  NOT NULL
    , salary         NUMBER(8,2)
      CONSTRAINT     emp_salary_nn_demo  NOT NULL
    , commission_pct NUMBER(2,2)
    , manager_id     NUMBER(6)
    , department_id  NUMBER(4)
    , dn             VARCHAR2(300)
    , CONSTRAINT     emp_salary_min_demo
                    CHECK (salary > 0)
    , CONSTRAINT     emp_email_uk_demo
                    UNIQUE (email)
   ) ;

This table contains twelve columns. The employee_id column is of datatype NUMBER. The hire_date column is of datatype DATE and has a default value of SYSDATE. The last_name column is of type VARCHAR2 and has a NOT NULL constraint, and so on.

Oracle Database 10g on Mac OS/X

Oracle Database 10g release 10.2.0.4 for Apple MAC OS X (Leopard 10.5.4) Intel x86-64 Available on OTN http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/10204macsoft_x86-64.html

A join is a query that combines rows from two or more tables, views, or materialized views. 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.
Join Conditions

Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a 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 conditions need not also appear in the select list.
Equijoins

An equijoin is a join with a join condition containing an equality operator ( = ). An equijoin combines rows that have equivalent values for the specified columns.

For Example :
the following query returns empno,name,sal,deptno and department name and city from department table.

select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,dept.city from emp,dept where emp.deptno=dept.deptno;


The above query can also be written like, using aliases, given below.

select e.empno, e.ename, e.sal, e.deptno, d.dname, d.city from emp e, dept d where emp.deptno=dept.deptno;

The above query can also be written like given below without using table qualifiers.

select empno,ename,sal,dname,city from emp,dept where emp.deptno=dept.deptno;


And if you want to see all the columns of both tables then the query can be written like this.

select * from emp,dept where emp.deptno=dept.deptno;


Non Equi Joins.

Non equi joins is used to return result from two or more tables where exact join is not possible.

For Example : we have emp table and salgrade table. The salgrade table contains grade and their low salary and high salary. Suppose you want to find the grade of employees based on their salaries then you can use NON EQUI join.

select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.lowsal and s.hisal
Self Joins

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.

For example:The following query returns employee names and their manager names for whom they are working.

Select e.empno, e.ename, m.ename “Manager” from emp e,

emp m where e.mgrid=m.empno
Inner Join

An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition.
Outer Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

* To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the ANSI LEFT [OUTER] JOIN syntax, or 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.

* To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the ANSI RIGHT [OUTER] syntax, or apply the outer join operator (+) to all columns of A in the join condition. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.

* To write a query that performs an outer join and and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the ANSI FULL [OUTER] JOIN syntax.

For example the following query returns all the employees and department names and even those department names where no employee is working.

select e.empno,e.ename,e.sal,e.deptno,d.dname,d.city from emp e, dept d where e.deptno(+)=d.deptno;

That is specify the (+) sign to the column which is lacking values.
Cartesian Products

If two tables in a join query have no join condition, Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product.