SQL Tricks to Level Up Your Database Skills 🚀

SQL is a powerful language, but mastering a few clever tricks can make your queries faster, cleaner, and more efficient. Here are some cool SQL hacks to boost your skills:

1️⃣ Use COALESCE Instead of CASE
Instead of writing a long CASE statement to handle NULL values, use COALESCE():

SELECT COALESCE(name, 'Unknown') FROM users;

This returns the first non-null value in the list.

2️⃣ Generate Sequential Numbers Without a Table
Need a sequence of numbers but don’t have a numbers table? Use GENERATE_SERIES (PostgreSQL) or WITH RECURSIVE (MySQL 8+):

SELECT generate_series(1, 10);

3️⃣ Find Duplicates Quickly
Easily identify duplicate values with GROUP BY and HAVING:

SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

4️⃣ Randomly Select Rows
Want a random sample of data? Use:
- PostgreSQL: ORDER BY RANDOM()
- MySQL: ORDER BY RAND()
- SQL Server: ORDER BY NEWID()

5️⃣ Pivot Data Without PIVOT (For Databases Without It)
Use CASE with SUM() to pivot data manually:

SELECT 
    user_id,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count
FROM users
GROUP BY user_id;

6️⃣ Efficiently Get the Last Inserted ID
Instead of running a separate SELECT, use:
- MySQL: SELECT LAST_INSERT_ID();
- PostgreSQL: RETURNING id;
- SQL Server: SELECT SCOPE_IDENTITY();

Like for more ❤️