What are you looking for ?
Home » » Lesson (6) In Oracle & SQl Server 2008

Lesson (6) In Oracle & SQl Server 2008

{[['']]}


Displaying Data from Multiple Tables in Oracle & Sql server 2008 


Displaying Data from Multiple Tables


After completing this lesson, you should be able to do the following:
• Write SELECT statements to access data from more than one table using equijoins and nonequijoins
• Join a table to itself by using a self-join
• View data that generally does not meet a join condition by using outer joins
• Generate a Cartesian product of all rows from two or more tables

Types of JOINS and its syntax 

Natural or Inner Joins

Natural Joins in Oracle

• The NATURAL JOIN clause is based on all columns in the two tables that have the same name.
• It selects rows from the two tables that have equal values in all matched columns.
• If the columns having the same names have different data types, an error is returned
SELECT department_id, department_name,location_id, city
FROM departments NATURAL JOIN locations ;

Inner joins in Sql Server

SELECT PATIENT.patient_id, PATIENT.patient_sex, GENERAL_COD.latin_desc, GENERAL_COD.local_desc
FROM PATIENT
Inner JOIN GENERAL_COD ONPATIENT.patient_sex =GENERAL_COD.sub_cod WHERE (GENERAL_COD.main_cod = -1000)

USING Clause


• If several columns have the same names but the data types do not match, natural join can be applied using the USING clause to specify the columns that should be used for an equijoin.
• Use the USING clause to match only one column when more than one column matches.
• The NATURAL JOIN and USING clauses are mutually exclusive
SELECT employee_id, last_name, location_id, department_id
FROM employees JOIN departments USING (department_id) ;
• Do not qualify a column that is used in the USING clause.
• If the same column is used elsewhere in the SQL statement, do not alias it
ON Clause
• The join condition for the natural join is basically an equijoin of all columns with the same name.
• Use the ON clause to specify arbitrary conditions or specify columns to join.
• The join condition is separated from other search conditions.
• The ON clause makes code easy to understand.

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id);
 SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON d.location_id = l.location_id;

 Applying Additional Conditions to a Join

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e JOINdepartments d
ON (e.department_id = d.department_id)
AND e.manager_id = 149 ;
SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e JOINdepartments d
ON (e.department_id = d.department_id)
WHERE e.manager_id = 149 ;

Self-Joins

In Oracle

SELECT worker.last_name emp,manager.last_name mgr
FROM employees worker JOINemployees manager
ON (worker.manager_id = manager.employee_id);

IN Sql Server

A self-join is simply a normal SQL join that joins one table to itself. This is accomplished by using table name aliases to give each instance of the table a separate name.



SELECT INVESTIGATION.sys_key AS InvCode,INVESTIGATION.descriptionAS INVESTIGATION,Inv_Group.sys_key ASInv_Group_key,
Inv_Group.description AS Inv_Group
FROM INVESTIGATION INNER JOIN INVESTIGATION AS Inv_Group ONINVESTIGATION.parent =Inv_Group.sys_key

Nonequijoins

SELECT e.last_name, e.salary, j.grade_level
FROM employees e JOINjob_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

OUTER JOIN Joins

INNER Or OUTER Joins

• In SQL:1999, the join of two tables returning only matched rows is called an inner join.

• A join between two tables that returns the results of the inner join as well as the unmatched rows from the left (or right) table is called a left (or right) outer join.

• A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join.

LEFT OUTER JOIN

IN Oracle:

SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFTOUTER JOINdepartments d
ON (e.department_id = d.department_id) ;

 IN Sql Server:

SELECT INVESTIGATION.sys_key AS InvCode,INVESTIGATION.descriptionAS INVESTIGATION,Inv_Group.sys_key ASInv_Group_key,Inv_Group.description ASInv_Group
FROM INVESTIGATION LEFT OUTER JOIN
INVESTIGATION AS Inv_Group ONINVESTIGATION.parent =Inv_Group.sys_key

 RIGHT OUTER JOIN

 IN Oracle:

SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHTOUTER JOINdepartments d
ON (e.department_id = d.department_id) ;


IN Sql Server:

SELECT INVESTIGATION.sys_key AS InvCode,INVESTIGATION.descriptionAS INVESTIGATION,Inv_Group.sys_key ASInv_Group_key,Inv_Group.description ASInv_Group
FROM INVESTIGATION Right OUTER JOIN INVESTIGATION AS Inv_Group ONINVESTIGATION.parent =Inv_Group.sys_key

FULL OUTER JOIN

IN Oracle: 

SELECT e.last_name, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
 

IN Sql Server:

SELECT PATIENT.patient_id, EPISODE.episod_key
FROM PATIENT Full OUTER JOIN EPISODE ON PATIENT.patient_id = EPISODE.PATIENT_ID

 Cartesian Products (Cross joins)

A Cartesian product is formed when:
  •  A join condition is omitted
  • A join condition is invalid
  • All rows in the first table are joined to all rows in the second table
  • To avoid a Cartesian product, always include a valid join condition.
• If the First Table Contains (20 rows) And the Second (8 rows) Cartesian product:    20 x 8 = 160 rows
SELECT last_name,department_name
FROM employees CROSS JOIN departments ;

 


Share this article :

Post a Comment

Flag Counter

Social Profile

 
Copyright x 2011. By Wael Medhat - All Rights Reserved