Logic Behind SQL Select Statement

Question

Lets start with a basic SQL statement like this:

SELECT CustomerName, City, Country FROM Customers;

We get something like this:

CustomerName	City	Country
Alex	Berlin	Germany

Now if I execute this statement on the same table: SELECT 'a' FROM Customers;

It gives me this result:

a
a
a
a

Why is this? There is no identifier named a. I can actually put anything in single quotes and it will just repeat it to me as many times as there are rows in the table.

Answer 1

The statement:

select ... from Customers;

Always returns one row for each row in your table Customers (what you put in the ... part does not affect how many rows you will get).

Now, talking about the ... part, this is ordinarily a comma separated list of columns and expressions to show for each row. If you use a column name, the value in that column of that row will be returned. If you use an expression, the expression will be evaluated for that row, and the result is returned. You placed a string literal (letter 'a') as the expression, so the result of that expression is string literal 'a', evaluated and returned for each of the three rows.

Normally you would write more elaborate expressions: for example, if one of your columns is called Distance, and you have values in it like 56,250, 1200, in each of your three rows, then you might like to retrieve these and combine (concatenate) a unit symbol, say a single letter 'm'. In this case you could write:

SELECT Distance+'m' as DistanceFromSea from Customers

Would return: |DistanceFromSea| |-------:| |56m| |250m| |1250m| .

Here the 'm' in the expression is not trivial, because it means something. The '+' sign is the concatenate operator).

While your 'a', all by itself, may not mean anything, there will be occasions you might like to display just a single character in a column: it could be a delimiter,for example, like '|'.

The DistanceFromSea in this example becomes the column 'alias', i.e. the name of the column value built by the expression. If you don't provide a name for an expression many database products would provide a made-up/default column name for you, generally by using the first few parts of the expression (but not providing one is not wrong).