Sql Server material 18

Set Operators: Combines the results of two or more queries into a single result set.

The following are basic rules for combining the result sets of two queries by using SET Operators:

• The number and the order of the columns must be the same in all queries.
• The data types must be compatible.

UNION: Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

            SELECT JOB FROM EMP WHERE DEPTNO=10

            UNION

            SELECT JOB FROM EMP WHERE DEPTNO=30

UNION ALL: These is same as UNION but in this case duplicates will not be eliminated.

            SELECT JOB FROM EMP WHERE DEPTNO=10

            UNION ALL

            SELECT JOB FROM EMP WHERE DEPTNO=30

INTERSECT: Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

            SELECT JOB FROM EMP WHERE DEPTNO=10

            INTERSECT

SELECT JOB FROM EMP WHERE DEPTNO=30

EXCEPT: Returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query.

            SELECT JOB FROM EMP WHERE DEPTNO=10

            EXCEPT

SELECT JOB FROM EMP WHERE DEPTNO=30


<<< Previous                                                                                                             Next >>>