{"repo":"ben-nour/SQL-tips-and-tricks","free":true,"listed":false,"github":"https://github.com/ben-nour/SQL-tips-and-tricks","clone":"git clone https://github.com/ben-nour/SQL-tips-and-tricks.git","description":"SQL tips and tricks","language":"SQL","stars":2302,"topics":["sql","tips-and-tricks","tips","snowflake","mysql","sqlite"],"license":"MIT","category":"databases-storage","readme_excerpt":"SQL tips and tricks A (somewhat opinionated) list of SQL tips and tricks that I've picked up over the years. There's so much you can do with SQL but I've focused on what I find most useful in my day-to-day work as a data analyst and what I wish I had known when I first started writing SQL. Please note that some of these tips might not be relevant for all RDBMSs. Table of contents Formatting/readability - Use a leading comma to separate fields - Use a dummy value in the WHERE clause - Indent your code - Consider CTEs when writing complex queries - Comment your code - Simplify joins with USING Data wrangling - Anti-joins will return rows from one table that have no match in another table - Use QUALIFY to filter window functions - You can (but shouldn't always) GROUP BY column position - Create a grand total with GROUP BY ROLLUP - Use EXCEPT to find the difference between two tables Performance - NOT EXISTS is faster than NOT IN if your column allows NULL - Implicit casting will slow down (or break) Common mistakes - Be aware of how NOT IN behaves with NULL values - Avoid ambiguity when naming calculated fields - Always specify which column belongs to which table Miscellaneous - Understand the order of execution - Read the documentation (in full) - Use descriptive names for your saved queries Formatting/readability Use a leading comma to separate fields Use a leading comma to separate fields in the SELECT clause rather than a trailing comma. - Clearly defines that this is a new ","default_branch":null,"files":null,"tree":[],"storefront":"/r/ben-nour","claimed":false,"request_supported":{"post":"https://gitbuyer.com/r/ben-nour/SQL-tips-and-tricks/request-supported","requests":0},"note":"indexed from public GitHub; nothing is for sale on this page. Clone it from GitHub. Paid listings live at /search."}