🔍 How to Verify Inserted Rows Using a CTE in SQL (WITH expected AS Pattern)

🔍 How to Verify Inserted Rows Using a CTE in SQL (

WITH expected AS

Pattern)

Whether you’re writing seeders, migration scripts, or integration tests — verifying that specific records were inserted into a table is a common and critical task.

Let me show you a clean and portable SQL technique using a Common Table Expression (CTE) to check that a set of expected rows exists in your database — without writing 6 separate

SELECT

queries or messing with application-level checks.

✅ Use Case

Imagine you just ran this

INSERT

into a table called

template

INSERT INTO template (service__id, id, os__id, ...)
VALUES
  ('wpsquared', 'cloudlinux8-wp2-v1', 'cloudlinux-8', ...),
  ('wpsquared', 'cloudlinux9-wp2-v1', 'cloudlinux-9', ...),
  ('wpsquared', 'cloudlinux9-wp2-v2', 'cloudlinux-9', ...),
  ('wpsquared', 'cloudlinux9-wp2-v3', 'cloudlinux-9', ...),
  ('plesk',     'ubuntu2204-plesk-v1', 'ubuntu-22.04', ...),
  ('cpanel',    'ubuntu2404-cpanel-v1', 'ubuntu-24.04', ...);

Now you want to confirm that all 6 rows exist.

🧠 The Pattern:

WITH expected AS (...)

Here’s the SQL snippet:

-- Step 1: Define the rows we expect
WITH expected AS (
    SELECT 'wpsquared' AS service__id, 'cloudlinux8-wp2-v1'  AS id UNION ALL
    SELECT 'wpsquared', 'cloudlinux9-wp2-v1'  UNION ALL
    SELECT 'wpsquared', 'cloudlinux9-wp2-v2'  UNION ALL
    SELECT 'wpsquared', 'cloudlinux9-wp2-v3'  UNION ALL
    SELECT 'plesk',     'ubuntu2204-plesk-v1' UNION ALL
    SELECT 'cpanel',    'ubuntu2404-cpanel-v1'
)

-- Step 2: Check which of them are missing
SELECT  e.service__id,
        e.id
FROM    expected e
LEFT JOIN template t
       ON  t.id = e.id
       AND t.service__id = e.service__id
WHERE   t.id IS NULL;

📋 Result

If the query returns 0 rows → ✅ You’re good! All expected rows are present.
If any rows are returned → ⚠️ These are missing or mismatched by primary key or foreign key.

🔄 Bonus: Quick Boolean Check

Want a compact version that tells you how many rows are missing?

WITH expected AS (
    -- same as above
)
SELECT COUNT(*) AS missing_count
FROM   expected e
LEFT   JOIN template t
       ON t.id = e.id AND t.service__id = e.service__id
WHERE  t.id IS NULL;

🧬 Cross-Database Compatibility

This pattern works on:

✅ PostgreSQL
✅ MySQL / MariaDB
✅ SQLite
✅ SQL Server 2005+ (with some minor syntax adaptation)

The key is that all of these databases support CTEs (Common Table Expressions) and

LEFT JOIN

logic.

🧑‍💻 When Should You Use This?

To test database seeders
To verify data migration scripts
During CI pipelines or test automation
For quick manual checks in staging or dev

📎 Final Tip

You can also extend this pattern to check not just existence, but field-level equality, version numbers, or even timestamps — just join on more columns and compare.

If you liked this trick, follow me or join my community [insert your Reddit, blog, or channel link here] where I share more real-world PHP + SQL tips like this one!

🔍 How to Verify Inserted Rows Using a CTE in SQL (WITH expected AS Pattern)