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

Lesson (8) In Oracle & SQl Server 2008

{[['']]}

Using the Set Operators in Sql server 2008 & Oracle




Using the Set Operators


After completing this lesson, you should be able to do the following:
       Describe set operators
       Use a set operator to combine multiple queries into a single query
       Control the order of rows returned

UNION Operator

The UNION operator returns results from both queries after eliminating duplication.

SELECT     sys_key, description
FROM         INVESTIGATION
union
SELECT     sys_key, latin_desc
FROM         Procedures

The UNION ALL operator returns results from both queries, including all duplication.


INTERSECT Operator

The INTERSECT operator returns rows that are common to both queries.

è  Oracle

SELECT item FROM Dinner
INTERSECT
SELECT item FROM Lunch;

è  Sql Server

You can achieve the same results by using an inner join:

SELECT Dinner.item
FROM Dinner INNER JOIN Lunch
ON Dinner.item = Lunch.item;


MINUS Or EXCEPT Operator















The MINUS operator returns rows in the first query that are not present in the second query.

è  Oracle

SELECT employee_id,job_id
FROM   employees
MINUS
SELECT employee_id,job_id
FROM   job_history;

è  Sql Server

SELECT     sys_key, description
FROM         INVESTIGATION
WHERE     (Group_Type = 1)
EXCEPT
SELECT     sys_key, description
FROM         INVESTIGATION
WHERE     (Group_Type = 1) AND (description = 'CYTOLOGY')


Set Operator Guidelines

       The expressions in the SELECT lists must match in number and data type.
       Parentheses can be used to alter the sequence of execution.
       The ORDER BY clause:
      Can appear only at the very end of the statement
      Will accept the column name, aliases from the first SELECT statement, or the positional notation
       Duplicate rows are automatically eliminated except in UNION ALL.
       Column names from the first query appear in the result.
       The output is sorted in ascending order by default except in UNION ALL.



Share this article :

Post a Comment

Flag Counter

Social Profile

 
Copyright x 2011. By Wael Medhat - All Rights Reserved