Advanced SQL Tutorial

Atomic Counters through Unique Key Checks

Imagine the following table:

CREATE TABLE u(id INT PRIMARY KEY, cnt INT);

INSERT INTO u VALUES (1, 2), (2, 4);

SELECT * FROM u;

+------+------+
| id   | cnt  |
+------+------+
|    1 |    2 |
|    2 |    4 |
+------+------+
3 rows in set (0,001 sec)

If you want to add to cnt whenever an event occurs, add a unique key over id and do the following:

INSERT INTO u VALUES (2, 1) ON DUPLICATE KEY UPDATE cnt = cnt + VALUES(cnt);


+------+------+
| id   | cnt  |
+------+------+
|    1 |    2 |
|    2 |    5 |
+------+------+
3 rows in set (0,001 sec)

This kind of behaviour is atomic, so it is safer than splitting it into two query. This behaviour also scales over multicores via Data Auto Sharding and Auto Indexing

Subselects

MemCP supports subselects in expressions including IN, EXISTS, and scalar subqueries.

IN Subselect

SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE active = 1)

EXISTS Subselect

SELECT * FROM products WHERE EXISTS (SELECT 1 FROM inventory WHERE product_id = products.id)

Scalar Subselect in SELECT

SELECT id, (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count
FROM customers

Scalar Subselect with Aggregation

SELECT (SELECT MAX(price) FROM products WHERE category = 'electronics') AS max_price

Correlated Subselects

Subselects can reference columns from the outer query:

SELECT id, name,
       (SELECT SUM(amount) FROM orders WHERE orders.customer_id = customers.id) AS total
FROM customers

Derived Tables

SELECT t.* FROM (
    SELECT id, COUNT(*) AS cnt FROM orders GROUP BY id
) AS t
WHERE t.cnt > 5

Limitations

• Scalar subselects must return exactly one row (error if multiple rows)
• Scalar subselects return NULL if no rows match