Prayag Verma

Data Engineer. I post what I wish someone told me earlier. SQL • Full Stack • Backend • No fluff, just code.

[Prayag Verma @CodeWithPrayag]

In practice: MERGE shines in ETL pipelines where staging tables feed into production. Common mistake: forgetting to handle WHEN NOT MATCHED BY SOURCE to delete stale rows. Save this. Follow @CodeWithPrayag for more SQL. #SQL #DataEngineering #Analytics

[Prayag Verma @CodeWithPrayag]

SQL Lesson 69: MERGE lets you INSERT, UPDATE, and DELETE in one statement. But most people still write 3 separate queries. Here's how MERGE handles upserts cleanly. Thread below

[Prayag Verma @CodeWithPrayag]

Here's how it works:

MERGE INTO employees AS target
USING staging_employees AS source
ON target.emp_id = source.emp_id
WHEN MATCHED THEN
UPDATE SET target.salary = source.salary
WHEN NOT MATCHED THEN
INSERT (emp_id, name, salary)
VALUES (source.emp_id, sourc...

[Prayag Verma @CodeWithPrayag]

SQL Lesson 69: MERGE does INSERT and UPDATE in one shot. But how does it decide which to run? Thread below

[Prayag Verma @CodeWithPrayag]

SQL Lesson 69: Running separate INSERT and UPDATE for upserts? There's one statement that handles both. Here's how MERGE works. Thread below

[Prayag Verma @CodeWithPrayag]

SQL Lesson 69: MERGE lets you INSERT, UPDATE, and DELETE in one statement. No more separate upsert logic. Here's how it works. Thread below

[Prayag Verma @CodeWithPrayag]

SQL Lesson 69: MERGE lets you INSERT, UPDATE, and DELETE in one statement. No more separate queries for upserts. Thread below

[Prayag Verma @CodeWithPrayag]

In practice: this is a correlated subquery update. Each row in orders is matched individually against payments. Use it when tables share a key but aren't joined directly. Watch for NULLs if no match exists.

Save this. Follow @CodeWithPrayag for more SQL.

#SQL #DataEngineer...

[Prayag Verma @CodeWithPrayag]

SQL Lesson 68: Can you UPDATE rows in one table using values from another table? Most devs don't know the cleanest way to do this. Thread below

[Prayag Verma @CodeWithPrayag]

Here's how it works:

UPDATE orders o
SET total = (
SELECT SUM(amount)
FROM payments p
WHERE p.order_id = https://t.co/zowksqXEXp
);

The subquery runs once per row, pulling the matching value from payments into orders.

[Prayag Verma @CodeWithPrayag]

In practice: use EXISTS when checking if related rows exist, IN for small static lists, JOIN when you need joined columns. Using IN on a million-row subquery is a common performance killer.

Save this. Follow @CodeWithPrayag for more SQL.

#SQL #DataEngineering #Analytics

[Prayag Verma @CodeWithPrayag]

SQL Lesson 67: EXISTS, IN, and JOIN can return the same rows. But they behave very differently under the hood. Which one should you actually use? Thread below

[Prayag Verma @CodeWithPrayag]

Here's the difference:

IN loads a full subquery result into memory.
EXISTS stops at the first match per row, making it faster on large tables.
JOIN is best when you need columns from both tables.

Same output, very different performance.

[Prayag Verma @CodeWithPrayag]

In practice: use EXISTS when checking if related data exists, use IN for small static lists, and use JOIN when you need columns from both tables.

Common mistake: using IN on a subquery that can return NULLs. It silently breaks your results.

Save this. Follow @CodeWithPraya...

[Prayag Verma @CodeWithPrayag]

SQL Lesson 67: EXISTS, IN, and JOIN can all return the same rows. But they behave very differently under the hood. Which one should you actually use? Thread below

[Prayag Verma @CodeWithPrayag]

Here's the difference:

IN loads all subquery values into memory first.
EXISTS stops as soon as it finds a match, short-circuit logic.
JOIN can duplicate rows if the related table has multiple matches.

For large tables, EXISTS often wins on performance.

[Prayag Verma @CodeWithPrayag]

In practice: this often hides in old comma-style FROM clauses or when you add a new table and forget the join key. Your query runs but returns garbage data at massive scale.

Save this. Follow @CodeWithPrayag for more SQL.
#SQL #DataEngineering #Database

[Prayag Verma @CodeWithPrayag]

SQL Lesson 66: A missing JOIN condition can silently multiply your rows into millions. What is a Cartesian product and how do you accidentally create one? Thread below

[Prayag Verma @CodeWithPrayag]

Here's how it happens:

SELECT https://t.co/zowksqXEXp, https://t.co/OsBwjvB0Db
FROM orders o, customers c

No JOIN condition means every order matches every customer. 1000 orders x 500 customers = 500,000 rows. Always add a WHERE or explicit JOIN condition.

[Prayag Verma @CodeWithPrayag]

In practice: this pattern catches missing invoice IDs, broken sensor readings, or skipped order numbers before they cause reporting errors. Common mistake: forgetting to filter deleted or NULL rows first.

Save this. Follow @CodeWithPrayag for more SQL.

#SQL #DataEngineerin...

[Prayag Verma @CodeWithPrayag]

SQL Lesson 65: How do you find missing numbers or consecutive groups in a sequence? Gaps and islands is a classic SQL problem. Thread below

[Prayag Verma @CodeWithPrayag]

Here's how it works:

SELECT id,
id - ROW_NUMBER() OVER (ORDER BY id) AS grp
FROM orders
ORDER BY id;

Rows with the same grp value form an island. A jump in grp means a gap exists.