TOP, ALL and DISTINCT clauses in SQL

There are certain clauses that are usually placed in the SELECT before the FROM and help in filtering records. These clauses are often known as predicates and in this section we will see how they work.

TOP

TOP limits the number of records returned by the query to the number specified in the clause.

SELECT TOP 4 *
FROM Customers
ORDER BY customerId DESC;

In the execution result we can see that it has only returned the last 4 records from the the Customers table.

ALL

ALL returns all records in a table. Unlike SELECT * in SQL Server, SELECT ALL must be accompanied by at least one column, so its use is often redundant and unnecessary.

SELECT ALL customerId
FROM Customers;

However ALL can be useful for comparisons using SUBSELECTS:

SELECT customerId,
      orderId,
      product,
      price
FROM Orders
WHERE price > ALL
   (SELECT price
   FROM orders
   WHERE customerId = 2)

This execution is returning the records from the Orders table whose prices are greater than the prices from customerId = 2.

The orders table has 2 records for the customerId = 2 with a price of 549,95 y 499,99 respectively, so our query returned the appropriate values .

To better understand the importance of this clause, let’s try executing the SELECT above ,without the ALL clause:

SELECT customerId,
      orderId,
      product,
      price
FROM Orders
WHERE price >
   (SELECT price
   FROM Orders
   WHERE customerId = 2)

Since the Orders table has more than one record with customerId = 2, the system is not able to establish the comparison. The ALL clause in this case would tell the system to validate all records with customerId = 2 and return the fields indicated in the main query.

DISTINCT

DISTINCT ignores records whose selected fields contain duplicate data.

SELECT DISTINCT customerId
FROM Orders
WHERE customerId IS NOT NULL
ORDER BY customerId ASC;

If we execute the same query without the DISTICT clause, we see that it returns duplicate records, since in our system there are several orders made by the same customer. which means that the Orders table contains records with duplicate customerId field.

SELECT customerId
FROM Orders
WHERE customerId IS NOT NULL
ORDER BY customerId ASC;