Common Tips

A database can be a virtual treasure trove of information, but like the treasure of the Caribbean pirates of long ago, the stuff that you really want is probably buried and hidden from view. The SQL SELECT statement is your tool for digging up this hidden information.

Even if you have a clear idea of what you want to retrieve, translating that idea into SQL can be a challenge. If your formulation is just a little off, you may end up with the wrong results — but results that are so close to what you expected that they mislead you. To reduce your chances of being misled, use the following tips.

Verify the DB Structure

If you retrieve data from a database and your results don't seem reasonable, check the database design.

Why this matters:

• Understanding table relationships helps prevent incorrect joins
• Knowing data types prevents type-related errors
• Understanding constraints helps identify data quality issues
• Reviewing indexes can explain query performance

What to check:

• Entity Relationship Diagrams (ERDs) to understand table relationships
• Table schemas to understand column types and constraints
• Primary and foreign key relationships
• Indexes that might affect query performance

Try Queries on a Test Database

Create a test database that has the same structure as your production database, but with only a few representative rows in the tables. Choose the data so that you know in advance what the results of your queries should be.

Best practices:

• Use small, well-understood datasets for testing
• Include edge cases (NULL values, duplicates, boundary conditions)
• Know the expected results before writing the query
• Test incrementally — start simple, then add complexity

Run each test query on the test data and see whether the results match your expectations. If they don't, you may need to reformulate your queries.

Benefits:

• Catch errors before they affect production data
• Understand query behavior with controlled data
• Build confidence in your SQL skills
• Speed up query development and debugging

Double-Check Queries That Include Joins

Joins are notoriously counterintuitive. If your query contains one, make sure that it's doing what you expect before you add WHERE clauses or other complicating factors.

Common join mistakes:

• Incorrect join type: Using INNER JOIN when you need LEFT JOIN
• Missing join conditions: Forgetting to specify the join key
• Wrong join key: Joining on incorrect columns
• Multiple joins: Not understanding the order of operations

How to verify joins:

1. Start with a simple SELECT * FROM each table separately
2. Add joins one at a time and verify results after each join
3. Check row counts — unexpected increases or decreases indicate issues
4. Verify that NULL values are handled correctly
5. Use EXPLAIN or query plans to understand execution

Example of verifying a join:

-- First, check the individual tables
SELECT COUNT(*) FROM customers;      -- Returns 1000
SELECT COUNT(*) FROM orders;         -- Returns 5000

-- Then check the join result
SELECT COUNT(*) 
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;  -- Should return ~5000, not 1000

-- If you get unexpected counts, investigate the join condition

Triple-Check Queries with Subselects

Queries with subselects (subqueries) take data from one table and, based on what is retrieved, take some data from another table. Therefore, by definition, such queries can really be hard to get right.

Why subqueries are tricky:

• The inner query must return the correct data
• The correlation between inner and outer queries must be correct
• Performance can degrade with complex nested queries
• NULL handling becomes more complex

Best practices for subqueries:

1. Start with the inner query: Write and test the inner SELECT first
2. Verify the inner result: Make sure it returns what you expect
3. Check for NULLs: Ensure NULL values are handled correctly
4. Consider alternatives: Sometimes JOINs or CTEs are clearer and more efficient
5. Test incrementally: Add one level of nesting at a time

Example of testing a subquery:

-- Step 1: Test the inner query first
SELECT customer_id 
FROM orders 
WHERE order_date > '2024-01-01';

-- Step 2: Verify it returns what you expect
-- Step 3: Then use it in the outer query
SELECT * 
FROM customers 
WHERE id IN (
    SELECT customer_id 
    FROM orders 
    WHERE order_date > '2024-01-01'
);

If you have two or more levels of subselects, you need to be even more careful. Consider using Common Table Expressions (CTEs) for better readability and debugging.

Use Parentheses with AND, OR, and NOT

Sometimes when you mix AND and OR, SQL doesn't process the expression in the order that you expect. Use parentheses in complex expressions to make sure that you get the desired results.

SQL operator precedence:

Without parentheses, SQL evaluates operators in this order:

1. NOT
2. AND
3. OR

This means that condition1 AND condition2 OR condition3 is evaluated as (condition1 AND condition2) OR condition3, which may not be what you intended.

Example of potential confusion:

-- Without parentheses (may be confusing)
SELECT * 
FROM orders 
WHERE customer_id = 1 
   OR customer_id = 2 
   AND total > 100;

-- This is evaluated as:
-- WHERE customer_id = 1 OR (customer_id = 2 AND total > 100)

-- With parentheses (clear intent)
SELECT * 
FROM orders 
WHERE (customer_id = 1 OR customer_id = 2) 
  AND total > 100;

-- This clearly means: orders from customer 1 or 2, AND total > 100

Best practices:

• Always use parentheses when mixing AND and OR
• Use parentheses to clarify your intent, even when not strictly necessary
• Group related conditions together
• Typing a few extra keystrokes is a small price to pay for better results

Parentheses also help to ensure that the NOT keyword is applied to the term or expression that you want it to apply to.

Always Document Your Process

The only thing you can be absolutely sure of in this world is change. Count on it. Six months from now, you won't remember why you wrote things the way you did, unless you carefully document what you did and why you did it that way.

What to document:

• Query purpose: What problem does this query solve?
• Business logic: Why did you choose this approach?
• Complex conditions: Explain non-obvious WHERE clauses
• Join rationale: Why join on these columns?
• Assumptions: What assumptions are you making about the data?
• Known limitations: Are there edge cases not handled?

Documentation methods:

-- Good: Documented query
/*
 * Purpose: Find top 10 customers by revenue in 2024
 * Business Rule: Include only completed orders
 * Date: 2024-12-15
 * Author: Data Team
 */
SELECT 
    c.name,
    SUM(o.total) AS total_revenue
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed'
  AND o.order_date >= '2024-01-01'
GROUP BY c.id, c.name
ORDER BY total_revenue DESC
LIMIT 10;

Benefits of documentation:

• Easier maintenance and debugging
• Knowledge sharing with team members
• Onboarding new developers faster
• Understanding business context later

Understanding of Business Requirements

SQL queries are often used to extract data for business analysis or reporting. Beginners may not fully understand the business requirements or the data model, which can lead to queries that produce incorrect or incomplete results.

Common issues:

• Misunderstanding metrics: Revenue vs. profit, unique vs. total
• Wrong time periods: Fiscal year vs. calendar year
• Incorrect filters: Including test data, excluding valid records
• Missing context: Not understanding business rules

How to avoid these issues:

1. Ask questions: Don't assume you understand the requirement
2. Verify with stakeholders: Confirm your understanding before writing queries
3. Check data dictionary: Understand what each field means
4. Review business rules: Understand how the business operates
5. Test with known examples: Compare results with manually calculated examples

Example of clarifying requirements:

• ❌ Bad: "Give me sales data"
• ✅ Good: "Give me total revenue (sum of price × quantity) for completed orders in Q1 2024, grouped by product category, excluding returns"

Read the Documentation

SQL is a programming language, and like any programming language, it has its own syntax rules. One of the most common mistakes is syntax errors, such as using the wrong keyword, omitting a comma, or using the wrong syntax for a function.

Why documentation matters:

• Different database systems have different syntax (PostgreSQL vs. MySQL vs. SQL Server)
• Functions may have different names or parameters
• Some features may not be available in all databases
• Best practices evolve with new versions

SQL provides a variety of built-in functions for data manipulation, aggregation, and analysis. However, it is important to use these functions correctly and to understand their parameters and return values.

What to read:

• Official DBMS documentation: PostgreSQL, MySQL, SQL Server, etc.
• Function reference: Parameters, return types, edge cases
• SQL standard: Understanding ANSI SQL vs. vendor-specific SQL
• Release notes: New features and breaking changes

Where to find documentation:

• Official database vendor websites
• Community wikis and forums
• SQL standard specifications
• Books and online courses

Example of checking function documentation:

Before using a function like DATE_TRUNC() in PostgreSQL, check:

• What parameters does it accept?
• What format does it return?
• Are there alternatives in other databases?
• What are the common use cases?

Good Websites to Sharpen Your SQL Skills

• SQLBolt is also a very good website that is interactive and teaches by giving simple and relatable examples. They have designed a series of lessons that are very interactive and also provide exercises for practice and better understanding.
• Khan Academy is the other best site for learning SQL. It is the best website if you want to learn programming and computer science online. They have a very convenient tutorial titled “ Intro to SQL: Querying and managing databases” in which they guide how to use SQL to store, query, and manipulate data.
• SQLZoo is a very popular site as it has a remarkable feature of examination projects. This feature makes SQLZoo unique from other websites. In this examination project, you need to solve some illustrations. All these projects carry each type of question from easy, medium, and hard. These tasks will help you in challenging your own skill.
• W3Schools SQL Tutorial: This is a popular website for learning SQL, with a comprehensive tutorial covering the basics and more advanced concepts.

Summary

Mastering SQL requires more than just knowing the syntax. These tips will help you:

• Avoid common mistakes by verifying structure and testing queries
• Write better queries by understanding joins and subqueries
• Maintain code quality through documentation and best practices
• Understand business context to deliver accurate results
• Continue learning through practice and documentation

Remember: SQL is a tool for solving business problems. The best SQL query is one that correctly answers the business question, is maintainable, and can be understood by others.

Keep practicing, keep learning, and don't hesitate to refer back to documentation when needed. The more you work with SQL, the more intuitive it will become.