Queries

The Transact-SQL language has one basic statement for retrieving information from a database: the SELECT statement. With this statement, it is possible to query information from one or more tables of a database (or even from multiple databases). The … [Read more...] about SQL SELECT – Its Clauses and Functions

Often, it is necessary to define one or more conditions that limit the selected rows. The WHERE clause specifies a Boolean expression (an expression that returns a value of TRUE or FALSE) that is tested for each row to be returned (potentially). If … [Read more...] about WHERE Clause – SELECT Statement

WHERE clause conditions can either be simple or contain multiple conditions. Multiple conditions can be built using the Boolean operators AND, OR, and NOT. The behavior of these operators was described in section "NULL Values" using truth … [Read more...] about Boolean operators AND, OR & NOT – WHERE Clause

An IN operator allows the specification of two or more expressions to be used for a query search. The result of the condition returns TRUE if the value of the corresponding column equals one of the expressions specified by the IN … [Read more...] about IN and BETWEEN Operators – WHERE Clause

A NULL in the CREATE TABLE statement specifies that a special value called NULL (which usually represents unknown or not applicable values) is allowed in the column. These values differ from all other values in a database. The WHERE clause of a … [Read more...] about NULL Values, LIKE Operator in WHERE Clause

The GROUP BY clause defines one or more columns as a group such that all rows within any group have the same values for those columns. Example 6.22 shows the simple use of the GROUP BY clause in a SELECT statement. Example 6.22 Get all jobs of … [Read more...] about GROUP BY and HAVING Clauses – SELECT Statement

Aggregate functions are functions that are used to get summary values. All aggregate functions can be divided into several groups: Convenient aggregate functionsStatistical aggregate functionsUser-defined aggregate functionsAnalytic aggregate … [Read more...] about Aggregate Functions in SELECT Statement

The ORDER BY clause defines the particular order of the rows in the result of a query. This clause has the following syntax: The col_name column defines the order. col_number is an alternative specification that identifies the column by its … [Read more...] about ORDER BY Clause – SQL Server

The IDENTITY property allows you to specify a counter of values for a specific column of a table. Columns with numeric data types, such as TINYINT, SMALLINT, INT, and BIGINT, can have this property. The Database Engine generates values for such … [Read more...] about SELECT Statement and IDENTITY Property

Using the IDENTITY property has several significant disadvantages, the most important of which are the following: You can use it only with the specified table.You cannot obtain the new value before using it.You can specify the IDENTITY property … [Read more...] about CREATE SEQUENCE Statement

In addition to the operators described earlier in the chapter, three set operators are supported in the Transact-SQL language: UNIONINTERSECTEXCEPT UNION Set Operator The result of the union of two sets is the set of all elements appearing … [Read more...] about Set Operators (UNION, INTERSECT and EXCEPT)

In database application programming, it is sometimes necessary to modify the representation of data. For instance, a person’s gender can be coded using the values 1, 2, and 3 (for female, male, and child, respectively). Such a programming technique … [Read more...] about CASE Expressions in SELECT Statements

All previous examples in this chapter contain comparisons of column values with an expression, constant, or set of constants. Additionally, the Transact-SQL language offers the ability to compare column values with the result of another SELECT … [Read more...] about Subqueries (Comparison, IN, ANY and ALL Operators)

A temporary table is a database object that is temporarily stored and managed by the database system. Temporary tables can be local or global. Local temporary tables have physical representation—that is, they are stored in the tempdb system database. … [Read more...] about Temporary Tables in SQL Server

The previous sections of this chapter demonstrated the use of the SELECT statement to query rows from one table of a database. If the Transact-SQL language supported only such simple SELECT statements, the attachment of two or more tables to retrieve … [Read more...] about SQL JOIN – JOIN Operator (Explicit join, Implicit join)

The phrases “natural join” and “equi-join” are often used as synonyms, but there is a slight difference between them. The equi-join operation always has one or more pairs of columns that have identical values in every row. The operation that … [Read more...] about Natural Join – Cartesian Product

In the previous examples of natural join, the result set included only rows from one table that have corresponding rows in the other table. Sometimes it is necessary to retrieve, in addition to the matching rows, the unmatched rows from one or both … [Read more...] about Outer Join – JOIN Operator