SQL CASE is used to provide conditional (if-else) logic in SQL statements.
SQL Syntax
SQL Select Statements
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.
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.
SQL UNION ALL
The SQL UNION ALL query is similar to SQL UNION query, it’s also used to merge two or more SQL SELECT query results. The difference is that SQL UNION returns distinct values, while SQL UNION ALL returns ALL the values.
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.
SQL OUTER JOIN
Unlike SQL INNER JOIN, SQL OUTER JOIN selects rows from 2 joined tables even there is no matches found.
There are three kind of SQL OUTER JOINs:
SQL LEFT OUTER JOIN
SQL RIGHT OUTER JOIN
SQL FULL OUTER JOIN
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.
SQL SELF JOIN
SQL SELF JOIN is a normal join which joins a table to itself.
Note: SQL SELF JOIN can be any kind of joins: SQL INNER JOIN, SQL LEFT JOIN, SQL RIGHT JOIN, SQL FULL JOIN. And that SQL SELF JOIN itself is not an SQL keyword.
SQL FULL JOIN
SQL FULL JOIN clause is used to select all the matched records from either left or right side table.
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.