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.

Read more

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.

Read more

SQL UNION

The SQL UNION query is used to merge two or more SQL SELECT query results. Each query statement should have same column structure: same number of columns, same or compatible data types and in same order.

Note: The result of SQL UNION query is distinct.

Read more

SQL EXISTS

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.

Read more

SQL RIGHT JOIN

SQL RIGHT JOIN clause is very similar with SQL LEFT JOIN clause, it is used to select all the matched records from the right side table, regardless if it has matched records in the left side table or not.

Read more