SQL: WHERE, ORDER BY, and LIMIT — Why Your Query May Return the Wrong Employees

A SQL query can run successfully and still return the wrong result.

Imagine your manager asks you to find the three highest-paid employees in the IT department.

You write a query, execute it, and get three rows. Everything looks fine.

But are those really the three highest-paid employees?

Let's use a simple example to understand where things can go wrong.

Sample Data

Consider the following "employees" table:

CREATE TABLE employees (
emp_id INT,
name VARCHAR(30),
dept VARCHAR(30),
salary INT
);

emp_id| name| dept| salary
101| Amit| IT| 60000
102| Neha| HR| 75000
103| Rahul| IT| 85000
104| Priya| IT| 50000
105| Kiran| IT| 90000
106| Sneha| Finance| 95000
107| Raj| IT| NULL
108| Meera| IT| 70000

Our requirement:

Find the three highest-paid employees from the IT department. Employees with missing salaries should be excluded.

1. Using LIMIT Without ORDER BY

Let's start with a query that looks reasonable:

SELECT *
FROM employees
WHERE dept = 'IT'
LIMIT 3;

This query returns three employees from the IT department.

But there is a problem.

We haven't told SQL how to sort the employees.

The database is free to return qualifying rows in an order that does not represent salary ranking.

So, getting three employees does not mean getting the three highest-paid employees.

1. Adding ORDER BY

Let's improve the query:

SELECT *
FROM employees
WHERE dept = 'IT'
ORDER BY salary DESC
LIMIT 3;

Now the records are sorted by salary, from highest to lowest.

However, there is one more detail worth understanding.

What happens when an employee's salary is NULL?

The position of NULL values in sorting depends on the database.

For example, MySQL and SQL Server generally place NULL values last with descending order, while PostgreSQL and Oracle generally place them first.

This means the result may differ between databases.

1. The Correct Query

If the requirement says employees with NULL salaries must be excluded, don't depend on the database's default sorting behaviour.

Add an explicit condition:

SELECT emp_id, name, salary
FROM employees
WHERE dept = 'IT'
AND salary IS NOT NULL
ORDER BY salary DESC
LIMIT 3;

Expected Output

emp_id| name| salary
105| Kiran| 90000
103| Rahul| 85000
108| Meera| 70000

Now we get exactly what the manager asked for.

Here's what SQL does:

1. "WHERE" filters employees belonging to the IT department.
2. "salary IS NOT NULL" removes employees without a salary.
3. "ORDER BY salary DESC" sorts employees from highest to lowest salary.
4. "LIMIT 3" returns only the first three records.

Remember These Three Clauses

Clause| Purpose
WHERE| Filters records
ORDER BY| Sorts records
LIMIT| Restricts the number of returned rows

A small mistake in any of these clauses can change the result of a business report.

One more thing: "LIMIT" is supported by MySQL and PostgreSQL. Oracle and SQL Server use different row-limiting syntax.

Try This Yourself

What would happen if we removed this condition?

AND salary IS NOT NULL

Would the query return the same three employees in MySQL, PostgreSQL, Oracle, and SQL Server?

Think about how each database handles NULL values when sorting in descending order.

Share your answer in the comments.

Learn More

For more practical examples, SQL interview questions, and explanations of filtering and sorting, visit:

"Restricting and Sorting Data in SQL — Sankalan Data Tech" (https://www.sankalandtech.com/Tutorials/sql-plsql-faq-interview/restricting-sorting-data-sql-faq.html)

SQL #SQLTutorial #SQLInterview #Database