Single Quotes, Double Quotes, and Backticks in MySQL

When you create a table or dataset, it is important to know and understand the concepts of quotes in MySQL. There are three types of quotes: single quotes, double quotes, and backticks. Understanding these quotes is essential, as misplacement may trigger an error while executing. In this blog, you will learn when to use single quotes, double quotes, and backticks in MySQL with examples.

Table of Contents:

• What are Single Quotes (‘) in MySQL?
  • When to use the single quotes (‘) in MySQL?
• What are Double Quotes (“) in MySQL?
  • When to Use the Double Quotes (“) in MySQL?
• What are Backticks (`) for Identifiers in MySQL?
  • When to Use Backticks (`) for Identifiers in MySQL?
• Key differences when using single quotes, double quotes, and backticks in MySQL
• Performance Considerations
• Use Cases
• Real-World Examples
• Conclusion

Let’s create a table to perform the usage of single quotes, double quotes, and backticks in MySQL.

-- Create the flight table
CREATE TABLE flights (
    flight_number INT NOT NULL PRIMARY KEY,
    departure_city VARCHAR(50) NOT NULL,
    arrival_city VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL
);

-- Insert sample data
INSERT INTO flights (flight_number, departure_city, arrival_city, status) VALUES
(101, 'New York', 'London', 'On Time'),
(202, 'Paris', 'Dubai', 'Delayed'),
(303, 'India', 'Tokyo', 'Cancelled'),
(404, 'Dubai', 'India', 'On Time'),
(505, 'Singapore', 'Sydney', 'Delayed');
SELECT * FROM flights;

Output:

What are Single Quotes (‘) in MySQL?

In MySQL, a single quote (‘) is essential for string literals. It is used to represent string values in queries like SELECT, INSERT, and UPDATE statements.

Syntax:

SELECT * FROM table_name WHERE column_name = 'string_value';

Example:

SELECT * FROM flights WHERE departure_city = 'Paris';

Output:

Explanation: Here, the SELECT query used the single quotes (‘) for the flight that needs to be fetched, and by using the WHERE condition, the specified departure city has been fetched.

When to use the single quotes (‘) in MySQL?

You can use the single quotes (‘) when you need to compare any strings in a table inside the WHERE clause. You can also use this single quote when you need to mention or add a new string to the table.

What are Double Quotes (“) in MySQL?

The Double Quotes (“) in MySQL perform the same as single quotes (‘). It is used to specify the string values in a table. But you have to check that when you are using double quotes, the ANSI mode is turned off in your compiler. If ANSI mode is on, double quotes will be treated as backticks for enclosing identifiers.

Syntax:

SELECT "column_name" FROM "table_name";

Condition 1: When ANSI mode is OFF

SELECT "Flight Code", "Status" FROM flights;

Output:

Explanation: Here, when the ANSI mode is OFF, the compiler will take the value as a string, so instead of retrieving the values in the column, it will print the literal text that was mentioned inside the double quotes.

Condition 2: When the ANSI mode is ON

SET sql_mode = 'ANSI_QUOTES';
SELECT "flight_number", "status" FROM flights;

Output:

Explanation: Here, when the ANSI mode is ON, the double quotes will behave as backticks for identifiers, so instead of printing the string, the ANSI_QUOTES will treat the value inside the double quote as a column name and fetch the values inside that particular column.

When to Use the Double Quotes (“) in MySQL?

• The double quotes (“) in MySQL will be very efficient when you use them when the ANSI mode is ON. 
• It will change the compiler from SQL_mode to ANSI_QUOTES mode and treat the double quotes (“) strings as backticks. 
• It is not the default in MySQL server, so using SET sql_mode = ‘ANSI_QUOTES’ turns ON the ANSI mode.

What are Backticks (`) for Identifiers in MySQL?

The backticks in MySQL are used for identifiers, which are used to include the column names, table names, and database names that have reserved keywords or special characters inside.

Syntax:

SELECT `column_name` FROM `table_name`;

Example:

SELECT `flight_number`, `departure_city`, `status` FROM `flights`;

Output:

Explanation: Here, the backticks included the column name mentioned with the help of backticks (`). It retrieves the values that are present in the name of column that is mentioned.

When to Use Backticks (`) for Identifiers in MySQL?

• It is necessary to use Backticks (`) for Identifiers when there are special characters or reserved keywords. 
• It is useful as it prevents case sensitivity issues in MySQL.
• It can be used when you need to fetch a specific column in a table.

Key differences when using single quotes, double quotes, and backticks in MySQL

Performance Considerations

• Single Quotes (‘): The single quotes will perform less efficiently when they are used in numeric values.
• Double Quotes (“): There will be an error when it is used without enabling ANSI mode. You have to make sure that the ANSI_QUOTES mode is ON before using double quotes.
• Backticks (`): It has no performance impact, but should be used when there are reserved keywords or special characters to avoid complications.

Use Cases

1. The quotes can be used to compare the text values between the strings.
2. The single quotes can be used to insert the values into the table.
3. The backticks are used to query the columns or databases that have special characters.
4. The double quotes are used in the ANSI mode to query the identifiers.

Real-World Examples

Case 1: To retrieve appointment details for a specific patient

CREATE TABLE `patients` (
    `patient_id` INT PRIMARY KEY,
    `patient_name` VARCHAR(100) NOT NULL,
    `condition` VARCHAR(100) NOT NULL,
    `appointment_date` DATE NOT NULL,
    `status` VARCHAR(50) NOT NULL
);
INSERT INTO `patients` (`patient_id`, `patient_name`, `condition`, `appointment_date`, `status`) VALUES
(1, 'Dhanu', 'Diabetes', '2025-03-20', 'Scheduled'),
(2, 'Prithvi', 'Asthma', '2025-03-21', 'Completed'),
(3, 'Arun', 'Hypertension', '2025-03-22', 'Cancelled');
SELECT * FROM `patients` WHERE `condition` = 'Diabetes';

Output:

Explanation: Here, the single quotes mention the name of the disease, and the WHERE condition fetched the details of the patient who has diabetes.

Case 2: To check the status of the movie ticket that has been booked before.

CREATE TABLE `bookings` (
    `booking_id` INT PRIMARY KEY,
    `customer_name` VARCHAR(100) NOT NULL,
    `movie_name` VARCHAR(100) NOT NULL,
    `show_time` DATETIME NOT NULL,
    `status` VARCHAR(50) NOT NULL
);
INSERT INTO `bookings` (`booking_id`, `customer_name`, `movie_name`, `show_time`, `status`) VALUES
(101, 'Anjum', 'Chaava', '2025-03-20 18:30:00', 'Confirmed'),
(102, 'Bhuvan', 'Mowgli', '2025-03-21 20:00:00', 'Cancelled'),
(103, 'Gaurav', 'Joker', '2025-03-22 17:00:00', 'Confirmed');
SET sql_mode = 'ANSI_QUOTES';  
SELECT "customer_name", "status" FROM bookings;

Output:

Explanation: Here, the double quotes fetched the status of bookings with their customer name when the ANSI mode is ON.

Case 3: To retrieve all the loan details from the bank, which are sorted by application date.

Example:

CREATE TABLE `loans` (
    `loan_id` INT PRIMARY KEY,
    `customer_name` VARCHAR(100) NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `type` VARCHAR(50) NOT NULL, 
    `status` VARCHAR(50) NOT NULL, 
    `date` DATE NOT NULL 
);
INSERT INTO `loans` (`loan_id`, `customer_name`, `amount`, `type`, `status`, `date`) VALUES
(1, 'John Doe', 50000.00, 'Home Loan', 'Approved', '2025-04-01'),
(2, 'Jane Smith', 20000.00, 'Car Loan', 'Pending', '2025-04-05'),
(3, 'Alice Brown', 10000.00, 'Personal Loan', 'Rejected', '2025-04-10');
SELECT * FROM `loans` ORDER BY `date` ASC;

Output:

Explanation: Here, the ‘type,’ ‘status,’ and ‘date’ are reserved keywords that fetched the status and date of the loan.

Conclusion

The quotes are very important to write an error-free query in SQL. Single quotes (‘) are used to add text as string literals, double quotes (“) are used in the ANSI mode, and backticks (`) are used when there are special characters. You can use these quotes based on your requirements.From this, you would have understood when to use the single quotes (‘), double quotes (“), and backticks (`) in MySQL.

Take your skills to the next level by enrolling in our SQL Training Course today and gain hands-on experience. Also, prepare for job interviews with our SQL interview questions, prepared by industry experts.

When to use single quotes, double quotes, and backticks in MySQL – FAQs