What happens when a comma is not placed following an existing field name in SQL

Admin 1
Viewed 255

This is with regard to the SQL Tryit Editor provided by W3 Schools. (Saying this just in case it is an environment-specific problem)

The code SELECT CustomerName, Address, City, PostalCode FROM Customers; yields the following output,

enter image description here

While code SELECT CustomerName, Address City, PostalCode FROM Customers; is executed, I get the following result

enter image description here

Clearly, the missing comma has resulted in not displaying the Address field. But this is distinct from the error message that otherwise comes when a field name is either not correct or not correctly separated. It seems to recognize Address to be an existing field, while it considers to display only the field immediately before the comma.

What is the logic behind the correct use of commas? Does that mean any number of correctly named fields, but without a follow up comma, will be ignored?

select sql
3 Answers

Commas are used to separate the fields, when you miss a comma like you have, the 2nd column will be considered as the column alias. Here Address City means, pick the column Address and name it City. You can see in your 2nd screenshot, values from Address column are picked but the name of the column is City.

user_147378040

Some SQL dialects (notably MySQL) interpret this as an alias: They read

SELECT CustomerName, Address City, PostalCode FROM Customers;

as if it were

SELECT CustomerName, Address AS City, PostalCode FROM Customers;

which of course means: Select the Address column, but rename it to City

There are good uses for aliases, notably when you want operations (mostly joins) to run on the same column but with a different context.

user_11005520

@Pale is right.

Your query

SELECT CustomerName, Address City, PostalCode FROM Customers;

is equivalent to

SELECT CustomerName, Address AS City, PostalCode FROM Customers;

We can clearly see on your second screenshot that the data from Address is in the column called City.

However, this would most likely not work (notice the third missing comma)

SELECT CustomerName, Address City PostalCode FROM Customers;

That being said, I'm not sure if the missing AS would work in all DB engines.

More info on aliases can be found here.

https://www.w3schools.com/sql/sql_alias.asp

user_43464530
Related