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 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;
|7||Paul||Williams||2696 Ocello Street||NULL|
|6||Jessie||Good||4452 Ventura Drive||790763467|
|5||Kimberly||Lee||4298 Drummond Street||778345112|
|4||Helen||Ward||711 Hershell Hollow Road||447824556|
In the execution result we can see that it has only returned the last 4 records from the the
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;
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)
|1||4||MacBook Pro M1||2449,5|
This execution is returning the records from the
Orders table whose prices are greater than the prices from
customerId = 2.
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
SELECT customerId, orderId, product, price FROM Orders WHERE price > (SELECT price FROM Orders WHERE customerId = 2)
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Orders table has more than one record with
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
2 and return the fields indicated in the main query.
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
SELECT customerId FROM Orders WHERE customerId IS NOT NULL ORDER BY customerId ASC;