Post #1554

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 ❤️