SQL Syntax, Comments & Operators

Every language has grammar rules, and SQL is no different. Before you write longer queries, it helps to know how a statement is put together, when capital letters matter, how to leave notes for yourself, and how MySQL works out an expression such as price * quantity > 5000 AND NOT status = 'cancelled'.

This lesson collects those rules in one place. You will meet the parts of a statement, comments, the difference between quotes and backticks, and the arithmetic, comparison and logical operators, ending with the order in which MySQL evaluates them.

The parts of a statement

A statement is one complete instruction to the database, such as "show me these rows" or "delete that order". It is built from a few kinds of pieces:

Clauses must appear in a fixed order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. You can leave out the ones you do not need, but you cannot swap them. A semicolon ; ends the statement. The MySQL command-line client waits for it before running anything, and you need it to separate statements when you run several at once.

Spaces, tabs and line breaks do not matter to SQL, so you can lay out a query in whatever way makes it easy to read. Putting each clause on its own line is a common habit.

Upper case and lower case

Different parts of a query follow different case rules, and this confuses many beginners.

• Keywords never care: select, SELECT and Select all work. Writing them in capitals is a convention that makes them stand out.
• Column names are not case-sensitive in MySQL: STOCK finds the stock column.
• Table and database names depend on the operating system. MySQL stores each table as files on disk, and Linux file names are case-sensitive. The lower_case_table_names setting is 0 (case-sensitive) on Linux, 1 on Windows and 2 on macOS. A query that uses Products works on your Windows laptop and then fails on a Linux web server. Use lower-case names everywhere and the problem never appears.
• Text values are compared using the column's collation, the set of rules for comparing text. MySQL's default, utf8mb4_0900_ai_ci, ends in ci for "case-insensitive", so 'rohtak' matches 'Rohtak'.

That last rule is one of the places where the browser's SQLite behaves differently:

SELECT COUNT(*) FROM customers WHERE city = 'rohtak';

COUNT(*)
--------
2
(1 row)

MySQL finds both Rohtak customers. SQLite compares text exactly, so the same query gives 0 in the Try-it editor. Type values with the same capitals as the data and your queries behave the same everywhere.

Comments

A comment is text that the database ignores. Use comments to explain why a query does something, or to switch off a line while testing. SQL has two standard styles:

-- Products that need reordering soon
SELECT product_name, stock     /* stock = units in the warehouse */
FROM products
WHERE stock < 30               -- fewer than 30 left
ORDER BY stock;

product_name                | stock
----------------------------+------
HD Webcam                   | 0
Graphics Tablet             | 12
27-inch Monitor             | 18
Noise-Cancelling Headphones | 25
(4 rows)

• -- starts a comment that runs to the end of the line. In MySQL the two dashes must be followed by a space: --fewer is not a comment.
• /* ... */ can sit inside a line or cover many lines.
• # also starts a line comment, but only in MySQL. Other databases, including SQLite, reject it, so prefer --.

Files made by mysqldump contain lines such as /*!40101 SET NAMES utf8mb4 */;. MySQL runs the code inside if its version is at least the number after the !; other databases see a normal comment.

Quotes, backticks and names

Text literals go inside single quotes: 'Jaipur'. To put an apostrophe inside, type it twice. Names that clash with keywords or contain spaces go inside backticks:

SELECT order_id AS `Order No`, `status`, 'Rohan''s order' AS note
FROM orders
WHERE customer_id = 3;

Order No | status    | note
---------+-----------+--------------
103      | delivered | Rohan's order
113      | shipped   | Rohan's order
(2 rows)

Backticks are MySQL's way of quoting an identifier. You need them when a name is a reserved word or contains spaces. A table called order must be written as `order`, because ORDER is a keyword. Even rank caused trouble when people upgraded to MySQL 8, because RANK became a reserved word for window functions. The simplest fix is to choose names that are not keywords.

Double quotes are the confusing case. In MySQL, "Jaipur" is a string, just like 'Jaipur'. In standard SQL, PostgreSQL and SQLite, double quotes mark an identifier, like backticks. MySQL switches to the standard meaning when the ANSI_QUOTES SQL mode is on. So the rule that works everywhere is: single quotes for values, backticks (or nothing) for names.

Arithmetic operators

You can calculate with +, -, *, / and % (remainder after division). Here each order line gets a total, a price after a ₹100 coupon, and a check for odd quantities:

SELECT order_id, quantity, unit_price,
       quantity * unit_price AS line_total,
       unit_price - 100 AS after_coupon,
       quantity % 2 AS odd_quantity
FROM order_items
WHERE order_id IN (101, 106, 112);

order_id | quantity | unit_price | line_total | after_coupon | odd_quantity
---------+----------+------------+------------+--------------+-------------
101      | 2        | 799        | 1598       | 699          | 0
101      | 1        | 549        | 549        | 449          | 1
106      | 2        | 1299       | 2598       | 1199         | 0
106      | 3        | 450        | 1350       | 350          | 1
112      | 1        | 6499       | 6499       | 6399         | 1
112      | 4        | 549        | 2196       | 449          | 0
(6 rows)

MySQL shows the money columns with two decimals, such as 1598.00. Two more rules matter. In MySQL, / always gives a decimal answer (7 / 2 is 3.5000), and the separate DIV operator gives the whole-number part (7 DIV 2 is 3). And any calculation with NULL gives NULL: price + NULL is NULL, not price.

Comparison and logical operators

Comparison operators (=, <> or !=, <, >, <=, >=, BETWEEN, IN, LIKE, IS NULL) do not only work inside WHERE. Each one is an expression that returns 1 for true, 0 for false, or NULL when a value is unknown:

SELECT product_name, price, stock,
       stock > 50 AS well_stocked,
       price BETWEEN 1000 AND 3000 AS mid_price,
       category IN ('Audio', 'Storage') AS audio_or_storage
FROM products
WHERE product_id <= 5;

product_name        | price | stock | well_stocked | mid_price | audio_or_storage
--------------------+-------+-------+--------------+-----------+-----------------
Wireless Mouse      | 799   | 120   | 1            | 0         | 0
Mechanical Keyboard | 3499  | 45    | 0            | 0         | 0
27-inch Monitor     | 15999 | 18    | 0            | 0         | 0
USB-C Hub           | 1899  | 60    | 1            | 1         | 0
Laptop Stand        | 1299  | 75    | 1            | 1         | 0
(5 rows)

The logical operators AND, OR and NOT combine these results, as you saw in AND, OR & NOT. MySQL adds a few extras that other databases do not have: XOR (exactly one side true), <=> (a NULL-safe equals, where NULL <=> NULL is 1), and the symbols &&, || and ! for AND, OR and NOT. Avoid the symbols. In particular, || means OR in MySQL but joins text in PostgreSQL, SQLite and standard SQL.

Operator precedence

When an expression has several operators, MySQL does not simply work from left to right. It follows an order called precedence, highest first:

Operators on the same level run from left to right. Brackets beat everything:

SELECT 2 + 3 * 4 AS no_brackets,
       (2 + 3) * 4 AS with_brackets,
       10 - 4 - 3 AS left_to_right,
       -2 * 3 + 10 AS unary_minus_first;

no_brackets | with_brackets | left_to_right | unary_minus_first
------------+---------------+---------------+------------------
14          | 20            | 3             | 4
(1 row)

The same rules apply to conditions. NOT category = 'Accessories' AND price < 3000 OR stock = 0 is read as ((NOT category = 'Accessories') AND price < 3000) OR stock = 0. That is why the out-of-stock HD Webcam appears in its result even though it is an accessory. When you mix AND and OR, add brackets even if you think they are not needed. They cost nothing and show the next reader exactly what you meant.

Common mistakes

• Double quotes for text. WHERE city = "Pune" works in MySQL by luck and breaks in other databases. Use 'Pune'.
• -- without a space. --note is not a comment in MySQL, so the query fails with a confusing syntax error.
• Mixed-case table names. FROM Orders works on Windows and fails on a Linux server. Keep names in lower case.
• Using || to join text in MySQL. It is a logical OR there, so 'Aarav' || ' Sharma' returns 0. Use CONCAT().

Key points

• Clauses follow a fixed order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT; whitespace does not matter.
• Keywords and column names are not case-sensitive; table names can be, on Linux.
• Comments: -- (with a space) and /* */ are standard; # is MySQL-only.
• Single quotes for text values, backticks for awkward names; double quotes mean different things in different databases.
• Comparisons return 1, 0 or NULL, and any arithmetic with NULL gives NULL.
• Multiplication beats addition, AND beats OR; brackets make the order clear.

-- Discount preview for expensive order lines
SELECT item_id,
       quantity * unit_price AS line_total,
       quantity * unit_price * 0.9 AS `after 10% off`,
       quantity >= 2 AS bulk
FROM order_items
WHERE unit_price > 2000;