Union of 2 tables A and B,is a table containing all rows that are in either A or B both Intersection of 2 tables A and B,is a table containing all rows that are common to both tables A and B
Difference of 2 tables A and B,is a table containing all Rows that are in table A but are not in table B
In Order for tables to be combined using the set operations,the tables have to be union-compatible.This means that the two tables need to have the same number of columns come from the same domain.
Example-No use in combining a column containing the age of staff with the number of rooms in property ,even thought both columns may have the same data type.
SQL UNION
The purpose of the SQL UNION command is to combine the results of two queries together. In this respect, UNION is somewhat similar to
JOIN in that they are both used to related information from multiple tables. One restriction of UNION is that all corresponding columns need
to be of the same data type. Also, when using UNION, only distinct values are selected (similar to SELECT DISTINCT).
The purpose of the SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL
and UNION is that, while UNION only selects distinct values, UNION ALL selects all values.
syntax
[SQL Statement 1]
UNION [ALL][SQL Statement 2]
SQL INTERSECT
Similar to the UNION command, INTERSECT also operates on two SQL statements. The difference is that, while UNION essentially acts as an
OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator
(value is selected only if it appears in both statements). Returns distinct values by comparing the results of two queries.
Similar to the UNION command, INTERSECT also operates on two SQL statements. The difference is that, while UNION essentially acts as an
OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator
(value is selected only if it appears in both statements). Returns distinct values by comparing the results of two queries.
INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
The basic rules for combining the result sets of two queries that use INTERSECT are the following
- The number and the order of the columns must be the same in all queries.
- The data types must be compatible.
syntax
[SQL Statement 1]
INTERSECT
[SQL Statement 2]
SQL EXCEPT
The EXCEPT (MINUS) operates on two SQL statements. It takes all the results from the first SQL statement, and then subtract out the ones
that are present in the second SQL statement to get the final answer. If the second SQL statement includes results not present in the first
SQL statement, such results are ignored.
EXCEPT returns any distinct values from the left query that are not also found on the right query.
The basic rules for combining the result sets of two queries that use EXCEPT are the following
The EXCEPT (MINUS) operates on two SQL statements. It takes all the results from the first SQL statement, and then subtract out the ones
that are present in the second SQL statement to get the final answer. If the second SQL statement includes results not present in the first
SQL statement, such results are ignored.
EXCEPT returns any distinct values from the left query that are not also found on the right query.
The basic rules for combining the result sets of two queries that use EXCEPT are the following
- The number and the order of the columns must be the same in all queries.
- The data types must be compatible.
syntax
[SQL Statement 1]EXCEPT[SQL Statement 2]SELECT C1,C2 FROM T1EXCEPTSELECT C1,C2 FROM T2
