DISTINCT, the WHERE clause with all its operators (=, <>, IN, BETWEEN, LIKE), wildcards, combining conditions with AND/OR, sorting with ORDER BY, and limiting results with TOP. All examples use fresh, realistic data.
Our Sample Table
Let's create a simple Employees table that we'll use throughout the article. Every query below works against this data.
CREATE TABLE Employees (
EmpId INT PRIMARY KEY,
FullName VARCHAR(100),
City VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10, 2),
Email VARCHAR(100)
);
INSERT INTO Employees VALUES
(1, 'Anita Sharma', 'Mumbai', 'Engineering', 75000, 'anita@shop.com'),
(2, 'Rohit Verma', 'Bangalore', 'Marketing', 58000, 'rohit@shop.com'),
(3, 'Priya Das', 'Mumbai', 'Engineering', 92000, 'priya@shop.com'),
(4, 'Karan Malhotra','Pune', 'Sales', 45000, 'karan@shop.com'),
(5, 'Meera Iyer', 'Bangalore', 'Engineering', 88000, 'meera@shop.com'),
(6, 'Vikram Singh', 'Delhi', 'Sales', 61000, 'vikram@shop.com'),
(7, 'Nisha Patel', 'Mumbai', 'Marketing', 54000, 'nisha@shop.com');
Selecting Columns
The SELECT statement picks what you want to see. Use * to get every column, or list the ones you want — which is almost always the better choice.
SELECT * FROM Employees; -- all columns, all rows
SELECT FullName, Salary FROM Employees; -- only the ones you need
SELECT * in production queries. Listing columns is faster, safer, and protects your app from breaking when new columns are added to the table.
Fully Qualified Table Names
SQL Server lets you write the full path to a table — Database.Schema.Table. This makes queries run correctly no matter which database you are currently connected to.
SELECT * FROM SampleDB.dbo.Employees;
Here SampleDB is the database, dbo is the schema, and Employees is the table. Useful in stored procedures and cross-database queries.
Removing Duplicates with DISTINCT
DISTINCT removes duplicate rows from the result. It works on whatever columns you list — the uniqueness is across the entire combination, not each column individually.
SELECT DISTINCT City FROM Employees;
-- Returns: Mumbai, Bangalore, Pune, Delhi
SELECT DISTINCT City, Department FROM Employees;
-- Returns unique City+Department pairs, not each column alone
DISTINCT dedupes one column at a time. It doesn't — it treats the entire row you select as a single unit and removes exact duplicates of that whole row.
Filtering Rows with WHERE
The WHERE clause keeps only the rows that match your condition. Everything else is thrown away.
SELECT FullName, Salary
FROM Employees
WHERE City = 'Mumbai';
Comparison Operators
| Operator | Meaning | Example |
|---|---|---|
= | Equal to | City = 'Pune' |
<> or != | Not equal to | Department <> 'Sales' |
> / >= | Greater than / or equal | Salary >= 60000 |
< / <= | Less than / or equal | Salary <= 50000 |
Both <> and != work for "not equal", but <> is the ANSI SQL standard and is preferred for clean code.
The IN Operator: Match Any of Many
IN is a short way to write many OR conditions against the same column.
-- Long way
SELECT * FROM Employees
WHERE City = 'Mumbai' OR City = 'Delhi' OR City = 'Pune';
-- Clean way with IN
SELECT * FROM Employees
WHERE City IN ('Mumbai', 'Delhi', 'Pune');
-- Opposite with NOT IN
SELECT * FROM Employees
WHERE City NOT IN ('Mumbai', 'Delhi');
The BETWEEN Operator: Match a Range
BETWEEN is the cleanest way to filter a numeric or date range. Both ends are inclusive.
SELECT FullName, Salary
FROM Employees
WHERE Salary BETWEEN 50000 AND 80000;
-- Includes 50000 and 80000 themselves
This is the same as Salary >= 50000 AND Salary <= 80000, just shorter. Works for numbers, dates, and strings.
The LIKE Operator: Pattern Matching
LIKE is how you search for text patterns. It uses special wildcard characters.
The Four Wildcards
| Wildcard | Matches | Example |
|---|---|---|
% |
Zero or more characters | 'A%' → Anita, Aman, Ashwin |
_ |
Exactly one character | '_anish' → manish, vanish |
[ ] |
Any single character from a set | '[ARK]%' → names starting with A, R, or K |
[^ ] |
Any single character NOT in the set | '[^ARK]%' → names NOT starting with A, R, or K |
Practical LIKE Examples
-- Names starting with A
SELECT * FROM Employees WHERE FullName LIKE 'A%';
-- Names ending with 'a'
SELECT * FROM Employees WHERE FullName LIKE '%a';
-- Names containing "ar"
SELECT * FROM Employees WHERE FullName LIKE '%ar%';
-- Emails from shop.com domain
SELECT * FROM Employees WHERE Email LIKE '%@shop.com';
-- Names starting with A, P, or M
SELECT * FROM Employees WHERE FullName LIKE '[APM]%';
-- Names NOT starting with A, P, or M
SELECT * FROM Employees WHERE FullName LIKE '[^APM]%';
-- Rows where email does NOT contain @
SELECT * FROM Employees WHERE Email NOT LIKE '%@%';
Combining Conditions with AND / OR
Use AND when every condition must be true, and OR when just one is enough. Use parentheses to make the logic unambiguous — they are free, and they save you from costly mistakes.
-- Engineers in Mumbai earning over 70k
SELECT *
FROM Employees
WHERE Department = 'Engineering'
AND City = 'Mumbai'
AND Salary > 70000;
-- People in Mumbai or Bangalore who are in Sales or Marketing
SELECT *
FROM Employees
WHERE (City = 'Mumbai' OR City = 'Bangalore')
AND (Department = 'Sales' OR Department = 'Marketing');
AND is evaluated before OR. Without parentheses, A OR B AND C becomes A OR (B AND C), which is usually not what you want. Always use parentheses when you mix AND and OR.
Sorting Results with ORDER BY
ORDER BY sorts the final rows. Default is ascending (ASC). Use DESC for descending. You can sort by multiple columns.
-- Sort by name (A → Z)
SELECT * FROM Employees ORDER BY FullName;
-- Sort by salary, highest first
SELECT * FROM Employees ORDER BY Salary DESC;
-- Primary sort by department, tie-break by salary DESC
SELECT * FROM Employees
ORDER BY Department ASC, Salary DESC;
The second column only matters when the first column has duplicates — that's when the tie-break kicks in.
Limiting Rows with TOP
TOP lets you ask for just the first N rows of the result, or the first N percent. Combine it with ORDER BY to get the highest, lowest, oldest, newest, or biggest.
-- First 3 rows (undefined order — quick sample)
SELECT TOP 3 * FROM Employees;
-- Top 3 highest-paid employees
SELECT TOP 3 FullName, Salary
FROM Employees
ORDER BY Salary DESC;
-- Top 25% of rows
SELECT TOP 25 PERCENT * FROM Employees;
-- Highest earner (single row)
SELECT TOP 1 FullName, Salary
FROM Employees
ORDER BY Salary DESC;
TOP without ORDER BY returns any rows the engine picks — not meaningful. Always pair TOP with ORDER BY when the "top" has to mean something specific.
Putting It All Together
A real-world query usually combines several of these clauses. The order matters — SQL Server always runs them in this sequence: FROM → WHERE → SELECT → ORDER BY → TOP.
SELECT TOP 5
FullName, Department, City, Salary
FROM Employees
WHERE Department IN ('Engineering', 'Marketing')
AND Salary >= 50000
AND FullName LIKE '[APM]%'
ORDER BY Salary DESC;
Read it like a sentence: "From the employees, pick those in Engineering or Marketing who earn at least 50k and whose name starts with A, P, or M. Sort them by salary, highest first, and give me only the top 5."
Complete Operator Summary
| Operator | Purpose | Example |
|---|---|---|
= | Equals | City = 'Mumbai' |
<> / != | Not equal | City <> 'Delhi' |
> >= < <= | Numeric / date comparisons | Salary >= 60000 |
IN (...) | Match any value in a list | City IN ('Mumbai','Pune') |
BETWEEN a AND b | Inclusive range | Salary BETWEEN 50000 AND 80000 |
LIKE 'A%' | Pattern with wildcards | FullName LIKE 'A%' |
NOT | Negate a condition | NOT IN, NOT LIKE, NOT BETWEEN |
AND / OR | Combine conditions | A AND (B OR C) |
IS NULL / IS NOT NULL | NULL check | Email IS NULL |
Common Mistakes
Mistake 1: Using = NULL or != NULL. Both always return nothing. Always use IS NULL and IS NOT NULL for NULL checks.
Mistake 2: Forgetting quotes around strings. Text values must be in single quotes ('Mumbai'), never double quotes.
Mistake 3: Mixing AND and OR without parentheses. Precedence surprises will cost you time. When in doubt, add brackets.
Mistake 4: Writing SELECT * in production. It hurts performance and breaks when columns are added. Always list the columns you need.
Mistake 5: Using TOP without ORDER BY. The rows you get are unpredictable. If "top" should mean something specific (highest, newest), always sort first.
The Interview Answer
"A SELECT statement picks columns from a table using the
FROM clause. The WHERE clause filters rows by any condition — comparisons, IN, BETWEEN, LIKE — and conditions can be combined with AND or OR. DISTINCT removes duplicate rows, ORDER BY sorts the results ascending or descending, and TOP limits the output to the first N rows or a percentage. For NULL checks I use IS NULL, not the equals sign."
Summary
The SELECT statement is the backbone of SQL. Start by picking the columns you want, filter rows with WHERE, combine conditions with AND and OR, sort with ORDER BY, and limit with TOP. Use IN for value lists, BETWEEN for ranges, and LIKE with the four wildcards (%, _, [ ], [^ ]) for pattern matching.
Three habits separate clean SQL from messy SQL — always list column names instead of *, always use parentheses when mixing AND/OR, and always use IS NULL (never = NULL). Get comfortable with all the operators in this article and you'll breeze through the first 15 minutes of any SQL Server interview.
| Concept | Key Takeaway |
|---|---|
| SELECT * | Gets all columns — fine for exploration, avoid in code |
| Fully qualified names | Database.Schema.Table avoids context bugs |
| DISTINCT | Removes whole-row duplicates from results |
| WHERE | Keeps only rows where the condition is true |
| IN | Clean shortcut for many ORs on the same column |
| BETWEEN | Inclusive range — includes both endpoints |
| LIKE wildcards | %, _, [ ], [^ ] |
| AND / OR | Combine conditions — use parentheses |
| ORDER BY | Sorts results — default ASC, use DESC for reverse |
| TOP | Limits rows — pair with ORDER BY for real meaning |