Written Assignment 3
Explain the SQL commands Union, Intersect, and Minus with concepts like union-compatibility, and syntax alternatives such as IN/NOT IN and various JOIN options
Database Management CIS-311

There are many different SQL commands. This assignment will focus on UNION, INTERSECT, and MINUS. UNION combines unique rows returned by two SELECT statements. UNION ALL functions in the same way as UNION except that it also returns duplicates. INTERSECT gives you rows that are found in both queries by eliminating rows that are only found in one or the other. An INTERSECT is simply an inner join where we compare the tuples of one table with those of the other, and select those that appear in both while weeding out duplicates. MINUS returns the rows that are in the first query but not the second by removing the rows that are only found in the second query. There are three primary SQL commands involved when implementing a Union, Intersection and difference relational operators. As you may know, SQL data manipulation commands are set-oriented which are involved in operating over entire sets of rows and columns in tables at once. The UNION, INTERSECT, and MINUS statements make sure these operations occur. Union, Intersect and Minus only work properly if relations are Union-Compatible, which is based on the names of the relation attributes that must be the same and their data types must be alike. Being compatible does not mean the data types have to be exactly the same. For example, both data types can be used to store numeric values such as NUMBER and SMALLINT as well as character (string) values such as VARCHAR and CHAR. An SQL IN statement can be used with where clause to list a set of matching records of a table. We can use SQL IN query with a NOT combination to filter out some records. We can link or collect data from two different tables using the IN command. An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. One common type of join is INNER JOIN. This is a simple join. An SQL INNER JOIN return all rows from multiple tables where the join condition is met. A LEFT JOIN returns all rows where there is at least one match in both tables. A LEFT JOIN returns all rows from the left table and matching rows from the right table. A RIGHT JOIN returns all rows from the right table and matching rows from the left table. A FULL JOIN returns all rows when there is a match in one of the tables. Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join. An EQUI JOIN performs a JOIN against equality or matching column, or columns values of the associated tables and an equal sign is used as comparison operator in the WHERE clause to refer equality. The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with same name of associate tables will appear once only In a NATURAL JOIN, the associated tables have one or more pairs of identically named columns and of the same data type.The ON clause is not used in a NATURAL JOIN.

NOTE: Since all information contained in this assignment is found in more than 3 sources, which makes it common knowledge, I did not use citations.