Mastering in SQLite

Set Operations and CASE Query

Published in

IVYMobility TechBytes

3 min readApr 24, 2020

Before getting into Set operators and case query, if you haven’t covered the previous topic - Grouping Data, then get into it.

To visit the introduction page to see the available topics click here.

A number of superheroes are available in our DB, click here to download it.

SET OPERATIONS

Set operations allow the results of multiple queries to be combined into a single result set. Set operators include

UNION
INTERSECT
EXCEPT

UNION

Sometimes, you need to combine data from multiple tables into a complete result set. It may be for tables with similar data within the same database or maybe you need to combine similar data from multiple databases.

Both UNION and UNION ALL operators combine rows from result sets into a single result set. The UNION operator removes eliminate duplicate rows, whereas the UNION ALL operator does not.

The following are rules to union data:

The number of columns in all queries must be the same.
The corresponding columns must have compatible data types.
The column names of the first query determine the column names of the combined result set.

Syntax

SELECT select_list1 
FROM table1 
UNION 
SELECT select_list2 
FROM table2

Let’s use UNION operator to combine two tables

SELECT
     id,
     name
 FROM
     charactersDetails
 UNION
 SELECT
     id,
     name
 FROM
     characterList;

Similarly using UNION ALL operator

SELECT
     id,
     name
 FROM
     charactersDetails
 UNION ALL
 SELECT
     id,
     name
 FROM
     characterList;

EXCEPT

SQLite EXCEPT operator compares the result sets of two queries and returns distinct rows from the left query that are not output by the right query.

This query must conform to the following rules:

First, the number of columns in the select lists of both queries must be the same.
Second, the order of the columns and their types must be comparable.

Syntax

SELECT select_list1 
FROM table1 
EXCEPT
SELECT select_list2 
FROM table2

Example for EXCEPT statement is

SELECT
     name
 FROM
     characterList
 EXCEPT
 SELECT
     name
 FROM
     charactersDetails;

INTERSECT

SQLite INTERSECT operator compares the result sets of two queries and returns distinct rows that are output by both queries.

The basic rules for combining the result sets of two queries are as follows:

First, the number and the order of the columns in all queries must be the same.
Second, the data types must be comparable.

Syntax

SELECT select_list1 
FROM table1 
INTERSECT
SELECT select_list2 
FROM table2

To intersect data of two tables,

SELECT
     name
 FROM
     characterList
 INTERSECT
 SELECT
     name
 FROM
     charactersDetails;

CASE

The SQLite CASE expression evaluates a list of conditions and returns an expression based on the result of the evaluation.

The CASE expression is similar to the IF-THEN-ELSE statement in other programming languages.

You can use the CASE expression in any clause or statement that accepts a valid expression.

Syntax

CASE case_expression      
            WHEN when_expression_1 THEN result_1      
            WHEN when_expression_2 THEN result_2     
            ...       
            [ ELSE result_else ]   
END

Example for CASE expression

SELECT
     id,
     name,
     superPower,
     CASE
         WHEN salary > 100000 THEN 'High paid'
         ELSE 'Low paid'
     END category
 FROM
     charactersDetails;