RDBMS Using Oracle. Lecture Week 7 Introduction to Oracle 9i SQL Last Lecture. Joining Tables

(1)

RDBMS Using Oracle

Lecture Week 7

Introduction to Oracle 9i SQL

Last Lecture

Joining Tables

Multiple Table Queries

Simple Joins

Complex Joins

Cartesian Joins

Outer Joins

Multi table Joins

Other Multiple Table Queries

(2)

©

The tables being joined are listed in the

FROM clause.

The join is performed in the WHERE

clause. Several operators can be used to

join tables, such as =, <, >, <>, <=, >=,

!=, BETWEEN, LIKE, and NOT; they can

all be used to join tables. However, the

most common operator is the equal

symbol.

Simple Joins

This most common join is Simple Join that is This most common join is Simple Join that is

joining two tables with equality operator, it is an joining two tables with equality operator, it is an equality join also known as

equality join also known as equijoinequijoin..

EQUIJOINEQUIJOIN, also referred to as an INNER JOIN. , also referred to as an INNER JOIN.

The The EQUIJOINEQUIJOIN joins two tables with a common joins two tables with a common

column in which each is usually the primary key. column in which each is usually the primary key.

(3)

©

The syntax for an

EQUIJOIN

is

SELECT TABLE1.COLUMN1, TABLE2.COLUMN2...FROM TABLE1, SELECT TABLE1.COLUMN1, TABLE2.COLUMN2...FROM TABLE1,

TABLE2

TABLE2 [, TABLE3 ][, TABLE3 ] WHERE WHERE

TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME [AND TABLE1.COLUMN_NAME = TABLE3.COLUMN_NAME ] [AND TABLE1.COLUMN_NAME = TABLE3.COLUMN_NAME ]

Example:

Select

Select emp.empno

emp.empno,

, emp.ename

emp.ename,

, dept.dname

dept.dname

from

from emp,dept

emp,dept

Where

Where emp.deptno

emp.deptno

= dept.deptno

=

dept.deptno

©

Another Example

Select

Select emp

emp.*,

.*,

dept.dname,

dept.dname

, dept.loc

dept.loc

from

from emp

emp

, dept

Where

Where emp.deptno

emp.deptno

= dept.deptno

=

dept.deptno

Remember that the asterisk (*) represents all columns of a table.

Cross Join

(4)

©

Complex Joins

Apart from specifying joining condition, we

can also write some other condition to

limit rows selected.

Such joins are known as complex joins

Select

Select emp

emp.*,

.*,

dept.dname,

dept.dname

, dept.loc

dept.loc

from

from emp

emp

, dept

Where

Where emp.deptno

emp.deptno

= dept.deptno

=

dept.deptno

and

(5)

©

Joins of Non

Joins of Non-

-Equality

Equality

NON-NON-EQUIJOINEQUIJOIN joins two or more tables based on joins two or more tables based on

a specified column value not equaling a specified a specified column value not equaling a specified column value in another table.

column value in another table.

The syntax for the NON-The syntax for the NON-EQUIJOINEQUIJOIN isis

FROM TABLE1, TABLE2 [, TABLE3 ] WHERE FROM TABLE1, TABLE2 [, TABLE3 ] WHERE

TABLE1.COLUMN_NAME != TABLE2.COLUMN_NAME TABLE1.COLUMN_NAME != TABLE2.COLUMN_NAME [ AND

[ AND TABLE1.COLUMN_NAME != TABLE1.COLUMN_NAME != TABLE2.COLUMN_NAME ]

TABLE2.COLUMN_NAME ]

©

Non

-

Equi

Join Row count

Suppose only 6 rows exist in each table. For every Suppose only 6 rows exist in each table. For every

record in table A, there is a corresponding record record in table A, there is a corresponding record in table B. Because non

in table B. Because non--equality is to be tested in equality is to be tested in the join of the two tables, each row in the first the join of the two tables, each row in the first table is paired with all rows from the second table is paired with all rows from the second table, except for its own corresponding row. This table, except for its own corresponding row. This means that each of the 6 rows are paired with 5 means that each of the 6 rows are paired with 5 unrelated rows in the second table; 6 rows unrelated rows in the second table; 6 rows multiplied by 5 rows equals 30 rows total. multiplied by 5 rows equals 30 rows total.

(6)

©

Natural Join

NATURAL JOIN is nearly the same as the NATURAL JOIN is nearly the same as the EQUIJOINEQUIJOIN; ;

however, the NATURAL JOIN differs from the

however, the NATURAL JOIN differs from the EQUIJOINEQUIJOIN by eliminating duplicate columns in the joining columns. by eliminating duplicate columns in the joining columns.

There is also no need to specify join condition, This join There is also no need to specify join condition, This join

is based on all columns with the same name and is based on all columns with the same name and datatype

datatypein both tables.in both tables. Select

Select empnoempno, , enameename, , dnamednamefromfrom emp

empNATURAL JOIN dept;NATURAL JOIN dept;

Select * from

(7)

Joins - Explanation

When you join two or more tables, a good idea is to When you join two or more tables, a good idea is to precede the field names with the table names. This is not precede the field names with the table names. This is not mandatory unless the same field name is found in more mandatory unless the same field name is found in more than one table.

than one table.

If you precede the field name with a table name, place a If you precede the field name with a table name, place a

period between the two names. For example, period between the two names. For example, tablename.fieldname

tablename.fieldname. .

You must specify which fields are being joined. You must specify which fields are being joined.

If you do not specify which fields are being joined, the If you do not specify which fields are being joined, the

result is what is commonly referred to as a "Cartesian result is what is commonly referred to as a "Cartesian join" in which all rows in the first table are joined with all join" in which all rows in the first table are joined with all rows in the second table.

(8)

Outer Joins

An OUTER JOIN is used to return all rows that exist in one An OUTER JOIN is used to return all rows that exist in one

table, even though corresponding rows do not exist in the table, even though corresponding rows do not exist in the joined table.

joined table.

The (+) symbol is used to denote an OUTER JOIN in a The (+) symbol is used to denote an OUTER JOIN in a query. The (+) is placed at the end of the table name in the query. The (+) is placed at the end of the table name in the WHERE clause.

WHERE clause.

The table with the (+) should be the table that does not The table with the (+) should be the table that does not

have matching rows. have matching rows.

In many implementations, the OUTER JOIN is broken down In many implementations, the OUTER JOIN is broken down

into joins called LEFT OUTER JOIN, RIGHT OUTER JOIN, into joins called LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

and FULL OUTER JOIN.

Outer Join

The Oracle syntax is

FROM TABLE1, TABLE2 [, TABLE3 ]

WHERE TABLE1.COLUMN_NAME[(+)] = TABLE2.COLUMN_NAME[(+)]

[ AND TABLE1.COLUMN_NAME[(+)] = TABLE3.COLUMN_NAME[(+)]]

To write a query that performs an outer join of tables A and B and returns all rows form A, apply the outer-join operator (+) to all columns of B in the outer-join

condition. For all rows in A that have no matching rows in B, the query returns NULL values for the columns in B.

(9)

©

Outer Join

The next example accomplishes the desired output The next example accomplishes the desired output through the use of an OUTER JOIN. Oracle's syntax is through the use of an OUTER JOIN. Oracle's syntax is used for the OUTER JOIN (right).

used for the OUTER JOIN (right).

SELECT

SELECT P.PROD_DESCP.PROD_DESC, , O.QTYO.QTYFROM FROM PRODUCTS_TBL

PRODUCTS_TBLP, P, ORDERS_TBLORDERS_TBLOO WHERE

WHERE P.PROD_IDP.PROD_ID= O.PROD_ID= O.PROD_ID(+); (+);

It will display all records of Products table and only It will display all records of Products table and only matching records from Orders Table.

matching records from Orders Table.

©

Right Outer Join

SELECT

WHERE P.PROD_IDP.PROD_ID(+) = O.PROD_ID(+) = O.PROD_ID; ; SELECT

SELECT P.PROD_DESCP.PROD_DESC, , O.QTYO.QTYFROMFROM PRODUCTS_TBL

PRODUCTS_TBLP RIGHT OUTER JOIN ORDERS_TBLP RIGHT OUTER JOIN ORDERS_TBLOO on

on P.PROD_IDP.PROD_ID= O.PROD_ID= O.PROD_ID; ; SELECT

PRODUCTS_TBLP RIGHT join ORDERS_TBLP RIGHT join ORDERS_TBLOO USING (

USING (PROD_IDPROD_ID););

Note: IN KEYWORD RIGHT OUTER JOIN AND

LEFT OUTER JOIN The word OUTER Is Optional.

(10)

©

Left Outer Join

SELECT

WHERE P.PROD_IDP.PROD_ID= O.PROD_ID= O.PROD_ID(+); (+); SELECT

SELECT P.PROD_DESCP.PROD_DESC, , O.QTYO.QTYFROMFROM PRODUCTS_TBL

PRODUCTS_TBLP LEFT OUTER JOIN ORDERS_TBLP LEFT OUTER JOIN ORDERS_TBLOO on

on P.PROD_IDP.PROD_ID= O.PROD_ID= O.PROD_ID; ; SELECT

PRODUCTS_TBLP Left join ORDERS_TBLP Left join ORDERS_TBLOO USING (

(11)

©

(12)

©

(13)

©

Self Join

Self JoinsSelf Joins

The SELF JOIN is used to join a table to itself, The SELF JOIN is used to join a table to itself,

as if the table were two tables, temporarily as if the table were two tables, temporarily renaming at least one table in the SQL renaming at least one table in the SQL statement. The syntax is as follows. statement. The syntax is as follows.

The following is an example:The following is an example:

SELECT

SELECT A.LAST_NAMEA.LAST_NAME, , B.LAST_NAMEB.LAST_NAME,, A.FIRST_NAME

A.FIRST_NAME FROM

FROM EMPLOYEE_TBLEMPLOYEE_TBLA, EMPLOYEE_TBLA, EMPLOYEE_TBL BB WHERE

WHERE A.LAST_NAMEA.LAST_NAME= B.LAST_NAME= B.LAST_NAME;;

©

Multi table Joins

Joining on Multiple Keys

We may have a table that has a primary

key that is comprised of more than one

column. You may also have a foreign key

in a table that consists of more than one

column, which references the multiple

column primary key.

(14)

©

Consider the following Oracle tables that are Consider the following Oracle tables that are

used here for examples only: used here for examples only:

SQL> SQL> descdescprod prod

Name Null? Type

--- --- --- ---SERIAL_NUMBER

SERIAL_NUMBER NOT NULL NUMBER(10)NOT NULL NUMBER(10)

VENDOR_NUMBER

VENDOR_NUMBER NOT NULL NUMBER(10)NOT NULL NUMBER(10) PRODUCT_NAME

PRODUCT_NAME NOT NULL VARCHAR2(30)NOT NULL VARCHAR2(30)

COST NOT NULL NUMBER(8,2)

SQL>

SQL> descdescordord

Name Null? Type

--- --- ---

---ORD_NO

ORD_NO NOT NULL NUMBER(10)NOT NULL NUMBER(10)

PROD_NUMBER

PROD_NUMBER NOT NULL NUMBER(10)NOT NULL NUMBER(10)

VENDOR_NUMBER

VENDOR_NUMBERNOT NULL NUMBER(10) NOT NULL NUMBER(10) QUANTITY NOT NULL NUMBER(5)

QUANTITY NOT NULL NUMBER(5)

ORD_DATE

ORD_DATE NOT NULL DATE NOT NULL DATE

The primary key in PROD is the combination of the The primary key in PROD is the combination of the columns

columns SERIAL_NUMBERSERIAL_NUMBERand VENDOR_NUMBERand VENDOR_NUMBER. .

The foreign key in The foreign key in ORDORDis also the combination of the is also the combination of the columns

columns SERIAL_NUMBERSERIAL_NUMBERand VENDOR_NUMBERand VENDOR_NUMBER..

When selecting data from both tables (PROD and When selecting data from both tables (PROD and ORDORD), ),

the join operation may appear as follows: the join operation may appear as follows:

SELECT P.PRODUCT_NAME, O.ORD_DATE, O.QUANTITYFROM PROD P, ORD O

WHERE P.SERIAL_NUMBER = O.SERIAL_NUMBER AND

(15)

Creating VIEWS

Improve Security Through Views

©

A view

• is often referred to as a virtual table

• allows a user to see a customized selection of one or

more tables

• is stored as an SQL query, which is executed

whenever the view is used

• reflects the current state of the database •can be treated as another table (with special

(16)

©

A view

• is created using the create view command • is displayed using a normal select command • can be referred to by a select, insert, update

or delete command

• is dropped using the drop view command

4

create view dept30_emps as

(select empno, ename, job, mgr from emp

where deptno = 30);

select * from dept30_emps;

query

view name

(17)

emp table

dept30_emps view

create view annual_costs as

(select empno, ename, comm, sal*12 annual_sal

from emp);

query

view name

(18)

©

emp table

annual_costs view

8

All columns produced using expressions such

**as sal*12 must be given aliases:**

create view annual_costs as (select empno, ename, comm, sal*12 annual_sal

(19)

©

Any other columns may also be given aliases:

create view annual_costs as (select empno, ename name, comm commission,

sal*12 annual_sal from emp);

©

(20)

©

All the columns may be given aliases as

follows:

create view annual_costs (employee, name,

commission, annual_sal) as

(select empno, ename, comm, sal*12 from emp);

(21)

©

The

order by

clause may not be used in

creating a view as the order of rows of a table

or view is not defined.

After the view is created, a query can be

written to display the view in the required

order:

select * from annual_costs order by commission, annual_sal;

©

(22)

©

Views are often used to collect summary

data, e.g.

create view dept_size (department, no_of_emps) as

(select dname, count(empno) from dept, emp Where

dept.deptno = emp.deptno (+) group by dname);

(23)

©

A view

• provides an additional level of security – different groups of users have different

database privileges

– a view can be used to control the

information the user has access to

©

A view

• can be used to convert units

– e.g. to see salary in terms of £ rather than $,

(24)

©

A view

• can be treated as a table in its own right • can be used in a query

• can be joined to another table or view

29

Modifying data in a view:

•

you cannot use delete on multiple-table views

• you cannot use insert unless all the non-null

columns in the underlying table are included in the view

• records updated through a multiple-table view must

belong to the same underlying table

• records cannot be inserted or updated through a

view which was defined using distinct

(25)

The END

Introduction to SQL

First 1OHT Course Review

Chapter 1Chapter 1 – – SQL FundamentalsSQL Fundamentals Chapter 2Chapter 2 –

– SQL Plus OverviewSQL Plus Overview

Chapter 3Chapter 3 –

– Single row functionsSingle row functions

– Aggregating data and single row functionsAggregating data and single row functions

(26)

©

– Modifying Data (Insert, update, delete)Modifying Data (Insert, update, delete)

– Managing tables (alter table, constraints)Managing tables (alter table, constraints)

– Working with viewsWorking with views

– Creating users, granting access to users etcCreating users, granting access to users etc

BOOK Æ Introduction to Oracle 9i SQL Book topics covered approx 75%