SQL NULL Comparison — Why = NULL Returns Zero Rows

SQL is the language your database speaks, and if you don't know it, you're guessing at what your data actually says. This isn't theory—it's the difference between a query that returns exactly what you need and a query that silently returns nothing because you forgot how NULL works. Skip the mental model, and you'll spend hours debugging why your WHERE clause is lying to you.

Why = NULL Returns Zero Rows

In SQL, NULL is not a value — it is a marker for the absence of a value. Because of this, the equality operator (=) cannot evaluate NULL on either side. Any comparison like column = NULL or NULL = NULL yields UNKNOWN, not TRUE, so the row is excluded from results. This is a direct consequence of SQL's three-valued logic: TRUE, FALSE, and UNKNOWN.

When you write WHERE column = NULL, the database engine does not treat NULL as a known value to match. Instead, it evaluates the expression as UNKNOWN, and since WHERE clauses only return rows where the condition is TRUE, zero rows are returned. The correct way to test for NULL is with IS NULL or IS NOT NULL. This is not a quirk — it is intentional and consistent across all SQL databases.

Use IS NULL whenever you need to filter for missing data. In real systems, this distinction matters for data integrity checks, ETL pipelines, and reporting queries. A common mistake is to write WHERE column = NULL expecting matches, which silently returns nothing — no error, no warning, just zero rows.

Your First SQL Query — SELECT, FROM, and WHERE Explained

SELECT is the most important SQL command you'll ever learn. It answers the question: 'Show me data.' The basic structure is almost a sentence: SELECT [what columns] FROM [which table].

The asterisk (*) is a wildcard meaning 'all columns'. Use it when exploring data. In production code, always name the columns explicitly — it's faster and far easier to read six months later.

The WHERE clause is how you filter. Without WHERE, SQL returns every single row in the table. WHERE adds a condition that each row must pass to be included in the results. Think of it as a bouncer at the door: only rows that satisfy the condition get in.

Conditions in WHERE can use these comparison operators: = (equals), != or <><> (not equals), > (greater than), < (less than), >= (greater than or equal), <= (less than or equal). You can also combine multiple conditions using AND (both must be true) and OR (either can be true).

-- ============================================================
-- Querying the bookstore database we created in the last step.
-- Run each query block one at a time to see the output.
-- ============================================================

-- Query 1: Get every column from every row in the books table.
-- Good for exploration, but avoid SELECT * in real apps.
SELECT * FROM books;

-- Query 2: Get only the title and price — we don't need everything.
SELECT title, price
FROM books;

-- Query 3: Filter with WHERE — only books cheaper than $10.00
SELECT title, price
FROM books
WHERE price < 10.00;

-- Query 4: Combine conditions with AND
-- Only books under $10 that were also published after 1980
SELECT title, price, published
FROM books
WHERE price < 10.00
  AND published > 1980;

-- Query 5: Use OR — books that are either Satire OR Dystopian genre
SELECT title, genre
FROM books
WHERE genre = 'Satire'
   OR genre = 'Dystopian';

-- Query 6: A tidier way to write the OR above, using IN
-- IN checks if a value matches any item in a list
SELECT title, genre
FROM books
WHERE genre IN ('Satire', 'Dystopian');

Sorting, Limiting and Shaping Results with ORDER BY and LIMIT

Fetching data is one thing — getting it back in a useful order is another. ORDER BY sorts your results by one or more columns. ASC means ascending (A→Z, 0→9) and is the default. DESC means descending (Z→A, 9→0).

You can sort by multiple columns: ORDER BY genre ASC, price DESC sorts alphabetically by genre first, then within each genre sorts by price from highest to lowest. This is enormously useful for leaderboards, product listings, and reports.

LIMIT controls how many rows come back. Databases can hold millions of rows — you almost never want all of them at once. LIMIT 5 gives you the top five. Pair it with ORDER BY and you can answer questions like 'What are the 3 most expensive books?' in a single clean query.

ALIAS (the AS keyword) lets you rename a column in the output. This is purely cosmetic — it doesn't change the database — but it makes results far more readable, especially when column names are long or ambiguous.

-- ============================================================
-- Sorting, limiting and renaming output columns.
-- These techniques are used in virtually every real-world query.
-- ============================================================

-- Query 1: Sort all books by price, cheapest first (ASC is default).
SELECT title, price
FROM books
ORDER BY price ASC;

-- Query 2: Sort by price, most expensive first.
SELECT title, price
FROM books
ORDER BY price DESC;

-- Query 3: The 3 most expensive books — combine ORDER BY with LIMIT.
SELECT title, price
FROM books
ORDER BY price DESC
LIMIT 3;

-- Query 4: Sort by genre alphabetically, then by price within each genre.
-- This is multi-column sorting.
SELECT title, genre, price
FROM books
ORDER BY genre ASC, price ASC;

-- Query 5: Use AS to give columns friendlier names in the output.
-- 'title' becomes 'Book Title', 'price' becomes 'Price (USD)'.
-- Notice: multi-word aliases need to be wrapped in double quotes.
SELECT title   AS "Book Title",
       price   AS "Price (USD)",
       published AS "Year"
FROM books
ORDER BY price DESC
LIMIT 4;

Aggregate Functions — COUNT, SUM, AVG, MIN, MAX and GROUP BY

So far every query has returned individual rows. But sometimes you don't want individual records — you want a summary. How many books does each author have? What's the average price per genre? What's the most expensive book? Aggregate functions answer these questions by collapsing many rows into a single calculated value.

The five core aggregate functions: COUNT() counts rows, SUM() adds up numeric values, AVG() calculates the average, MIN() finds the smallest value, MAX() finds the largest.

Aggregate functions become truly powerful when combined with GROUP BY. GROUP BY splits your table into groups (one group per unique value in a column) and then runs the aggregate function on each group separately. The result is one summary row per group.

HAVING is like WHERE but for aggregated results. You can't use WHERE to filter on a COUNT or SUM because those values don't exist until after grouping happens. HAVING runs after the groups are formed, so it can filter on them.

-- ============================================================
-- Aggregate functions and GROUP BY on our bookstore data.
-- This is where SQL starts to feel genuinely powerful.
-- ============================================================

-- Query 1: How many books are in the entire table?
-- COUNT(*) counts every row regardless of NULL values.
SELECT COUNT(*) AS total_books
FROM books;

-- Query 2: What is the average price of all books?
SELECT ROUND(AVG(price), 2) AS average_price
FROM books;
-- ROUND(value, 2) rounds the result to 2 decimal places.

-- Query 3: Most expensive and cheapest single book.
SELECT MAX(price) AS most_expensive,
       MIN(price) AS cheapest
FROM books;

-- Query 4: How many books does each author have?
-- GROUP BY splits the table into one group per author_id,
-- then COUNT(*) counts the rows in each group.
SELECT author_id,
       COUNT(*) AS number_of_books
FROM books
GROUP BY author_id
ORDER BY number_of_books DESC;

-- Query 5: Average price per genre.
SELECT genre,
       COUNT(*)            AS book_count,
       ROUND(AVG(price),2) AS avg_price
FROM books
GROUP BY genre
ORDER BY avg_price DESC;

-- Query 6: HAVING — only show genres that have MORE than 1 book.
-- We cannot use WHERE here because book_count doesn't exist yet
-- at the point WHERE runs. HAVING runs AFTER GROUP BY.
SELECT genre,
       COUNT(*) AS book_count
FROM books
GROUP BY genre
HAVING book_count > 1;

Joining Tables with INNER JOIN — Connecting Data Across Tables

So far we've worked with one table. In real databases, data lives spread across multiple tables that relate to each other — customers -> orders -> products. To bring that data together in a single query, you need JOINs.

INNER JOIN returns only rows where the join condition is true in both tables. It's the most common JOIN type. The syntax: SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column. The ON clause specifies how the tables relate.

In our bookstore, books have an author_id that links to the authors table. To get each book's title alongside the author's full name, we join books to authors on books.author_id = authors.author_id.

If a book has an author_id that doesn't exist in authors, that book is excluded from the result. That's the 'inner' part — only matching records survive.

-- ============================================================
-- Joining books and authors to show book title + author name.
-- INNER JOIN = only rows where the match exists in both tables.
-- ============================================================

-- Basic INNER JOIN: get every book with its author's full name.
SELECT books.title,
       authors.full_name
FROM books
INNER JOIN authors ON books.author_id = authors.author_id;

-- Add sorting to see them in a nice order.
SELECT books.title AS "Book Title",
       authors.full_name AS "Author"
FROM books
INNER JOIN authors ON books.author_id = authors.author_id
ORDER BY authors.full_name ASC;

-- Use table aliases to keep the query readable.
SELECT b.title,
       a.full_name
FROM books   AS b
INNER JOIN authors AS a ON b.author_id = a.author_id;

Modifying Data — INSERT, UPDATE, and DELETE Basics

Querying data is half the story. To manage data, you need to add, change, and remove it. SQL provides three core commands for that: INSERT adds new rows, UPDATE modifies existing rows, and DELETE removes rows.

INSERT INTO table (columns) VALUES (values) adds one row at a time. You can also insert multiple rows in a single statement.

UPDATE table SET column = new_value WHERE condition changes data. The WHERE clause is critical here — if you omit it, every row in the table gets updated.

DELETE FROM table WHERE condition removes rows. Same warning: missing WHERE deletes every row.

Always run a SELECT with the same WHERE first to verify which rows will be affected before running UPDATE or DELETE.

-- ============================================================
-- Adding, updating, and deleting data in our bookstore.
-- Run each block separately and verify with SELECT afterward.
-- ============================================================

-- 1. INSERT a new author.
INSERT INTO authors (author_id, full_name, country)
VALUES (5, 'Elena Ferrante', 'Italy');

-- 2. INSERT multiple books at once.
INSERT INTO books (book_id, title, author_id, genre, price, published)
VALUES
    (7, 'My Brilliant Friend',       5, 'Literary Fiction',  14.99, 2012),
    (8, 'The Story of a New Name',   5, 'Literary Fiction',  14.99, 2013);

-- 3. UPDATE the price of all books by a specific author (author_id = 1).
-- FIRST, preview which rows will be updated:
SELECT book_id, title, price
FROM books
WHERE author_id = 1;

-- THEN, run the UPDATE (10% price increase for George Orwell's books).
UPDATE books
SET price = price * 1.10
WHERE author_id = 1;

-- 4. DELETE a specific book by its ID.
-- Preview first:
SELECT * FROM books WHERE book_id = 8;

-- Then delete:
DELETE FROM books
WHERE book_id = 8;

-- 5. Danger: UPDATE without WHERE updates ALL rows.
-- Uncomment the next line only if you really mean it.
-- UPDATE books SET price = 0.00;

Database Design: Why Your Schema Is Probably Wrong

Most developers jump straight to writing queries before they've thought about how their data actually relates. That's like building a house without a foundation. It works until it doesn't—and when it fails, it fails hard.

A relational database isn't just a bucket for data. It's a model of your business logic. Every table should represent one entity. Every column should be atomic. This is called normalization, and ignoring it is why you end up with columns like tags_csv or user_preferences_json that you're forced to parse in application code.

Here's the rule: If you can't explain what a table represents in one sentence, your schema is broken. A table called orders_items_products_users is a cry for help. Split it. Name things clearly. Define foreign keys upfront—not as an afterthought when your JOINs stop making sense.

Design for queries you'll write six months from now, not the one you're writing today. The database doesn't care about your feelings. It cares about constraints, indexes, and referential integrity.

// io.thecodeforge — database tutorial

-- Bad schema: everything in one table
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    product_name VARCHAR(100),
    product_price DECIMAL(10,2),
    order_date DATETIME,
    shipping_address TEXT
);

-- Good schema: normalized entities
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME,
    shipping_address TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

SQL Basics: The Mental Model That Saves Hours

SQL is not Python. It's not JavaScript. Stop trying to think procedurally—SQL is declarative. You tell it WHAT you want, not HOW to get it. This trips up everyone who comes from imperative languages.

Here's the mental model: Imagine SQL as a pipeline. Data flows through each clause in a specific order. Most developers think SELECT runs first. It doesn't. The actual execution order is:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

This matters because you can't filter on an alias you defined in SELECT within WHERE—the WHERE clause hasn't seen it yet. I've seen junior devs spend an hour debugging this exact issue.

Another fundamental: SQL works on sets, not loops. When you write SELECT * FROM users WHERE id IN (1,2,3), the database is doing set operations, not iterating through a list. This is why SQL is faster at data operations than application code—it's built for batch processing.

Understand this mental model, and you'll stop writing queries that work accidentally and start writing queries that work by design.

// io.thecodeforge — database tutorial

-- This WILL fail because WHERE can't see the alias
SELECT 
    u.id,
    CONCAT(u.first_name, ' ', u.last_name) AS full_name
FROM users u
WHERE full_name LIKE '%Smith%';

-- This WORKS: use HAVING or subquery
SELECT 
    u.id,
    CONCAT(u.first_name, ' ', u.last_name) AS full_name
FROM users u
HAVING full_name LIKE '%Smith%';

-- But best practice: filter before projection
SELECT 
    u.id,
    CONCAT(u.first_name, ' ', u.last_name) AS full_name
FROM users u
WHERE u.last_name = 'Smith';

DBMS vs. SQL: Why You Need Both to Survive

Newcomers confuse SQL with the database itself. They're not the same thing. SQL is the language. DBMS (Database Management System) is the engine that runs it. You can write perfect SQL and still get destroyed by the DBMS you're running it on.

MySQL, PostgreSQL, SQL Server, SQLite—they all speak SQL, but each has its own quirks. PostgreSQL handles JSON natively. MySQL has GROUP BY behavior that'll bite you if you're not careful. SQLite doesn't support RIGHT JOINs at all. Knowing the difference keeps you from shipping code that works locally but explodes in production.

A DBMS does four things: store data, retrieve data, manage concurrent access, and ensure data integrity. That last one is why transactions exist. If you're not using transactions for operations that modify multiple tables, you're asking for data corruption.

Here's what matters: Pick one DBMS and learn it deeply before jumping to others. PostgreSQL is the industry standard for production systems. SQLite is fine for local dev. MySQL is popular but has enough foot-guns to fill a quarry. Know your toolkit.

// io.thecodeforge — database tutorial

-- PostgreSQL: returns full_name and count
SELECT 
    full_name, 
    COUNT(*)
FROM users
GROUP BY full_name;

-- MySQL: same query might run but returns garbage
-- MySQL allows SELECT columns not in GROUP BY
-- It picks arbitrary values. This is dangerous.

-- Safe MySQL version:
SELECT 
    full_name, 
    COUNT(*) AS cnt
FROM users
GROUP BY full_name
ORDER BY cnt DESC;

Subqueries: When One Query Isn't Enough

You've mastered SELECT, JOIN, and GROUP BY. But real-world data doesn't always cooperate. You need to filter by aggregated results, compare against averages, or find rows that don't exist in another table. That's where subqueries — queries inside queries — save your ass.

A subquery can live in WHERE, FROM, or even SELECT. It runs first, producing a value or a set of rows. The outer query uses that result like a variable. This isn't just theory: you'll use it to find customers who spent above average, products never ordered, or employees in the bottom quartile.

Don't nest too deep. Two levels is usually enough. Three is a code smell. If you need four, you're not breaking down the problem properly. Subqueries are surgical tools, not a substitute for proper schema design.

// io.thecodeforge — database tutorial

-- Get customers who spent more than average
SELECT first_name, last_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total) > (
        SELECT AVG(total)
        FROM orders
    )
);

Window Functions: Rank, Lag, and Moving Averages Without Self-Joins

You need a running total. Or to rank salespeople by region. Or find the difference between each month's revenue and the previous month. You could write a self-join with GROUP BY — ugly, slow, and hard to read. Or you could use window functions, the unsung heroes of analytical SQL.

Window functions compute across a set of rows related to the current row, without collapsing them into one result. OVER() defines the window. PARTITION BY splits into groups. ORDER BY within the window sets the sequence. ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER() — these are your new tools.

Every senior dev uses window functions for reporting, deduplication, and time-series analysis. They're faster than subqueries and cleaner than self-joins. Learn them once, use them daily.

// io.thecodeforge — database tutorial

-- Month-over-month revenue change per product
SELECT 
    product_id,
    month,
    revenue,
    LAG(revenue) OVER (
        PARTITION BY product_id 
        ORDER BY month
    ) AS prev_month_revenue,
    revenue - LAG(revenue) OVER (
        PARTITION BY product_id 
        ORDER BY month
    ) AS change
FROM product_monthly_revenue;

Why SQL Optimization Matters Before Your Query Runs

Slow queries don't fail—they just waste seconds that compound into hours. SQL optimization is the practice of reducing query execution time and resource consumption. The why: every unoptimized SELECT or JOIN scans more rows than necessary, locking tables, consuming memory, and frustrating users. The how starts with understanding the query execution plan—EXPLAIN ANALYZE reveals whether your database is doing full table scans when it could use an index. Indexes are the single most impactful tool: a B-tree index on a WHERE column can turn a 10-second query into 10 milliseconds. But indexes aren't free—they slow down writes. Other techniques include avoiding SELECT *, using EXISTS instead of IN for subqueries, filtering early with WHERE before JOINs, and choosing the correct join type. The result: faster APIs, lower hosting costs, and happier DBAs.

// io.thecodeforge — database tutorial

// Slow: full table scan on 1M rows
SELECT * FROM orders WHERE status = 'pending';

// Fast: B-tree index on status
CREATE INDEX idx_orders_status ON orders(status);

// Re-run same query — now uses index scan
EXPLAIN ANALYZE
SELECT id, customer_id, total
FROM orders
WHERE status = 'pending';

The Most Dreaded Topics: NULLs, JOIN Confusion, and Subquery Bloat

Three topics make experienced developers sweat: NULL behavior, multi-table JOINs, and nested subqueries. NULL is not a value—it's the absence of a value. Comparing anything to NULL with = returns NULL, not TRUE or FALSE, which is why WHERE column = NULL gives zero rows. The fix: use IS NULL or IS DISTINCT FROM. JOIN confusion happens because developers forget LEFT JOIN keeps all rows from the left table, while INNER JOIN drops non-matching rows. Mixing OUTER and INNER joins in the same query is the top cause of unexpected row loss. Subquery bloat occurs when developers wrap multiple subqueries inside SELECT instead of using JOINs or CTEs. A subquery inside a WHERE clause runs once per row—this is a performance disaster. Use EXISTS for correlated subqueries or rewrite with JOIN. The mental model: every query is a set operation—if you can't picture the Venn diagram, you'll get wrong results.

// io.thecodeforge — database tutorial

-- WRONG: returns zero rows for missing middle_name
SELECT id, name FROM users WHERE middle_name = NULL;

-- CORRECT: check for absence
SELECT id, name FROM users WHERE middle_name IS NULL;

-- JOIN trap: accidental cartesian product
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Subquery bloat (runs N+1 times)
-- Rewrite as JOIN or use EXISTS

Course Syllabus: From Zero to Production-Ready SQL

This course is structured to build your mental model of SQL from the ground up, then rapidly push you into practical, complex querying. The syllabus is divided into four parts. Part One covers the foundational mental model: how a database engine interprets SELECT, WHERE, and FROM clauses differently than you read them. We then explore schema design — why most beginners (and many seniors) end up with conflicting, redundant tables. Part Two dives into aggregate functions, GROUP BY behavior, and the silent pitfalls of NULLs in calculations. Part Three is the core of real-world SQL: joining tables with INNER and LEFT JOINs, subqueries as derived tables, and window functions that eliminate fragile self-joins. Part Four focuses on data modification (INSERT, UPDATE, DELETE) with transactional safety, and finally SQL optimization — reading execution plans before your query hits production. Each module ends with a short quiz, and the final project is a multi-join warehouse report.

// io.thecodeforge — database tutorial
-- Quick check: list modules for review
SELECT module_id, title, duration_min
FROM course_syllabus
WHERE course_name = 'introduction_sql'
ORDER BY module_id;

Projects, Certification & Paid Features

This course includes two capstone projects: first, you'll design and populate a normalized inventory database for a fictitious e-commerce store — including customers, orders, products, and a shared many-to-many order_items table. You'll write queries that answer real business questions: 'Which products have the highest average order value?' and 'Which customers have not ordered in 90 days?' The second project is a performance analysis: you're given a slow production query (a 6-table JOIN with subqueries) and must rewrite it using window functions and indexing hints to cut execution time by 80%. Upon completing both projects and passing the final assessment (80%+ on a 20-question mixed quiz), you'll earn a certificate of completion from TheCodeForge, verifiable via a unique link. Current course rating: 4.7/5 from 1,200+ reviews. A paid plan unlocks downloadable PDF cheatsheets, a private Slack community for live query reviews, and early access to the 'Advanced Indexing & Query Tuning' module. Free tier includes all lessons and community forum access.

// io.thecodeforge — database tutorial
SELECT student_id
FROM course_progress
WHERE course_id = 'intro_sql'
  AND project_1_passed = 1
  AND project_2_passed = 1
  AND final_quiz_score >= 80
  AND plan_type IN ('free', 'paid');

Modern SQL: SQL:2023 Standard Features

The SQL:2023 standard introduces several modern features that enhance query capabilities and developer productivity. Key additions include property graph queries for graph-based data relationships, improved JSON support with JSON_TABLE and JSON_VALUE functions, and new aggregate functions like ANY_VALUE for non-deterministic grouping. Additionally, SQL:2023 standardizes the GREATEST and LEAST functions, which were previously vendor-specific, and introduces the NULLS NOT DISTINCT clause for unique constraints that treat NULLs as equal. These features simplify complex queries and align SQL with modern data modeling needs. For example, JSON_TABLE allows you to parse JSON documents into relational rows directly within SQL:

``sql SELECT FROM JSON_TABLE( '{"items": [{"id": 1, "name": "A"}, {"id": 2, "name": "B"}]}', '$.items[]' COLUMNS ( id INT PATH '$.id', name VARCHAR(10) PATH '$.name' ) ) AS jt; ``

This returns two rows with id and name columns. The property graph query syntax enables pattern matching on graphs using MATCH clauses, similar to Cypher. While full support varies by database, these features represent the future of SQL standardization.

-- SQL:2023 JSON_TABLE example
SELECT *
FROM JSON_TABLE(
  '{"items": [{"id": 1, "name": "A"}, {"id": 2, "name": "B"}]}',
  '$.items[*]' COLUMNS (
    id INT PATH '$.id',
    name VARCHAR(10) PATH '$.name'
  )
) AS jt;

-- GREATEST and LEAST (standardized)
SELECT GREATEST(10, 20, 30) AS max_val; -- returns 30

-- NULLS NOT DISTINCT for unique constraints
CREATE TABLE t (
  a INT,
  b INT,
  UNIQUE NULLS NOT DISTINCT (a, b)
);

SQL Dialects: PostgreSQL vs MySQL vs SQL Server vs Oracle Differences

While SQL is standardized, each database vendor implements its own dialect with unique syntax, functions, and behaviors. Understanding these differences is crucial for writing portable code and avoiding surprises. Key areas of divergence include:

• String Concatenation: PostgreSQL and Oracle use ||, MySQL uses CONCAT(), SQL Server uses +.
• LIMIT/OFFSET: PostgreSQL and MySQL use LIMIT ... OFFSET, SQL Server uses OFFSET ... FETCH NEXT, Oracle uses OFFSET ... ROWS FETCH NEXT or ROWNUM.
• Date/Time Functions: PostgreSQL uses NOW(), MySQL uses NOW(), SQL Server uses GETDATE(), Oracle uses SYSDATE.
• NULL Ordering: PostgreSQL treats NULLs as largest by default, MySQL as smallest, SQL Server as smallest, Oracle as largest. Use NULLS FIRST/LAST for control.
• Auto-increment: PostgreSQL uses SERIAL or IDENTITY, MySQL uses AUTO_INCREMENT, SQL Server uses IDENTITY, Oracle uses SEQUENCE or IDENTITY.
• Window Functions: All support them, but syntax for frames and aggregates varies slightly.

Example of LIMIT differences:

```sql -- PostgreSQL / MySQL SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- SQL Server SELECT * FROM users ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Oracle (12c+) SELECT * FROM users ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; ```

When writing cross-database applications, use an ORM or abstract the SQL layer to handle these differences.

-- String concatenation
-- PostgreSQL / Oracle
SELECT 'Hello' || ' ' || 'World';
-- MySQL
SELECT CONCAT('Hello', ' ', 'World');
-- SQL Server
SELECT 'Hello' + ' ' + 'World';

-- LIMIT/OFFSET
-- PostgreSQL / MySQL
SELECT * FROM products ORDER BY price LIMIT 5 OFFSET 10;
-- SQL Server / Oracle (12c+)
SELECT * FROM products ORDER BY price OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

-- NULL ordering (defaults vary)
SELECT * FROM employees ORDER BY salary NULLS LAST; -- explicit

SQL in the Cloud: BigQuery, Redshift, Snowflake Dialect Differences

Cloud data warehouses like Google BigQuery, Amazon Redshift, and Snowflake extend SQL with proprietary features optimized for large-scale analytics. While they support standard SQL, key differences affect query writing and optimization:

• BigQuery: Uses GoogleSQL, which is ANSI-compliant but has unique features like SELECT EXCEPT, UNNEST for arrays, and PARTITION BY for clustering. It charges per query (data scanned), so SELECT is expensive. BigQuery requires backticks for table names with hyphens.

-- BigQuery: SELECT * EXCEPT
SELECT * EXCEPT (sensitive_column) FROM dataset.table;

-- Snowflake: QUALIFY for window filters
SELECT id, amount, RANK() OVER (ORDER BY amount DESC) AS rnk
FROM sales
QUALIFY rnk <= 10;

-- Redshift: Distribution key example
CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_date DATE
) DISTKEY (customer_id) SORTKEY (order_date);

-- BigQuery: Partitioned table
CREATE TABLE dataset.sales
PARTITION BY DATE(order_date)
CLUSTER BY customer_id
AS SELECT * FROM source;