When we need to combine two or more
SELECT statements into a single result set
UNION keyword goes into action. The important thing to note is
Each SELECT statement of UNION operator must have the same number of columns.
The default usage of
UNION removes the duplicate rows, but if we need this duplication we can use
UNION ALL to show them all. A limitation regarding UNION is we can use one
ORDER BY to sort the entire resultset!
-- UNION SELECT table1.Id as Table1Id, table1.Value as Table1Value FROM Table1 table1 UNION SELECT table2.Id as Table2Id, table2.Value as Table2Value FROM Table2 table2 ORDER BY Table1Value DESC
-- UNION ALL SELECT table1.Id as Table1Id, table1.Value as Table1Value FROM Table1 table1 UNION ALL SELECT table2.Id as Table2Id, table2.Value as Table2Value FROM Table2 table2 ORDER BY Table1Value DESC
Case is a very commonplace operator used to format the data with an if-else logic approximating to the programming languages.
It is very simple to use, yet solves a lot of problems:
SELECT CASE Name WHEN 'Ozgur' THEN 'Admin' WHEN 'Ahmet' THEN 'User' ELSE Name END AS NameModified FROM Table1
In this syntax, we take the
Name column of a hypothetical Table1 and modify the results if it has a value of
We can also use multiple statements in a WHEN clause:
SELECT CASE WHEN (age = 11 AND gender = 'male') THEN 'target' WHEN (age = 12 OR age = 15) THEN 'no target' ELSE 'target' END AS 'Result' FROM Customers