Cheat sheet
The following is a cheat sheet of the most common operations in SQL as programmed in delightql. For a complete list, please see the language reference. Notably, this list does not include things that delightql can do that SQL has no easy representation for. For a list of those, see new features.:
SELECT
users(*)
|> (first_name, last_name as LN, email)
SELECT "first_name" AS "first_name", "last_name" AS "LN", "email" AS "email"
FROM "users"
DISTINCT
users(*) |> %(country,status)
SELECT DISTINCT "country" AS "country",
"status" AS "status",
FROM "users"
GROUP BY
orders(*)
|> %(status ~> count:(%user_id))
SELECT "status" AS "status", count(DISTINCT("user_id")) AS "count_2"
FROM "orders"
GROUP BY "status"
WHERE
users(*), age > 25, country = "USA"
SELECT *
FROM "users"
WHERE ("age" > 25 AND "country" IS NOT DISTINCT FROM 'USA')
HAVING
users(*) |> %(country ~> count:(*) as user_count), user_count > 10
SELECT *
FROM (
SELECT "country" AS "country", count(*) AS "user_count"
FROM "users"
GROUP BY "country"
) AS "t0"
WHERE "user_count" > 10
INNER JOIN ON
main.users(*) as u, main.orders(*) as o,
u.id = o.user_id
SELECT *
FROM "users" AS "u"
INNER JOIN "orders" AS "o" ON "u"."id" IS NOT DISTINCT FROM "o"."user_id"
INNER JOIN USING
users(*),
orders(*.(status,created_at))
SELECT *
FROM "users"
INNER JOIN "orders" USING ("status", "created_at")
OUTER JOIN ON
users(*) as u, orders?(*) as o, u.id = o.user_id
SELECT *
FROM "users" AS "u"
LEFT JOIN "orders" AS "o" ON "u"."id" IS NOT DISTINCT FROM "o"."user_id"
SELECT (qualified)
orders(*) as o, users(*) as u,
o.user_id = u.id
|> (u.last_name as ln,
u.first_name,
age + 20 as age_20,
o.id,
o.total)
SELECT
u.last_name AS ln,
u.first_name AS first_name,
u.age + 20 AS age_20,
o.id AS id,
o.total AS total
FROM orders AS o
JOIN users AS u ON o.user_id = u.id;
VALUES (inline data)
_(
product,price,quantity
-------------------------
"Laptop",999.99,5;
"Mouse",29.99,15;
"Keyboard",79.99,10
)
SELECT *
FROM (
SELECT 'Laptop' AS "product", 999.99 AS "price", 5 AS "quantity"
UNION ALL
SELECT 'Mouse', 29.99, 15
UNION ALL
SELECT 'Keyboard', 79.99, 10
) AS "t0"
IN
users(*), status in ("active"; "pending"; "suspended")
SELECT *
FROM "users"
WHERE "status" IN ('active', 'pending', 'suspended')
users(*), (age, status) in (25, "active"; 30, "pending"; 35, "inactive")
SELECT *
FROM "users"
WHERE ((("age" = 25 AND "status" = 'active') OR ("age" = 30 AND "status" = 'pending')) OR ("age" = 35 AND "status" = 'inactive'))
CASE SEARCH
age_bucket:(a) :
_:(a>64 -> "older age";
a>0,a<20 -> "young";
a>=20, a<=40 -> "adult")
users(*)
|> (first_name, last_name, age_bucket:(age))
SELECT
users.first_name AS first_name,
users.last_name AS last_name,
CASE
WHEN users.age > 64 THEN 'older age'
WHEN users.age > 0
AND users.age < 20 THEN 'young'
WHEN users.age >= 20
AND users.age <= 40 THEN 'adult'
END AS age_bucket_3
FROM users;
LIMIT
users(*), #<5
SELECT *
FROM "users"
LIMIT 5
ORDER BY
users(*)
|> #(age descending,last_name ascending)
select
*
from users order by age desc, last_name asc;
EXISTS
users(*) as u,
+orders(, u.id = o.user_id) as o
SELECT *
FROM "users" AS "u"
WHERE EXISTS (SELECT 1
FROM (
SELECT *
FROM "orders" AS "o"
WHERE "u"."id" IS NOT DISTINCT FROM "o"."user_id"
) AS "t0")
NOT EXISTS
users(*) as u,
\+orders(, u.id = o.user_id) as o
SELECT *
FROM "users" AS "u"
WHERE NOT EXISTS (SELECT 1
FROM (
SELECT *
FROM "orders" AS "o"
WHERE "u"."id" IS NOT DISTINCT FROM "o"."user_id"
) AS "t0")
UNION ALL
users_2024(|> (first_name,last_name,email))
||
users_2023(|> (first_name,last_name,email))
SELECT
users_2024.first_name AS first_name,
users_2024.last_name AS last_name,
users_2024.email AS email
FROM users_2024
UNION ALL
SELECT
users_2023.first_name AS first_name,
users_2023.last_name AS last_name,
users_2023.email AS email
FROM users_2023;