What is the meaning of a constant in a SELECT query?

Considering the 2 below queries:

1)

USE AdventureWorks
GO
SELECT a.ProductID, a.ListPrice
FROM Production.Product a
WHERE EXISTS (SELECT 1 FROM Sales.SalesOrderDetail b
          WHERE b.ProductID = a.ProductID)

2)

USE AdventureWorks
GO
SELECT a.ProductID, a.Name, b.SalesOrderID
FROM Production.Product a LEFT OUTER JOIN Sales.SalesOrderDetail b
ON a.ProductID = b.ProductID
ORDER BY 1

My only question is know what is the meaning of the number 1 in those queries? How about if I change them to 2 or something else?

Thank开发者_如何学Cs for helping

In the first case it does not matter; you can select a 2 or anything, really, because it is an existence query. In general selecting a constant can be used for other things besides existence queries (it just drops the constant into a column in the result set), but existence queries are where you are most likely to encounter a constant.

For example, given a table called person containing three columns, id, firstname, lastname, and birthdate, you can write a query like this:

select firstname, 'YAY'
from person
where month(birthdate) = 6;

and this would return something like

name     'YAY'
---------------
Ani       YAY
Sipho     YAY
Hiro      YAY

It's not useful, but it is possible. The idea is that in a select statement you select expressions, which can be not only column names but constants and function calls, too. A more likely case is:

select lastname||','||firstname, year(birthday)
from person;

Here the || is the string concatenation operator, and year is a function I made up.

The reason you sometimes see 1 in existence queries is this. Suppose you only wanted to know whether there was a person whose name started with 'H', but you didn't care who this person was. You can say

select id
from person
where lastname like 'H%';

but since we don't need the id, you can also say

select 1
from person
where lastname like 'H%';

because all you care about is whether or not you get a non-empty result set or not.

In the second case, the 1 is a column number; it means you want your results sorted by the value in the first column. Changing that to a 2 would order by the second column.

By the way, another place where constants are selected is when you are dumping from a relational database into a highly denormalized CSV file that you will be processing in NOSQL-like systems.

In the second case the 1 is not a literal at all. Rather, it is an ordinal number, indicating that the resultset should be sorted by its first column. If you changed the 1 to 4 the query would fail with an error because the resultset only has three columns.

BTW, the reason you use a constant like 1 instead of using an actual column is you avoid the I/O of actually getting the column value. This may improve performance.