Is SQL case-sensitive? The question seems simple, and you may think it’s a yes-no question. However, the answer depends on multiple factors. Let’s take a look at the possible answers.
First of all, this question can be understood in multiple ways. Which part of SQL are you asking about? Do you mean keywords (e.g., SELECT, WHERE, etc.), table and column names, or values in the columns? Which database management system (DBMS) do you have in mind?
To answer this question in the broadest way possible, we’ll go through the case sensitivity of different parts of SQL. Within each part, we’ll talk about the different DBMSs.
Before we begin, let’s make sure we stay on the same page with the vocabulary. If any terms in the previous paragraphs sound weird and unfamiliar to you, you may want to check the article “SQL Terms Beginners Should Know.” It guides you through the basic SQL vocabulary like table, database, and DBMS.
Are SQL Keywords Case-Sensitive?
Let’s start there. Keywords in SQL are case-insensitive for the most popular DBMSs. The computer doesn’t care whether you write SELECT, select, or sELeCt; so, in theory, you can write however you like.
Unfortunately, it is a little bit different in practice. If you’re thinking about working professionally with SQL, you have to consider it involves collaborating with other engineers. This means you’ll have to read each other's code.
Because of this, you need a convention whether to write keywords in all capital letters, capitalize only the first letter, or leave everything in lowercase letters. This way, everyone will read and understand the code quicker. Most often, SQL keywords are written in capital letters.
This also applies if you’re a student collaborating with other students. You may have different courses with different projects, and switching between them is less time-consuming with a clear code style.
If you’re still at the beginning of your programming journey, you may want to know how to learn the SQL syntax, as learning the syntax also includes learning keywords.
Are SQL Table and Column Names Case-Sensitive?
The next is table and column names. This topic is a little bit more complicated.
Unfortunately, different DBMSs work differently. In addition, the behavior depends on the operating system (i.e., Windows, macOS, or Linux). The user can also impact case sensitivity.
We won’t discuss the topic in detail, as there are many ways to customize the behavior. We’ll focus on the three most popular relational databases, all of which are taught at learnSQL.com: PostgreSQL, MySQL, and MS SQL Server. For more information, refer to your DBMS documentation.
Let’s start with PostgreSQL. In this database, table names are case-insensitive. This behavior doesn’t depend on the operating system. However, you can easily modify this by adding double quotation marks (") to the table name. This forces PostgreSQL to be case-sensitive for table names. The same goes for column names.
Then, we have MySQL. By default, it depends on the operating system and its case sensitivity. This means MySQL is case-insensitive in Windows and macOS, while it is case-sensitive in most Linux systems. However, you can change the behavior by changing collation.
Last but not least: MS SQL Server. By default, it’s case-insensitive, and like with MySQL, you can change this behavior by modifying the collation setting.
Keep in mind: even though you can use different cases for your code, it doesn’t mean you should. Have a clear code style in your projects so that it’s easier to read and navigate. In PostgreSQL and MySQL, table names are most often written in lowercase (or more precisely, snake_case). In MS SQL Server, the most common practice is to use PascalCase.
Differences in case sensitivity can lead to problems in executing your queries. For this reason, you need to understand the error messages you can get. If you’re new to SQL, we recommend reading the article “6 Common SQL Syntax Mistakes All Learners Make,” which helps you with the most common errors everyone gets at the beginning of the SQL journey.
Are Column Values in SQL Case-Sensitive?
Last but not least, let's talk about column values. It also differs a little based on the DBMS. Fortunately, though, this one is simpler.
By default, PostgreSQL performs a case-sensitive string comparison. This means WHERE column = 'abc' returns TRUE only for values in the column exactly equal to 'abc'. The same goes for the LIKE operator. column LIKE 'ABC' or column LIKE 'aBc' will return FALSE for such comparison. To do a case-insensitive comparison, use the ILIKE keyword; e.g., column ILIKE 'aBc' and column ILIKE 'ABC' both return TRUE for 'abc'.
In contrast, MySQL and MS SQL Server have case-insensitive behaviors by default. This means WHERE column = 'abc' returns TRUE for e.g., 'abc', 'ABC', or 'aBc'. The same goes for the LIKE operator.
In all three DBMSs, you can change the collation setting to modify the default behavior.
In this section, we have mentioned concepts as LIKE and string comparisons. If you’re new to SQL, these terms may be confusing. If you haven’t heard about them or you don’t feel confident using them, you may want to check the SQL Basics course. It guides you through the basics of SQL, including string comparison.
So, Is SQL Case-Sensitive?
Is SQL case-sensitive? As you can see, it depends on what you mean by the question. There isn’t a simple yes or no answer. However, you now know the general answer to this question. Furthermore, you know when in doubt to refer to the documentation of the DBMS you’re using, as every database can have different default behaviors.
If this article provided you with information you didn’t know, you may want to check out our SQL from A to Z bundle. These courses guide you through SQL and allow you to navigate comfortably through many SQL articles and conversations.
And remember: even if some concepts are hard to understand at first, you can learn everything with enough patience. Good luck and happy learning!
