SQL, the life🩸of databases, can either be your best friend 🤝 or your biggest time sink ⚓. Whether you're wrangling data as a developer 👨💻 or managing databases as an administrator, these 5️⃣ tricks are designed to make your SQL journey smoother 📈 . I have tried these on my own and they have helped me to do things faster and more efficiently.
The WHERE & NULL weird relationship 🫨
SELECT * FROM Customers WHERE NAME = 'SHOBHIT'; ✅
SELECT * FROM Customers WHERE Name = NULL; ❎
SQL treats NULL as a special marker for missing or unknown data, and standard equality comparison (=) doesn't work with NULL.
SELECT * FROM Customers WHERE Name IS NULL; ✅
SELECT * FROM Customers WHERE Name IS NOT NULL; ✅
Undo PRODUCTION Queries 🤩😎
START TRANSACTION;
UPDATE Customers SET membership = true WHERE fees > 5000;
ROLLBACK;
A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. If you encounter any issues or want to cancel the changes made within the transaction, you can use a ROLLBACK command to undo those changes.
However, if you don't face any issues, you can use COMMIT instead of ROLLBACK to end the transaction.
SARGABLE & NON-SARGABLE QUERIES 🧐🤨🤔
This concept will be more clearly understood if you have worked with indexes. For now, you can consider them as database structures that improve query performance by allowing for faster data retrieval based on specific columns.
-- Search ARGument ABLE
SELECT * FROM customers WHERE LAST_NAME = 'SMITH'
-- Search ARGument NOT ABLE
SELECT * FROM customers WHERE UPPER(last_name) = 'SMITH'
Now, while executing the
Using ORDER BY Clause Efficiently 📈
This is quite a small enhancement but is quite useful when one has to work with SQL queries his/her whole day. (No offense 😝)
SELECT Title, ReleaseDate, Rating
FROM Movies
WHERE ReleaseDate BETWEEN '2010-01-01' AND '2020-12-31'
ORDER BY ReleaseDate DESC 👀
LIMIT 10;
SELECT Title, ReleaseDate, Rating
FROM Movies
WHERE ReleaseDate BETWEEN '2010-01-01' AND '2020-12-31'
ORDER BY 2 DESC 👀
LIMIT 10;
Hence, Instead of naming a particular column, we can just use a number which will depend upon the position of the SQL SELECT query 😊
⨷ Handling Multiple WHERE Conditions ⨷
-- Different Parameters
SELECT *
FROM Customers
WHERE Country = 'USA'
AND City = 'New York'
AND Age >= 25;
-- Don't Need to Add Where Multiple Times
-- Same Parameters
SELECT *
FROM Customers
WHERE Country = 'USA'
AND Country = 'India' ❎ ☹️
--------
SELECT *
FROM Customers
WHERE Country = 'USA'
OR Country = 'India' ✅ 😐
--------
SELECT *
FROM Customers
WHERE Country IN ('USA','INDIA') ✅ 😄
Well, That brings us to the end ⚓️ of the article. If you want more such content do subscribe to my newsletter 📰. Also, you can follow me on Twitter 💙 to get daily updates about the web world 🤗.