SQL Study Notes: The From & Select Clauses
The FROM clause is the first clause to be evaluated logically in a SELECT query and has two main roles:
- it’s the clause where you indicate the tables that you want to query
- it’s the clause where you can apply table operators like joins to input tables
In some cases, T-SQL supports omitting the schema name, as in FROM Employees, in which case it uses an implicit schema name resolution process. It is considered a best practice to always explicitly indicate the schema name. This practice can prevent you from ending up with a schema name that you did not intend to be used, and can also remove the cost involved in the implicit resolution process, although this cost is minor. In the FROM clause, you can alias the queried tables with your chosen names.
Note that if you assign an alias to a table, you basically rename the table for the duration of the query and therefore the original table name will no longer be visible. Normally, you can prefix a column name you refer to in a query with the table name, as in Employees.empid. however, if you aliased the Employees table as E, the reference Employees.empid is invalid; you have to use E.empid, as the following example demonstrates.
SELECT Employees.empid, firstname, lastname FROM HR.Employees AS E;
If you try running this code by using the full table name as the column prefix, the code will fail.
SELECT E.empid, firstname, lastname FROM HR.Employees AS E;
The SELECT clause of a query has two main roles:
- it evaluates expressions that define the attributes in the query’s result, assigning them with aliases if needed
- Using a DISTINCT clause, you can eliminate duplicate rows in the result if needed
In the SELECT clause, you can assign your own aliases to the expressions that define the result attributes. There are a number of supported forms of aliasing:
- <expression> AS <alias> – "empid AS employeeid"
- <expression> <alias> – "empid employeeid"
- <alias> = <expression> "employeeid = empid"
Aliasing the expression, you assign a name to the result attribute, making the result of the query relational, as follows.
SELECT empid, firstname + N' ' + lastname AS fullname FROM HR.Employees;
Here’s an abbreviated form of the result of this query.
<table width="300" border="0" cellspacing="0" cellpadding="1"> <tr> <td> empid </td> <td> fullname</td> </tr> <tr> <td>-----------</td> <td> ------------------------------- </td> </tr> <tr> <td>1</td> <td>John Candy </td> </tr> <tr> <td>2</td> <td>Steve Martin</td> </tr> <tr> <td>3</td> <td>John Belushi </td> </tr> </table>
If duplicates are possible in the result, T-SQL won’t eliminate unless instructed. Relations, being sets; do not have duplicates therefore a result with duplicates is considered non-relational. Therefore, if duplicates are possible in the result, and you want to eliminate them in order to return a relational result. Adding the DISTINCT clause eliminates duplicates
SELECT DISTINCT country, county, town FROM HR.Employees;
The difference between standard SQL and T-SQL. Standard SQL, a SELECT query must have at minimum FROM and SELECT clauses. T-SQL supports a SELECT query with only a SELECT clause and without a FROM clause. Such a query is as if issued against an imaginary table that has only one row.
Identifiers of attributes, schemas, tables, and other objects. There are cases in which you are required to use delimiters. T-SQL supports both a standard form to delimit identifiers using double quotation marks.
- [Sales].[Orders] which is the propretary form
When the identifier is “regular,” delimiting it is optional. In a regular identifier, the identifier complies with the rules for formatting identifiers.
The Rules for Formatting Identifiers
- The first character must be a letter in the range A through Z (lower or uppercase), underscore (_), at sign (@), or number sign (#) NOT a number!
- Subsequent characters can include letters, decimal numbers, at sign, dollar sign ($), number sign, or underscore.
- The identifier cannot be a reserved keyword in T-SQL
- The identifier cannot have embedded spaces
- The identifier must not include supplementary characters
An identifier that doesn’t comply with these rules must be delimited eg "2013"this requires delimiting as it starts with a number as does column names that contain spaces such as "phone number" which is the proprietary way of delimiting or [phone number] which is the standard form. T-SQL supports both.