CODEWITHSUNDEEP
mysql
user329820
user329820

Reputation: 985

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 statement will work? because some of you said that we should write IS NULL instead of =null

Upvotes: 0

Views: 419

Answers (7)

Blorgbeard
Blorgbeard

Reputation: 103447

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

Upvotes: 2

Tor Valamo
Tor Valamo

Reputation: 33749

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.

Upvotes: 2

majorpayne27
majorpayne27

Reputation: 181

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;

Upvotes: 1

justkt
justkt

Reputation: 14766

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.

Upvotes: 1

UltraCommit
UltraCommit

Reputation: 2276

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.

Upvotes: 1

Justin Niessner
Justin Niessner

Reputation: 245399

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.

Upvotes: 8

Otávio Décio
Otávio Décio

Reputation: 74250

You should use 

SELECT 'A' FROM T WHERE A IS NULL;

Upvotes: 4

Related Questions