{[['
']]}
']]}
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.
Post a Comment