SQL has many unique commands that help simplify database commands. SQL’s UNION command allows us to combine the results of two or more database queries that are not necessarily linked through a database relationship. For example, if we have a school database and wish to use it to create a master contact list for all students, faculty and staff that could be difficult. Looking at our database, we can discover that the records corresponding to each of these constituencies appears in separate database tables.
Our first impulse might be to export the data from each table into a spreadsheet and combine the information there. However, an easier way would be to combine these records within the database by writing separate queries for each constituency and combining the results of those queries with the UNION statement. The syntax of the UNION statement is the following: SELECT_Query_1 UNION [ALL] SELECT_Query_2 The purpose of the SQL UNION command is to combine the results of two queries together. UNION is somewhat similar to the JOIN command in that they are both used to related information from multiple tables.
This command does have some restrictions. 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. Also, each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types and all of the columns in each SELECT statement must be in the same order. There is also a UNION ALL command (Rob and Coronel (2009). 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. The syntax is as follows: [SQL Statement 1] UNION [ALL] [SQL Statement 2] (SQL Book) Another helpful SQL command is 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, the INTERSECT command acts as an AND operator in that the value is selected only if it appears in both statements (Rob and Coronel (2009).
It also 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 operator. The basic rules for combining the result sets of two queries that use INTERSECT is that the number and the order of the columns must be the same in all queries and also that the data types must be compatible. The syntax is the following: [SQL Statement 1] INTERSECT [SQL Statement 2] (SQL Book).
Another helpful SQL command is the EXCEPT command. The EXCEPT or MINUS command operates on two SQL statements. It takes all the results from the first SQL statement, and then subtracts out the ones that are present in the second SQL statement to get the final answer( BLACK WASP). If the second SQL statement includes results not present in the first SQL statement, then the 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 that the number and the order of the columns must be the same in all queries and also that the data types must be compatible(Rob and Coronel (2009). References Rob, P. , & Coronel, C. (2009). Database Systems Design, Implementation, and Management (8th ed. ). Boston, MA: Thomson Course Technology. SQL Book. Retrieved from http://www. sqlbook. com/SQL/SQL-UNION-and-UNION-ALL-32. aspx BLACK WASP. Retrieved from http://www. blackwasp. co. uk/SQLExceptIntersect. aspx
