MySQL Reserved Words

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:

Explanation: As shown in the above table, We have created a table where column name is reserved words. For this, we have used backticks(`).

Inserting Data into the Table

Let's insert some data in this table and fetch the data.

For inserting the data run the following query.

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

Now we have successfully inserted the data in our table. Now our table will look something like this.

Fetching Data from the Table

Now let's fetch the data from this table. As we have used reserved words as identifiers in our table, we have to enclose them in backticks if we want to use them as identifiers. Let's implement this using a query where we use both reserved words and reserved words as identifiers.

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`;

Output:

Explanation: As shown in the above table, we have used included reserved words and reserved words as identifiers. So if we want to use reserved words as identifiers, we have to enclose them in backticks(`) as shown in the query.

Conclusion

These reserved words are integral to MySQL's functionality, acting as predefined commands that dictate how data is stored, retrieved, and manipulated. Using these words appropriately ensures that your SQL queries execute correctly and maintain the integrity of your database operations. Staying informed about MySQL's reserved words and best practices will facilitate smoother development processes and more robust database systems.