SQL Basic syntax

Case Sensitivity in SQL Statements and Objects

One of the key things that sets SQL apart from other programming languages is its general case insensitivity. For keywords like SELECT, FROM, WHERE etc, capitalization does not matter:

SeLeCt * FrOm Books WhErE Publish_Date > '2020-01-01';

This query searching books would run identically to:

select * from books where publish_date > '2020-01-01';

So SQL allows flexibility in formatting statement keywords. Capitalization conventions like CamelCase or lowercase are usually just for readability.

String comparison

When comparing string values in SQL using the WHERE clause or JOIN conditions, the default behavior is often case-insensitive. For example, 'john' and 'John' would be considered equal. However, this behavior can vary depending on the database system and collation settings.

Statement Termination with Semicolons

In SQL, each statement must be terminated with a semicolon ; symbol.

Without a semicolon, databases will fail to parse the full statement:

SELECT * FROM books WHERE price > 10 ORDER BY publish_date DESC
SELECT * FROM authors

This would error. The semicolon indicates the end:

SELECT * FROM books WHERE price > 10 ORDER BY publish_date DESC;
SELECT * FROM authors;

Now both statements will execute properly.

Indentation and Line Breaks

SQL statements can grow quite long with multiple clauses and nesting. Proper indentation and line breaks are key for readability.

For example:

SELECT c.Name, b.Title, r.Rating
FROM customers c
LEFT JOIN reviews r
    ON r.customer_id = c.customer_id
LEFT JOIN books b
   ON b.book_id = r.book_id
WHERE r.Rating IS NOT NULL
ORDER BY c.Name;

The indented JOIN clauses are much clearer to parse than cramming onto one line. Consistent indentation also helps when modifying complex queries.

Commenting Code

Comments documenting sections of SQL code or explaining logic should always be included:

# Select customer and order data for November 2022
/* comment */
SELECT c.Name, o.OrderDate, o.Amount
FROM customers c
INNER JOIN orders o
    -- Join between customer and order tables
    ON c.CustomerID = o.CustomerID
WHERE o.OrderDate BETWEEN '2022-11-01' AND '2022-11-30'
ORDER BY c.Name;

Comments like above help future developers understand the purpose of code segments without having to decipher complex logic themselves.