SQL Basics: SELECT, FROM, WHERE, and Your First Queries

You're staring at a database containing thousands of customer records, sales transactions, and product information. Your manager just asked for "all customers from California who made purchases over $500 last quarter," and you need the answer in the next hour. This is exactly why SQL exists – to turn your specific questions into precise data queries that databases can execute instantly.

SQL (Structured Query Language) is the universal language for talking to databases. Whether you're working with PostgreSQL, MySQL, SQL Server, or any other relational database, the core SELECT statement remains your primary tool for extracting insights from data. Master these fundamentals, and you'll be able to answer complex business questions with a few lines of code.

What you'll learn:

• How to construct SELECT statements that retrieve exactly the data you need
• When and how to use FROM clauses with multiple table considerations
• How to filter data effectively with WHERE clauses and comparison operators
• How to combine conditions using logical operators (AND, OR, NOT)
• How to avoid common pitfalls that lead to incorrect results or poor performance
• Real-world techniques for building queries that scale in production environments

Prerequisites

You should have basic familiarity with databases and tables (rows and columns), and access to a SQL database system for hands-on practice. We'll use examples that work across most SQL implementations, noting any important differences.

Understanding the SELECT Statement Structure

Every SQL query starts with understanding what you want (SELECT), where it comes from (FROM), and what conditions must be met (WHERE). Think of it like giving directions: "Get me the customer names (SELECT) from the customers table (FROM) where they live in Texas (WHERE)."

Here's the basic structure:

SELECT column1, column2, column3
FROM table_name
WHERE condition;

The semicolon at the end is crucial – it tells the database where your query ends. In production environments, you'll often run multiple queries in sequence, and forgetting the semicolon can cause unexpected results when statements get combined.

Let's start with real data. Imagine you're working with an e-commerce company's customer database:

-- Customer table structure:
-- customer_id (integer)
-- first_name (varchar)
-- last_name (varchar)
-- email (varchar)
-- state (varchar)
-- registration_date (date)
-- total_spent (decimal)

Mastering the SELECT Clause

The SELECT clause determines which columns appear in your results. You have several options beyond just listing column names:

Selecting Specific Columns

SELECT first_name, last_name, email
FROM customers;

This returns three columns for all customers. Always select only the columns you need – selecting unnecessary columns wastes network bandwidth and memory, especially with large result sets.

Selecting All Columns

SELECT *
FROM customers;

The asterisk (*) selects all columns. While convenient for exploration, avoid this in production code. When table structures change (new columns added), your application might break or perform poorly.

Using Column Aliases

SELECT 
    first_name AS customer_first_name,
    last_name AS customer_last_name,
    total_spent AS lifetime_value
FROM customers;

Aliases make your output more readable and help when joining tables that have similar column names. They're essential for calculated fields:

SELECT 
    first_name,
    last_name,
    total_spent * 0.10 AS estimated_annual_value
FROM customers;

Literal Values and Expressions

SELECT 
    first_name,
    last_name,
    'Premium' AS customer_tier,
    CURRENT_DATE AS report_date
FROM customers
WHERE total_spent > 1000;

This adds constant values to your result set, useful for categorizing data or adding metadata to reports.

Working with the FROM Clause

The FROM clause specifies your data source. While it seems straightforward, there are important considerations:

Basic Table References

SELECT first_name, last_name
FROM customers;

Table Aliases

When working with complex queries, table aliases save typing and improve readability:

SELECT c.first_name, c.last_name, c.total_spent
FROM customers AS c
WHERE c.state = 'California';

You can omit the AS keyword:

SELECT c.first_name, c.last_name
FROM customers c;

Table aliases become essential when joining multiple tables (covered in later lessons).

Schema References

In production environments, you'll often need to specify the schema (database namespace):

SELECT first_name, last_name
FROM sales_db.customers;

This ensures you're querying the correct table when multiple schemas contain similarly named tables.

Filtering Data with WHERE Clauses

The WHERE clause is where SQL becomes powerful. It allows you to specify exactly which rows you want, turning a fire hose of data into precise answers.

Basic Comparison Operators

-- Exact match
SELECT first_name, last_name, total_spent
FROM customers
WHERE state = 'Texas';

-- Numeric comparisons
SELECT first_name, last_name, total_spent
FROM customers
WHERE total_spent > 500;

-- Date comparisons
SELECT first_name, last_name, registration_date
FROM customers
WHERE registration_date >= '2024-01-01';

String Comparisons and Pattern Matching

-- Case-sensitive exact match
SELECT first_name, last_name
FROM customers
WHERE first_name = 'John';

-- Pattern matching with LIKE
SELECT first_name, last_name, email
FROM customers
WHERE email LIKE '%@gmail.com';

-- Wildcard patterns
SELECT first_name, last_name
FROM customers
WHERE last_name LIKE 'Smith%';  -- Starts with 'Smith'

SELECT first_name, last_name
FROM customers
WHERE first_name LIKE '_ohn';   -- Four letters ending in 'ohn'

Warning: LIKE with leading wildcards (%something) can be slow on large tables because it can't use indexes effectively. Use them judiciously.

Handling NULL Values

NULL represents missing or unknown data, and it requires special handling:

-- This WON'T work as expected
SELECT first_name, last_name
FROM customers
WHERE email = NULL;  -- Returns no results

-- Correct way to check for NULL
SELECT first_name, last_name
FROM customers
WHERE email IS NULL;

-- Check for non-NULL values
SELECT first_name, last_name, email
FROM customers
WHERE email IS NOT NULL;

Range Conditions

-- Using BETWEEN for ranges (inclusive)
SELECT first_name, last_name, total_spent
FROM customers
WHERE total_spent BETWEEN 100 AND 1000;

-- Equivalent to:
SELECT first_name, last_name, total_spent
FROM customers
WHERE total_spent >= 100 AND total_spent <= 1000;

-- Date ranges
SELECT first_name, last_name, registration_date
FROM customers
WHERE registration_date BETWEEN '2024-01-01' AND '2024-12-31';

Membership Testing

-- Check if value is in a list
SELECT first_name, last_name, state
FROM customers
WHERE state IN ('California', 'Texas', 'New York');

-- Exclude specific values
SELECT first_name, last_name, state
FROM customers
WHERE state NOT IN ('Alaska', 'Hawaii');

Combining Conditions with Logical Operators

Real-world queries often require multiple conditions. SQL provides logical operators to combine them:

Using AND

-- Both conditions must be true
SELECT first_name, last_name, state, total_spent
FROM customers
WHERE state = 'California' 
  AND total_spent > 1000;

Using OR

-- Either condition can be true
SELECT first_name, last_name, state
FROM customers
WHERE state = 'California' 
   OR state = 'Texas';

Using NOT

-- Negate a condition
SELECT first_name, last_name, state
FROM customers
WHERE NOT state = 'California';

-- Equivalent to:
SELECT first_name, last_name, state
FROM customers
WHERE state != 'California';

Complex Condition Combinations

Parentheses control the order of operations, just like in math:

-- High-value customers from specific states OR any customer from New York
SELECT first_name, last_name, state, total_spent
FROM customers
WHERE (state IN ('California', 'Texas') AND total_spent > 1000)
   OR state = 'New York';

Without parentheses, this would have different meaning:

-- This means: (CA customers) OR (TX customers with >$1000) OR (NY customers)
SELECT first_name, last_name, state, total_spent
FROM customers
WHERE state = 'California' 
   OR state = 'Texas' AND total_spent > 1000
   OR state = 'New York';

Best Practice: Always use parentheses when combining AND and OR operators to make your intent explicit.

Advanced WHERE Techniques

Case-Insensitive Comparisons

-- Using UPPER() function
SELECT first_name, last_name, email
FROM customers
WHERE UPPER(email) LIKE '%@GMAIL.COM';

-- Using LOWER() function
SELECT first_name, last_name
FROM customers
WHERE LOWER(state) = 'california';

Working with Dates

-- Extract parts of dates
SELECT first_name, last_name, registration_date
FROM customers
WHERE EXTRACT(YEAR FROM registration_date) = 2024;

-- Date arithmetic
SELECT first_name, last_name, registration_date
FROM customers
WHERE registration_date > CURRENT_DATE - INTERVAL '30 days';

String Functions in WHERE Clauses

-- Find customers with long email addresses
SELECT first_name, last_name, email
FROM customers
WHERE LENGTH(email) > 25;

-- Find customers whose names start with specific letters
SELECT first_name, last_name
FROM customers
WHERE SUBSTRING(last_name, 1, 1) IN ('A', 'B', 'C');

Building Production-Ready Queries

When writing queries for production systems, consider these factors:

Performance Considerations

-- Good: Uses indexed column first
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id = 12345;

-- Less optimal: Function on column prevents index usage
SELECT customer_id, first_name, last_name
FROM customers
WHERE UPPER(first_name) = 'JOHN';

-- Better: Store data consistently or use functional index
SELECT customer_id, first_name, last_name
FROM customers
WHERE first_name = 'John';

Limiting Results

-- Limit results to prevent overwhelming the application
SELECT first_name, last_name, email
FROM customers
WHERE state = 'California'
LIMIT 100;

Handling Special Characters

-- Use parameterized queries in applications, but for direct SQL:
SELECT first_name, last_name
FROM customers
WHERE last_name = 'O''Brien';  -- Escape single quotes by doubling them

Hands-On Exercise

Let's build a comprehensive query for a business scenario. You're working for an e-commerce company and need to identify high-value customers for a marketing campaign.

Scenario: Find customers who meet these criteria:

• Registered in the last 2 years
• Have spent more than $500 total
• Are from California, Texas, or New York
• Have a valid email address
• Exclude anyone with "test" in their email

Here's how to approach this step-by-step:

-- Step 1: Start with basic structure
SELECT first_name, last_name, email, state, total_spent, registration_date
FROM customers;

-- Step 2: Add date filter
SELECT first_name, last_name, email, state, total_spent, registration_date
FROM customers
WHERE registration_date >= CURRENT_DATE - INTERVAL '2 years';

-- Step 3: Add spending filter
SELECT first_name, last_name, email, state, total_spent, registration_date
FROM customers
WHERE registration_date >= CURRENT_DATE - INTERVAL '2 years'
  AND total_spent > 500;

-- Step 4: Add state filter
SELECT first_name, last_name, email, state, total_spent, registration_date
FROM customers
WHERE registration_date >= CURRENT_DATE - INTERVAL '2 years'
  AND total_spent > 500
  AND state IN ('California', 'Texas', 'New York');

-- Step 5: Add email validation
SELECT first_name, last_name, email, state, total_spent, registration_date
FROM customers
WHERE registration_date >= CURRENT_DATE - INTERVAL '2 years'
  AND total_spent > 500
  AND state IN ('California', 'Texas', 'New York')
  AND email IS NOT NULL
  AND email LIKE '%@%.%';  -- Basic email format check

-- Step 6: Exclude test accounts
SELECT first_name, last_name, email, state, total_spent, registration_date
FROM customers
WHERE registration_date >= CURRENT_DATE - INTERVAL '2 years'
  AND total_spent > 500
  AND state IN ('California', 'Texas', 'New York')
  AND email IS NOT NULL
  AND email LIKE '%@%.%'
  AND LOWER(email) NOT LIKE '%test%';

-- Final version with better formatting and additional business value
SELECT 
    first_name,
    last_name,
    email,
    state,
    total_spent,
    registration_date,
    total_spent * 0.05 AS estimated_monthly_value
FROM customers
WHERE registration_date >= CURRENT_DATE - INTERVAL '2 years'
  AND total_spent > 500
  AND state IN ('California', 'Texas', 'New York')
  AND email IS NOT NULL
  AND email LIKE '%@%.%'
  AND LOWER(email) NOT LIKE '%test%'
ORDER BY total_spent DESC
LIMIT 1000;

Common Mistakes & Troubleshooting

Mistake 1: Forgetting About NULL Values

-- This won't find customers with NULL email addresses
SELECT first_name, last_name
FROM customers
WHERE email != 'invalid@example.com';

-- Correct approach
SELECT first_name, last_name
FROM customers
WHERE email IS NULL OR email != 'invalid@example.com';

Mistake 2: Incorrect Date Comparisons

-- Dangerous: assumes specific date format
SELECT first_name, last_name
FROM customers
WHERE registration_date > '01/01/2024';  -- Ambiguous format

-- Better: use standard ISO format
SELECT first_name, last_name
FROM customers
WHERE registration_date > '2024-01-01';

Mistake 3: Case Sensitivity Issues

-- May miss results due to case differences
SELECT first_name, last_name
FROM customers
WHERE state = 'california';

-- More reliable
SELECT first_name, last_name
FROM customers
WHERE LOWER(state) = 'california'
   OR state = 'California';

Mistake 4: Inefficient LIKE Patterns

-- Slow on large tables
SELECT first_name, last_name
FROM customers
WHERE first_name LIKE '%John%';

-- Faster alternatives when possible
SELECT first_name, last_name
FROM customers
WHERE first_name = 'John'
   OR first_name LIKE 'John %';

Mistake 5: Logic Operator Precedence

-- This doesn't work as intended
SELECT first_name, last_name, state, total_spent
FROM customers
WHERE state = 'California' OR state = 'Texas' AND total_spent > 1000;

-- Clear intention with parentheses
SELECT first_name, last_name, state, total_spent
FROM customers
WHERE (state = 'California' OR state = 'Texas') AND total_spent > 1000;

Troubleshooting Query Performance

When your queries run slowly:

1. Check for functions on columns in WHERE clauses – these prevent index usage
2. Look for leading wildcards in LIKE patterns – consider full-text search instead
3. Verify you're not selecting unnecessary columns – especially avoid SELECT *
4. Consider adding appropriate indexes on frequently filtered columns
5. Use LIMIT clauses when you don't need all results

Debugging Unexpected Results

When your query returns wrong data:

1. Run parts of the query separately to isolate issues
2. Check for NULL handling – add IS NULL conditions to test
3. Verify date formats and time zones
4. Test string comparisons with known data values
5. Use COUNT(*) to verify how many rows meet each condition

Summary & Next Steps

You've now mastered the fundamental building blocks of SQL queries. The SELECT, FROM, and WHERE clauses form the foundation of data retrieval, allowing you to answer specific questions with precise conditions. You can now:

• Construct targeted SELECT statements that retrieve exactly the columns you need
• Use FROM clauses effectively with proper table and schema references
• Filter data using WHERE clauses with comparison operators, pattern matching, and NULL handling
• Combine multiple conditions using logical operators with proper precedence
• Build production-ready queries that perform well and return accurate results

The techniques you've learned here – especially careful condition building and NULL handling – will serve you throughout your SQL journey. Every advanced SQL feature builds on these fundamentals.

Next steps in your SQL learning path:

• Sorting and Grouping Data – Use ORDER BY, GROUP BY, and aggregate functions to organize and summarize your results
• Joining Tables – Combine data from multiple tables using INNER, LEFT, RIGHT, and FULL OUTER joins
• Subqueries and CTEs – Write queries within queries to handle complex business logic
• Window Functions – Perform advanced analytics like running totals, rankings, and moving averages
• Query Optimization – Learn to write efficient queries that perform well on large datasets

The foundation you've built with SELECT, FROM, and WHERE will support every advanced technique you'll learn. Practice these fundamentals with real data in your organization, and you'll quickly develop the intuition needed for more complex SQL challenges.