SQL Syntax

Overview

The SQL syntax defines the rules and structure for writing SQL statements. Understanding SQL syntax is fundamental to writing correct and effective database queries.

SQL syntax is relatively straightforward, but it's important to follow the proper structure and rules to ensure your queries execute correctly and efficiently.

Basic SQL Statement Structure

Most SQL statements follow a consistent pattern:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name;

SQL statements typically consist of:

• Keywords: Reserved words like SELECT, FROM, WHERE (usually uppercase for readability)
• Clauses: Parts of statements like WHERE, ORDER BY, GROUP BY
• Table and column names: Identifiers for your data
• Operators and values: Comparison operators, logical operators, literals

SQL Syntax Rules

1. Case Sensitivity

SQL keywords are case-insensitive. However, conventions vary:

• Uppercase keywords: Commonly used for readability (SELECT, FROM, WHERE)
• Table/column names: May be case-sensitive depending on the database system
• String values: Usually case-sensitive ('John' ≠ 'john')

-- These are all equivalent:
SELECT * FROM Customers;
select * from customers;
Select * From Customers;

2. Semicolons

Semicolons (;) are used to separate multiple SQL statements. Some databases require them, while others treat each statement separately:

SELECT * FROM Customers;
SELECT * FROM Orders;
SELECT * FROM Products;

3. Whitespace and Line Breaks

SQL ignores extra whitespace. You can format queries for readability:

-- Single line
SELECT * FROM Customers WHERE age > 25;

-- Multi-line (more readable)
SELECT *
FROM Customers
WHERE age > 25
ORDER BY name;

4. String Literals

String values must be enclosed in single quotes ('). Double quotes are sometimes used for identifiers (table/column names):

SELECT * FROM Customers WHERE name = 'John Doe';

-- To include a single quote in a string, escape it (varies by database):
SELECT * FROM Customers WHERE name = 'O''Brien';

5. Comments

SQL supports comments to document your code:

-- Single-line comment

/* Multi-line
   comment */

SELECT * FROM Customers; -- End-of-line comment

SQL Statement Types

Data Query Language (DQL)

Retrieves data from the database:

SELECT column1, column2
FROM table_name
WHERE condition;

Data Manipulation Language (DML)

Modifies data in the database:

-- INSERT
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

-- UPDATE
UPDATE table_name SET column1 = value1 WHERE condition;

-- DELETE
DELETE FROM table_name WHERE condition;

Data Definition Language (DDL)

Creates and modifies database structure:

-- CREATE
CREATE TABLE table_name (column1 datatype, column2 datatype);

-- ALTER
ALTER TABLE table_name ADD column_name datatype;

-- DROP
DROP TABLE table_name;

Query Execution Order

Although SQL queries are written in a specific order, they are executed in a different logical order:

-- Written order:
SELECT column1, COUNT(*)
FROM table_name
WHERE condition
GROUP BY column1
HAVING COUNT(*) > 5
ORDER BY column1;

-- Logical execution order:
-- 1. FROM (identify tables)
-- 2. WHERE (filter rows)
-- 3. GROUP BY (group rows)
-- 4. HAVING (filter groups)
-- 5. SELECT (select columns)
-- 6. ORDER BY (sort results)

Common SQL Operators

Comparison Operators

• = Equal to
• <> or != Not equal to
• > Greater than
• < Less than
• >= Greater than or equal
• <= Less than or equal

Logical Operators

• AND Both conditions must be true
• OR Either condition must be true
• NOT Negates a condition

Best Practices

• Use uppercase for keywords: Makes code more readable
• Format consistently: Use consistent indentation and line breaks
• Use meaningful names: Name tables and columns descriptively
• Always use WHERE with UPDATE/DELETE: Prevents accidental updates/deletes
• Test queries first: Use SELECT to preview results before modifying data
• Use comments: Document complex queries for future reference

Common Syntax Errors

• Missing quotes: Forgetting quotes around string values
• Typos in keywords: SELECT vs SELCT
• Missing commas: In lists of columns or values
• Incorrect operator precedence: Use parentheses to clarify order
• Table/column name errors: Typos or case sensitivity issues

Next Steps

Now that you understand SQL syntax basics, you're ready to write your first queries with the SELECT statement.