Learn CTE in SQL -- The basic bits

Regular SQL select:

SELECT * FROM fleet.node_check_configs WHERE fqdn = 'host-123;`

Start introducing CTE:

WITH result1 AS (
	SELECT * FROM fleet.node_check_configs WHERE fqdn = 'host-123'
)
SELECT * FROM reuslt1

Equal to

SELECT * FROM (
	SELECT * FROM fleet.node_check_configs WHERE fqdn = 'host-123'
) AS result1

Incorrect if you:

SELECT * FROM (
	(SELECT * FROM fleet.node_check_configs WHERE fqdn = 'host-123' ) AS result1
)

You can NOT do: SELECT * FROM ( (subquery) as subquery_result)

You have to: SELECT * FROM (subquery) as subquery_result

Chain (NOTE: There is only one with):

WITH
active_checks AS (
	SELECT * FROM fleet.node_check_configs
	WHERE fqdn = 'host-123' AND deleted_at IS NULL
),
deleted_checks AS (
	SELECT * FROM fleet.node_check_configs
	WHERE fqdn = 'host-123' AND deleted_at IS NOT NULL
)
SELECT * FROM active_checks UNION ALL SELECT * FROM deleted_checks

NOTE

There is only ONE WITH, and there is only ONE ,

Using CTE for update and writes:

  WITH
  deleted AS (
      UPDATE fleet.node_check_configs
      SET deleted_at = NOW()
      WHERE fqdn = 'host-123'
      RETURNING check_id
  )
  SELECT * FROM deleted   -- see what was just deleted

So the deleted table should look like:

check_id
---
check-a
check-b
...

One CTE for ‘just sync’

WITH
incoming AS (
	-- Building a new 'incoming' table with columns:
	-- check_id, check_name, check_interval, ...
	-- This the just building the 'incoming' table
	-- which will be used in follow two steps
	--   - upsert + mark deleted
	SELECT * FROM (VALUES
		('check-a', 'http check', '30s', ...),
		('check-b', 'tcp check' , '15s', ...)
	) AS t(check_id, check_name, check_interval, ...) -- the `t` here is meaningless, you can change it to anything
),
upserted AS (
	-- Using 'incoming', if not exist, insert, if already exist, update
	INSERT INTO fleet.node_check_configs (fqdn, check_id, check_name, ...) 
	SELECT 'host-123' check_id, check_name, ...
	FROM incoming
	  -- 'ON CONFLICT + excluded' -> here is the logic for 'how to handle conflict'
	  -- i.e. the update logic
	ON CONFLICT (fqdn, check_id) DO UPDATE SET
		check_name = excluded.check_name,
		... <many other columns, we need to explain what to do for each of them>
),
unreported AS (
	UPDATE fleet.node_check_configs
	set deleted_at = NOW()
	WHERE fqdn = 'host-123'
		AND deleted_at IS NULL
		AND NOT EXISTS (
			SELECT 1 FROM incoming -- using `SELECT 1` here as we don't need the column values for the 'matched items'
			WHERE incoming.check_id = node_check_configs.check_id
		)
) -- no comma here!
SELECT 1 -- yep, we basically did everything in CTE, but we still need to make this a complete query...so add this.

You can't

WITH incoming AS (VALUES ('check-a', 'http check', '30s', ...), ('check-b', 'tcp check' ,'15s' ...))),

This won’t work. In the CTE you must use complete SELETE statement (or some other statement I guess?), you can’t use VALUES.

But you can

WITH incoming(check_id, check_name, check_interval) AS (
	VALUES
		('check-a', 'http check', '30s'...),
		('check-b', 'tcp check', '15s'...),
)

For the part of:

	UPDATE fleet.node_check_configs
	set deleted_at = NOW()
	WHERE fqdn = 'host-123'
		AND deleted_at IS NULL
		AND NOT EXISTS (
			SELECT 1 FROM incoming -- using `SELECT 1` here as we don't need the column values for the 'matched items'
			WHERE incoming.check_id = node_check_configs.check_id
		)

It can also be written as:

	UPDATE fleet.node_check_configs
	set deleted_at = NOW()
	WHERE fqdn = 'host-123'
		AND deleted_at IS NULL
		AND node_check_configs.check_id NOT IN (
			SELECT check_id FROM incoming
		)

→ This one is much easier to understand.

For the NOT EXISTS, see NOT EXISTS, generally, just 背下来.

Appendix

Insert

Most basic:

  INSERT INTO users (name, age)
  VALUES ('alice', 25)

But VALUES can be replaced by SELECT

Not the other way around

Not all the places of ‘SELECT’ can be replaced by ‘VALUES’

VALUES is just hardcoded literal data. SELECT can do everything VALUES can (by selecting constants), plus it can also pull from tables, CTEs, join things, filter, transform, etc.

With SELECT:

  INSERT INTO users (name, age)
  SELECT name, age FROM new_users

NOT EXISTS

Just always remember this pattern:

  SELECT name FROM students
  WHERE NOT EXISTS (
      SELECT 1 FROM grades
      WHERE grades.student_id = students.id
  )

NOT EXISTS (SELECT 1 FROM ... WHERE ...) IS the pattern. There’s no simpler form — that’s just how the syntax works.

就是背下来就是了

Always in a WHERE statement

NOT EXISTS basically always used in a WHERE state. Select * FROM students WHERE NOT EXISTS (...) UPDATE students SET status = "inactive" WHERE NOT EXISTS (...) DELETE FROM students WHERE NOT EXISTS (...)