MySQL is a popular and widely used Relational Database Management System. Like any programming language, MySQL has its own set of reserved words. Reserved words are specific terms or keywords with predefined meanings within the database system. It is very important to know the reserved words to avoid conflicts in the execution of the queries.

What are Reserved Words?

Reserved words in MySQL are predefined keywords that have specific purposes and meanings within the database system. These words are reserved for MySQL to perform various operations, such as defining data structures, manipulating data, and executing queries. It’s crucial to note that reserved words cannot be used as identifiers, such as table or column names unless enclosed in backticks (`) to differentiate them from reserved words.

Reserved Words in MySQL

Here are some commonly used reserved words in MySQL and their meaning.

1. SELECT: Used to retrieve data from one or more database tables.
2. INSERT: Used to insert new records into a database table.
3. UPDATE: Used to modify existing records in a database table.
4. DELETE: Used to delete specific records from a database table.
5. CREATE: Used to create a new database, table, or other database objects.
6. ALTER: Used to modify the structure of an existing database object.
7. DROP: Used to delete or remove a database object.
8. WHERE: Used to filter data based on specific conditions in a query.
9. JOIN: Used to combine rows from two or more tables based on a related column.
10. FROM: Specifies the data source for the SELECT statement, indicating the table(s) from which to retrieve data.
11. ORDER BY: Sorts the result set in ascending or descending order based on one or more columns.
12. GROUP BY: Groups the result set by one or more columns, typically used with aggregate functions like SUM, COUNT, etc.
13. HAVING: Filters the grouped result set based on specified conditions, similar to the WHERE clause but applied after the GROUP BY operation.
14. LIMIT: Limits the number of rows returned by a SELECT statement.
15. INNER JOIN: Retrieves records that have matching values in both tables being joined.
16. LEFT JOIN: Retrieves all records from the left table and the matched records from the right table.
17. RIGHT JOIN: Retrieves all records from the right table and the matched records from the left table.
18. FULL JOIN: Retrieves all records when there is a match in either the left or right table.
19. DISTINCT: Filters out duplicate rows from the result set.
20. UNION: Combines the result sets of two or more SELECT statements into a single result set.

How to Use Reserved Words as Identifiers

Suppose you have used reserved keywords as identifiers, then it will return the syntax error. For using reserved words as identifiers, it should be enclose in backticks(`). MySQL understands that it should be treated as a user-defined identifier rather than a reserved word.

Example

CREATE TABLE MyTable
(
    order_id INTEGER,
    `GROUP` INTEGER,
    `Table` INTEGER
);

In the above query, we have used two reserved words GROUP and TABLE, for using these reserved keywords as identifiers we have enclosed them in backticks(`).

Query:

DESCRIBE MyTable;

Output:

INSERT INTO MyTable (order_id, `GROUP`, `Table`) VALUES 
(1, 3, 1), 
(2, 5, 3), 
(3, 8, 11), 
(4, 12, 4), 
(5, 9, 2);

SELECT
    m1.order_id,
    m1.`GROUP`,
    m1.`Table`
FROM
    MyTable m1
JOIN
    MyTable m2 ON m1.order_id = m2.order_id
WHERE
    m1.`GROUP` > 5
ORDER BY
    m1.`Table`;