Completing the basics
This is part 4 of a four-part article which explains SQL
Content:
NULL values
-
NULL
values are special and treated differently. Look at these comparisons:
comparison | result |
---|---|
NULL = ''
|
NULL |
NULL = 0
|
NULL |
NULL <> ''
|
NULL |
NULL <> 0
|
NULL |
NULL = NULL
|
NULL |
NULL <> NULL
|
NULL |
NULL IS NULL
|
true |
NULL IS NOT NULL
|
false |
- If a column admits
NULL
values, or the result of aLEFT JOIN
orRIGHT JOIN
query (like missing rows in a related table) produceNULL
values, those then need to be treated and compared using theIS
orIS NOT
clauses; as all other comparisons returnNULL
as shown in the chart above
CASE WHEN
- In standard SQL the CASE clause helps to create results using a simple syntax
- Here's an example
SELECT
id,
name,
CASE WHEN name LIKE '%Sport%'
THEN 'Sports'
ELSE 'Formal'
END AS type
FROM products
ORDER BY id;
- This query will produce the following results, using the DB schema defined so far:
id | name | type |
---|---|---|
1 | Sport shoes A | Sports |
2 | Sport watch B | Sports |
3 | Suit C | Formal |
- Several
WHEN ... THEN ...
parts can be concatenated using the sameCASE
clause - After those, the
ELSE
part goes; if there is no ELSE and none of the WHEN parts match, the result for the column is aNULL
- The
END
part must be the last one of the clause
Views
- A View could be created using the previous SELECT
- Here is the view named
products_type
:
CREATE OR REPLACE VIEW products_type AS
SELECT
id,
name,
CASE WHEN name LIKE '%Sport%'
THEN 'Sports'
ELSE 'Formal'
END AS type
FROM products
ORDER BY id;
- The syntax CREATE OR REPLACE is not supported by all DBMS, so consider using instead
CREATE
and if in need to update it, delete it first withDROP VIEW
- This View could be used almost like a table. Consider:
- It adds a calculated column,
type
- It already provides an
ORDER BY
- It adds a calculated column,
SELECT *
FROM products_type
WHERE type = 'Sports';
id | name | type |
---|---|---|
1 | Sport shoes A | Sports |
2 | Sport watch B | Sports |
- Also, a different
ORDER BY
could be used -
Some DBMS allow views to
INSERT
,UPDATE
orDELETE
the underlying table(s), if at all possible, given the query
Aggregate functions at work
- How to obtain the total quantity sold, per month, per product, for year 2020, in just one query?
- Here is the result of the query
id | name | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Sport shoes A | 5 | 0 | 1 | 0 | 0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
2 | Sport watch B | 1 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 |
3 | Suit C | 0 | 1 | 0 | 1 | 2 | 0 | 0 | 0 | 0 | 0 | 2 | 0 |
- The syntax vary a little among DBMS engines, due to differences in the treatment of DATE types
PostgreSQL
SELECT
s.id_product AS id,
p.name,
SUM(CASE WHEN date_part('month', s.date) = '01' THEN s.quantity ELSE 0 END) AS jan,
SUM(CASE WHEN date_part('month', s.date) = '02' THEN s.quantity ELSE 0 END) AS feb,
SUM(CASE WHEN date_part('month', s.date) = '03' THEN s.quantity ELSE 0 END) AS mar,
SUM(CASE WHEN date_part('month', s.date) = '04' THEN s.quantity ELSE 0 END) AS apr,
SUM(CASE WHEN date_part('month', s.date) = '05' THEN s.quantity ELSE 0 END) AS may,
SUM(CASE WHEN date_part('month', s.date) = '06' THEN s.quantity ELSE 0 END) AS jun,
SUM(CASE WHEN date_part('month', s.date) = '07' THEN s.quantity ELSE 0 END) AS jul,
SUM(CASE WHEN date_part('month', s.date) = '08' THEN s.quantity ELSE 0 END) AS aug,
SUM(CASE WHEN date_part('month', s.date) = '09' THEN s.quantity ELSE 0 END) AS sep,
SUM(CASE WHEN date_part('month', s.date) = '10' THEN s.quantity ELSE 0 END) AS oct,
SUM(CASE WHEN date_part('month', s.date) = '11' THEN s.quantity ELSE 0 END) AS nov,
SUM(CASE WHEN date_part('month', s.date) = '12' THEN s.quantity ELSE 0 END) AS dec
FROM sales s
JOIN products p ON s.id_product = p.id
WHERE date_part('year', s.date) = '2020'
GROUP BY s.id_product, p.name
ORDER BY id_product;
MySQL / MariaDB
SELECT
s.id_product AS id,
p.name,
SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '01' THEN s.quantity ELSE 0 END) AS "jan",
SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '02' THEN s.quantity ELSE 0 END) AS "feb",
SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '03' THEN s.quantity ELSE 0 END) AS "mar",
SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '04' THEN s.quantity ELSE 0 END) AS "apr",
SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '05' THEN s.quantity ELSE 0 END) AS "may",
SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '06' THEN s.quantity ELSE 0 END) AS "jun",
SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '07' THEN s.quantity ELSE 0 END) AS "jul",
SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '08' THEN s.quantity ELSE 0 END) AS "aug",
SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '09' THEN s.quantity ELSE 0 END) AS "sep",
SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '10' THEN s.quantity ELSE 0 END) AS "oct",
SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '11' THEN s.quantity ELSE 0 END) AS "nov",
SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '12' THEN s.quantity ELSE 0 END) AS "dec"
FROM sales s
JOIN products p ON s.id_product = p.id
WHERE EXTRACT(YEAR FROM s.date) = '2020'
GROUP BY s.id_product, p.name
ORDER BY s.id_product;
SQLite
SELECT
s.id_product AS id,
p.name,
SUM(CASE WHEN strftime('%m', s.date) = '01' THEN s.quantity ELSE 0 END) AS jan,
SUM(CASE WHEN strftime('%m', s.date) = '02' THEN s.quantity ELSE 0 END) AS feb,
SUM(CASE WHEN strftime('%m', s.date) = '03' THEN s.quantity ELSE 0 END) AS mar,
SUM(CASE WHEN strftime('%m', s.date) = '04' THEN s.quantity ELSE 0 END) AS apr,
SUM(CASE WHEN strftime('%m', s.date) = '05' THEN s.quantity ELSE 0 END) AS may,
SUM(CASE WHEN strftime('%m', s.date) = '06' THEN s.quantity ELSE 0 END) AS jun,
SUM(CASE WHEN strftime('%m', s.date) = '07' THEN s.quantity ELSE 0 END) AS jul,
SUM(CASE WHEN strftime('%m', s.date) = '08' THEN s.quantity ELSE 0 END) AS aug,
SUM(CASE WHEN strftime('%m', s.date) = '09' THEN s.quantity ELSE 0 END) AS sep,
SUM(CASE WHEN strftime('%m', s.date) = '10' THEN s.quantity ELSE 0 END) AS oct,
SUM(CASE WHEN strftime('%m', s.date) = '11' THEN s.quantity ELSE 0 END) AS nov,
SUM(CASE WHEN strftime('%m', s.date) = '12' THEN s.quantity ELSE 0 END) AS dec
FROM sales s
JOIN products p ON s.id_product = p.id
WHERE strftime('%Y', date) = '2020'
GROUP BY id_product
ORDER BY id_product;
- In this example, two features of DML are combined together:
-
GROUP BY
, to summarize row values -
CASE WHEN
to obtain calculated values for each month of the year, replacing the quantity with a zero when the month in the date doesn't match the column it's calculated for
-
Final words
It's my hope that this brief introduction to SQL has piqued your interest.
It's a very powerful Domain Specific Language.
Having a basic notion of the SQL fundamentals, in my view, is essential for improving our use of it.
Glossary
Term | a.k.a. | What it is |
---|---|---|
Check Constraint | Check | A Constraint where the values of one or more columns are limited to a specific set |
Constraint |
Set of restrictions in a DB which make the DB consistent; there are several types of constraints, such as: Primary Key, Foreign Keys, Unique Keys, NULL , Check Constraints
|
|
Data Base | DB | A combination of tables, rules, constraints, triggers and stored procedures which is managed by a DBMS |
Data Base Management System | DBMS | The software that administer Data Bases; in this context, an SQL DB |
Data Definition Language | DDL | Defines the schema in a DB |
Data Manipulation Language | DML | Operates on tables in a DB |
Foreign Key | FK | A combination of one or more columns in a table, which point to the PK in a table |
Primary Key | PK |
It's a unique composition of values in a row, which make the row unique; serves as an identification, and no NULL columns are allowed |
Relational Data Base Management | RDBMS | a DBMS which follows the Relational math principles |
Schema | The complete definition of a set of tables, constraints and other objects in a DB | |
Table | Relation | A matrix formed of tuples, each of them consists of the same type of values (each positioned element); if the table has a PK, then it might be considered a Set, from Set Theory, in the sense that each element of the set is a tuple, and there is no more than one tuple contained in the set with the same values |
Trigger | Code that the DBMS executes whenever changes occur on the DB; this code is configured within the DB, and it could be written in SQL or a procedural language within the DBMS; this usually implies difference between DBMS vendors or products | |
Unique Key | UK |
Similar to PK, a unique combination of columns in each row, except that NULL values are accepted |
View |
A projection of one or more tables which produce a table-like result from a SELECT statement; there are different types of views: Read Only, Writable and Materialized |
Top comments (0)