SQL CASE is used to provide conditional (if-else) logic in SQL statements.
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.
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.
SQL EXISTS is used to test if a subquery returns a row or not. If yes, then the outer query proceeds, if not, the outer query stops and no row returned.
SQL FULL JOIN clause is used to select all the matched records from either left or right side table.