Basic SQL for Testers : SET Operators
SET operators are mainly used to combine the same type of data from two or more tables. Although more than one select statement will then be present, only one result set is returned.
Rules on Set Operations:
1. The result sets of all queries must have the same number of columns.
2. In every result set the data type of each column must match the data type of its corresponding column in the first result set.
3. In order to sort the result, an ORDER BY clause should be part of the last statement.
4. The records from the top query must match the positional ordering of the records from the bottom query.
5. The column names or aliases must be found out by the first select statement.
Four Set Operators:
1. UNION ALL
The SQL UNION ALL Operator is used to list all records from two or more select statements. All the records from both tables must be in the same order.
Syntax- SELECT CustomerId,Name FROM Customers UNION ALL SELECT CustomerId,Name FROM CustomerOld
The SQL Union ALL Operator is used to combine two tables using select statement when both tables have the same number of columns.
Union works like Distinct. Union all DOES NOT do distinct.
Syntax- SELECT CustomerId,Name FROM Customers UNION SELECT CustomerId,Name FROM CustomerOld
INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
Syntax – SELECT CustomerId,Name FROM Customers INTERSECT SELECT CustomerId,Name FROM CustomerOld
EXCEPT clause in SQL Server is working as like MINUS operation in Oracle. EXCEPT query returns all rows which are in the first query but those are not returned in the second query.
Syntax- SELECT CustomerId,Name FROM Customers EXCEPT SELECT CustomerId,Name FROM CustomerOld