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