Discuss in Forum
The keyword ‘ALL’ used along with ‘UNION’ is not synonymous with just the ‘UNION’ statement. It produces the duplicate rows, if they exist, from the combination of the two tables in the SELECT query.
The keyword ‘DISTINCT’ used along with ‘UNION’ is synonymous with just the ‘UNION’ statement. It produces only the distinct rows from the combination of the two tables in the SELECT query.
SELECT name, id FROM person UNION name, salary FROM Employee;
Even if the columns ‘id’ and ‘salary’ have different data types, the results from these columns are placed into the column ‘id’. The data types can be determined from the values in the columns.
The operators ‘ALL’ and ‘ANY’ are used to perform operations on columns. They are used in conjunction with a comparison operator in order to test the result of a column subquery.
When there is a need to evaluate multiple rows in comparison to the outer query, the ‘IN’ and ‘NOT IN’ operators are used. They are used for testing whether a comparison value is present in a set of values.