SQL: Union and Case

SQL: Union and Case

What are union and case? How do we use them in SQL?

Union

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!

Syntax

Union

-- 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

-- 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

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 'Ozgur' or 'Ahmet'.

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