SQL INTERSECT

The SQL INTERSECT query is similar to the SQL UNION query, also works on two SQL Select statements. The difference is that SQL UNION query selects rows either in the first or the second Select statement (like OR operator), while the SQL INTERSECT query selects rows in the both first and second Select statement (like AND operator).

Note: SQL INTERSECT query only returns distinct values.
Continue reading

SQL MINUS, SQL EXCEPT

SQL MINUS query or SQL EXCEPT query is used to subtract out the result of second query from the result of the first qeury. It takes the distinct result set of the first query, then filter out the records which appear in the result set of the second query. If the second query includes the records which are not in the first query, they will be ignored.

The two query statements should have same column structure: same number of columns, same or compatible data types and in same order.

Some databases use SQL MINUS and some databases use SQL EXCEPT. Please check documentation before use them. For some popular databases, here is a quick reference: MS SQL Server uses SQL EXCEPT, Oracle uses SQL MINUS, and mySQL doesn’t support any of them.

Note: The result of SQL MINUS query or SQL EXCEPT query is distinct.

Continue reading