Many programming languages allow you to comment out code so that it does not run. SQL allows comments as well. Learn how to add SQL comments in this article.
What is an SQL Comment?
A comment in SQL is a piece of text in your code that is not executed when it is run on the database.
This comment could be in a simple SQL query editor, an SQL file, or code that has been named such as a stored procedure.
Why would you have text in your code that you don’t want to run?
Some examples are:
- Labelling an area of the code to make it easier for you or others to understand
- Removing a column from being shown in the output of a SELECT query but still having the name in the query to make it easier to edit later
- Disabling lines of a WHERE clause to check data and performance
Let’s take a look at how to add a comment in SQL.
How to Comment in SQL
To add a comment in SQL, you can either:
- begin your line with two dashes “–“
- enclose your code in a forward slash and asterisk “/*”
MySQL also allows for the hash symbol “#” to be used.
There are two types of comments in SQL: single-line and multi-line (also known as a block comment).
A single-line comment means only the one line is commented out. You can apply the same syntax to another line, but it just takes a bit more time.
A multi-line comment is where multiple lines of code are commented out with the use of the symbols.
Let’s see what syntax can be used in each database:
-- OR #
Let’s take a look at some examples of these types of comments below.
Single Line SQL Comment
A single-line comment is where you add two dashes “–” (or you can also use a hash “#” in MySQL) before a line of code. The code will then be “commented out” and will not be run when you run your statement. It will also show a different colour if you’re using an IDE (such as SQL Developer or SQL Server Management Studio).
--Show all data SELECT * FROM student;
If you run this code, the line of “–Show all data” is not run. The two dashes at the start indicate it’s a comment. It’s for display purposes only.
Comment Out Column in SELECT Clause
Another example of a comment is to exclude a column from being displayed in the SELECT clause:
SELECT id, --first_name, last_name, date_of_birth FROM student;
When this query is run, the first_name column is not shown in the output. It’s in the query here, but the dashes means it is treated as a comment.
Comment Out Column with Leading Commas
Let’s say you have a query that has a range of columns and you want to comment one of them out.
SELECT id, first_name, last_name, --date_of_birth FROM student;
If you run this query, you might think it will run and just not show the date of birth. However, you’ll get an error, because the query is essentially doing this:
SELECT id, first_name, last_name, FROM student;
See the comma at the end of last_name? This means the query is expecting another column, which causes a syntax error.
How can you resolve this?
You could remove the comma in this situation:
SELECT id, first_name, last_name --date_of_birth FROM student;
But then if you want to show the date_of_birth column again, you’ll need to add the comma back in. If you forget to do this, then the last_name column will be shown with an alias of date_of_birth.
SELECT id, first_name, last_name date_of_birth FROM student;
What’s another way of doing this?
Use leading commas on columns lists.
You might have seen this before:
SELECT id ,first_name ,last_name ,date_of_birth FROM student;
This SQL will still run correctly, even with the commas at the start of the line. However, the benefit is that you can comment and uncomment any of the columns (except the ID) without any issues:
SELECT id ,first_name ,last_name --,date_of_birth FROM student;
So if you ever see any queries written like this, then the reason is to make it easier to comment out parts of it.
SQL Comments in WHERE Clause
You can comment out any part of an SQL statement. Another common way to use comments is to exclude sections of a WHERE clause:
SELECT id, first_name, last_name, date_of_birth FROM student WHERE status = 'Enrolled' --AND amount_owing = 0 ;
Adding a comment will ensure the query still runs, and the code is still there for you to re-enable later.
SQL Comments in WHERE Clause and 1=1
You also might have seen code like this before:
SELECT id, first_name, last_name, date_of_birth FROM student WHERE 1=1 AND status = 'Enrolled' AND amount_owing = 0 ;
Why is there a clause there that says 1=1? That’s always true. So why add it?
It’s there to make it easier to comment out lines of the WHERE clause. Without it, you would have to make a few little adjustments if you wanted to exclude the status=’Enrolled’ line. With this trick, you can just add a comment to the start of the line.
SELECT id, first_name, last_name, date_of_birth FROM student WHERE 1=1 --AND status = 'Enrolled' AND amount_owing = 0 ;
Comment at End of Line
You can use the single-line comment to add a comment at the end of a line. Anything after the — characters is commented out.
SELECT id, first_name, last_name, date_of_birth FROM student WHERE 1=1 AND status = 'Enrolled' --Only show students that are enrolled AND amount_owing = 0 ;
This can help explain part of the statement.
MySQL Comment Alternative
Oracle, SQL Server, MySQL, and PostgreSQL support using two dashes “–” for a single-line comment.
MySQL comments can also be added using the hash “#” character. You can use that as a comment character instead, if you like:
#Show all data SELECT * FROM student;
It performs in the same way as a two dash “–” comment, but it’s MySQL specific.
Multi-Line Comment or Block Comment
The other type of comment is a multi-line comment. This allows you to enclose one or more lines of code in forward slashes and asterisk characters “/*” and “*/” to comment it out.
You can use this on a single line, a part of a line, or on multiple lines.
Let’s see some examples.
Single-Line Comment on Multiple Lines
You can use the single-line comment on multiple lines. It’s just more time consuming to do.
SELECT id --first_name, --last_name, --date_of_birth FROM student WHERE 1=1 AND status = 'Enrolled' AND amount_owing = 0 ;
Adding “–” characters to many lines takes time. You can use the multi-line comment to do this easier.
Multi-Line SQL Comment
To comment out multiple lines at once, enclose the text you want to comment out inside /* and */ characters:
SELECT id /*first_name, last_name, date_of_birth*/ FROM student WHERE 1=1 AND status = 'Enrolled' AND amount_owing = 0 ;
You can see here that several lines of the SQL statement are commented out.
Multi-Line Comment Outside of Statement
You can add an SQL comment outside of the SQL statement using this syntax as well:
/* This query finds all records in the student table */ SELECT * FROM student;
The statement being commented does not need to be on its own line. Anything in between the two /* and */ symbols is treated as a comment.
SQL Comment in Part of an SQL Statement
You can comment out part of a line in an SQL statement using multi-line comments.
Here’s our SQL statement before adding the comment.
SELECT id, first_name, last_name, date_of_birth FROM student WHERE status IN ( 'Enrolled', 'Pending', 'Cancelled');
Let’s say we want to remove the check for Pending records. We can comment just that part out.
SELECT id, first_name, last_name, date_of_birth FROM student WHERE status IN ( 'Enrolled', /*'Pending'*/, 'Cancelled');
The Pending check is ignored and the statement will run.
SQL comments allow you to exclude parts of your code from running but still have them display in your script. This helps with testing your code and making code easier to read for yourself and others.