The predicate is included between the clause and the first name of the field to retrieve, the possible predicates are:
- ALL: Returns all the fields in the table
- TOP: Returns a certain number of records from the table
- DISTINCT: Skip records whose selected fields match completely
- DISTINCTROW: Skip duplicate records based on the entire record and not just the selected fields.
ALL (* can be used instead):
Advertisements
If none of the predicates are included, ALL is assumed. The Database Engine selects all records that meet the conditions of the SQL statement. It is not convenient to abuse this predicate since we force the database engine to analyze the structure of the table to find out the fields it contains, it is much faster to indicate the list of fields desired.
Advertisements
– SELECT ALL FROM Employees;
– SELECT * DESDE Employees;
Advertisements
TOP
Returns a certain number of records that fall between the beginning or the end of a range specified by an ORDER BY clause. Suppose we want to retrieve the names of the first 25 students of the 1994 academic year:
Advertisements
- SELECT TOP 25 First name, Last name DESDE Students
- ORDER BY Note DESC;
Advertisements
If the clause is not included ORDER BY, the query will return an arbitrary set of 25 records from the Students table. The predicate TOP it does not choose between equal values. In the example above, if the 25th grade and the 26th grade average are equal, the query will return 26 records. You can use the reserved word PERCENT to return a certain percentage of records that fall at the beginning or end of a range specified by the clause ORDER BY. Suppose instead of the first 25 students we want 10 percent of the course:
- SELECT TOP 10 PERCENT Name last Name DESDE Students ORDER BY Note DESC;
The value that follows TOP must be an unsigned Integer.TOP it does not affect the possible update of the query.
DISTINCT
Skip the records that contain duplicate data in the selected fields. So that the values of each field listed in the statement SELECT included in the query must be unique.
For example, multiple employees listed in the Employees table can have the same last name. If two records contain Lopez in the Last Name field, the following statement SQL returns a single record:
- SELECT DISTINCT Last name DESDE Employees;
In other words the predicate DISTINCT returns those records whose fields indicated in the clause SELECT have a different content. The result of a query that uses DISTINCT it is not upgradeable and does not reflect subsequent changes made by other users.
DISTINCTROW
Returns the different records from a table; Unlike the previous predicate that only looked at the content of the selected fields, it does so in the content of the complete record regardless of the fields indicated in the clause SELECT.
- SELECT DISTINCTROW Last name DESDE Employees;
If the employees table contains two records: Antonio López and Marta López, the example of the predicate DISTINCT returns a single record with the value Lopez in the Last name field since it looks for no duplicates in that field. This last example returns two records with the value Lopez in the last name as non-duplicates are being searched for in the entire record.
