MySQL column names and aliases

I have read that after select we use column-names but I have found a statement that was like this:

SELECT 'A' FROM T WHERE A = NULL;

would you lease help me? thanks (A is a column- name here?) my DBMS is MySQL

EDITED : the exact question is this that: Will the above statement produce a row (select all that apply)? Notice that ANSI_NULLS is OFF.

I want to know that the above state开发者_运维知识库ment will work? because some of you said that we should write IS NULL instead of =null

---

Based on that query, you would get a result set containing the character 'A' for each row where the column named A was equal to null.

If you actually want to see the value of the column A instead of the character 'A', you have to remove the single quotes:

SELECT A FROM T WHERE A IS NULL

Either way, you should not use = NULL. Certain RDMSs don't handle that the way you would think. The standard is to use IS NULL instead.

---

You should use

SELECT 'A' FROM T WHERE A IS NULL; 

---

There are three types of quotes in SQL.

• The single quote ' means that something is a string literal. 'A' in this instance means that it returns the character A for all rows where the column A is NULL.
• The double quote " means that something is an identifier. This is useful if the identifier has the same name as a reserved word, like select. Example: SELECT "select" FROM T selects the column select from the table T.
• The backtick quote ` works only in MySQL, and is the same as the double quote. The double quote can sometimes used for string literals in MySQL, although this is very much against the standard. MySQL has an option to conform to the standard, using SET SQL_MODE='ANSI'; where the backtick becomes invalid, and you need to use the single and double quotes instead.

An identifier without quotes is the same as an identifier with double quotes, unless it's a reserved word.

Hope this helps understand a bit more.

---

In answer to your question:

A = NULL is always false, so you will get no rows returned. To compare with NULL you must use A is NULL instead.

NULL is special in SQL, in that it is not equal to anything, even itself. Yep, (NULL = NULL) evaluates to false.

If you change it to IS NULL, then you will get a set of rows with one column, containing the character 'A' in each row. You will get one 'A' for each row in the table T where the A column is null.

You will get the letter A and not the value of the column because you have quotes around the 'A'. If you remove them, you'll get the value of A in each row (which will be null, because those are the rows you're selecting with your where clause).

If you wanted to see which rows in T had a null value for A, then you should change it to select * from T where A is null

---

Your SELECT statement has the following meaning:

"For every row of the table called T, return the string 'A' if the column A of the table T is NULL"

So, if you have 3 records where A is NULL, the output will be:

A
A
A
3 row(s) selected

The correct syntax is WHERE A IS NULL, and not WHERE A = NULL.

---

Have you tried running it on your test database to see what it does? Or was this just in reading?

Breaking down that statement, what is says is:

In the table T (FROM T), find the rows where the value of A is null (WHERE A = NULL).

For each of those rows, return an 'A'.

The result I would expect is

+--+
|T |
+--+
|A |
|A |
...
|A |
+--+

If the statement was instead:

SELECT A FROM T WHERE A = NULL;

Where the single quotes are removed, it would return a bunch of nulls, the value of the column A.

---

A is a column name, but you probably don't want single-quotes around it. I'd try...

SELECT A FROM T WHERE A IS NULL;