05. Querying Data -- SELECT & WHERE Clause

Level: Beginner

ℹ️ What You'll Learn

• Basic SELECT syntax -- retrieve all data
• SELECT specific columns -- not all columns
• WHERE clause -- filter results
• Comparison operators (=, !=, >, <, >=, <=)
• Multiple conditions (AND, OR, NOT)
• LIKE operator -- pattern matching
• IN operator -- matching multiple values

INSERT adds data. SELECT retrieves it. This article teaches SELECT -- the R in CRUD (Create, Read, Update, Delete).

Why SELECT Matters for Backend Developers

Every API that returns data uses SELECT:

Frontend (React/Angular) requests: "Give me all students in 10th class"
    v
C# Backend API receives request
    v
Backend calls: SELECT * FROM Student WHERE ClassName = '10-A'
    v
SQL Server retrieves matching students
    v
Backend returns data to frontend (JSON)

Without SELECT, your API cannot retrieve data for users.

Basic SELECT Syntax

SELECT ColumnName1, ColumnName2, ColumnName3
FROM TableName
WHERE Condition;

Parts:

• SELECT -- Choose which columns to retrieve
• FROM -- Which table to read from
• WHERE -- Filter results (optional)

Example 1: SELECT All Columns

Retrieve all data from Student table:

SELECT * FROM Student;

Result (if table has data from article 04):

StudentId | Name        | RollNumber | ClassName | Section | DateOfBirth | Status
1         | Ravi Kumar  | 101        | 10-A      | A       | NULL        | Active
2         | Priya Sharma| 102        | 10-A      | A       | NULL        | Active
3         | Arjun Reddy | 103        | 10-B      | B       | NULL        | Active

Symbol * means: All columns.

Example 2: SELECT Specific Columns

Only retrieve Name and RollNumber:

SELECT Name, RollNumber FROM Student;

Result:

Name         | RollNumber
Ravi Kumar   | 101
Priya Sharma | 102
Arjun Reddy  | 103

Why useful: API returns only needed data. Faster. Less bandwidth.

Example 3: WHERE Clause -- Equal To

Get only students in class 10-A:

SELECT * FROM Student WHERE ClassName = '10-A';

Result:

StudentId | Name         | RollNumber | ClassName | Section | Status
1         | Ravi Kumar   | 101        | 10-A      | A       | Active
2         | Priya Sharma | 102        | 10-A      | A       | Active

Symbol = means: Exact match.

Example 4: WHERE Clause -- Not Equal

Get all students NOT in 10-A:

SELECT * FROM Student WHERE ClassName != '10-A';

Or use <> (both mean same):

SELECT * FROM Student WHERE ClassName <> '10-A';

Result:

StudentId | Name       | RollNumber | ClassName | Section | Status
3         | Arjun Reddy| 103        | 10-B      | B       | Active

Example 5: WHERE Clause -- Greater Than

Get students with roll number > 102:

SELECT * FROM Student WHERE RollNumber > 102;

Result:

StudentId | Name        | RollNumber | ClassName | Section | Status
3         | Arjun Reddy | 103        | 10-B      | B       | Active

Operators:

• > Greater than
• < Less than
• >= Greater than or equal
• <= Less than or equal
• = Equal
• != Not equal (same as <>)

Example 6: WHERE AND -- Multiple Conditions

Get students in 10-A AND with roll > 101:

SELECT * FROM Student 
WHERE ClassName = '10-A' AND RollNumber > 101;

Result:

StudentId | Name         | RollNumber | ClassName | Section | Status
2         | Priya Sharma | 102        | 10-A      | A       | Active

AND means: ALL conditions must be true.

Example 7: WHERE OR -- Any Condition True

Get students in 10-A OR 10-B:

SELECT * FROM Student 
WHERE ClassName = '10-A' OR ClassName = '10-B';

Result:

StudentId | Name         | RollNumber | ClassName | Section | Status
1         | Ravi Kumar   | 101        | 10-A      | A       | Active
2         | Priya Sharma | 102        | 10-A      | A       | Active
3         | Arjun Reddy  | 103        | 10-B      | B       | Active

OR means: ANY condition true is enough.

Example 8: WHERE IN -- Multiple Values

Get students in 10-A or 10-C (shorthand):

SELECT * FROM Student 
WHERE ClassName IN ('10-A', '10-C');

Same as:

SELECT * FROM Student 
WHERE ClassName = '10-A' OR ClassName = '10-C';

IN is cleaner for multiple OR conditions.

Example 9: WHERE LIKE -- Pattern Matching

Get all students whose name starts with 'R':

SELECT * FROM Student 
WHERE Name LIKE 'R%';

Result:

StudentId | Name       | RollNumber | ClassName | Section | Status
1         | Ravi Kumar | 101        | 10-A      | A       | Active

Pattern symbols:

• % Any characters (wildcard)
• _ Single character
• 'R%' Starts with R
• '%Kumar' Ends with Kumar
• '%av%' Contains av

Querying Teacher Table

Teacher table from article 03:

SELECT * FROM Teacher;

Result:

TeacherId | Name        | EmployeeCode | Qualification | ExperienceYears | Salary  | IsActive
1         | Dr. Mehta   | EMP001       | M.Sc          | 8               | 50000.0 | 1
2         | Mrs. Rao    | EMP002       | B.Ed          | 12              | 55000.0 | 1
3         | Mr. Sharma  | EMP003       | M.Tech        | 5               | 45000.0 | 1

Get only active teachers:

SELECT Name, EmployeeCode, Salary FROM Teacher WHERE IsActive = 1;

Result:

Name        | EmployeeCode | Salary
Dr. Mehta   | EMP001       | 50000.0
Mrs. Rao    | EMP002       | 55000.0
Mr. Sharma  | EMP003       | 45000.0

Get teachers with salary > 50000:

SELECT Name, Salary FROM Teacher WHERE Salary > 50000;

Result:

Name     | Salary
Mrs. Rao | 55000.0

---

Common SELECT Mistakes

Mistake 1: Using WHERE without FROM

Wrong:

SELECT * WHERE Name = 'Ravi';
-- Error: Incorrect syntax near 'WHERE'

Missing which table to query from.

Result: Syntax error. Query fails.

Fix: Always include FROM:

SELECT * FROM Student WHERE Name = 'Ravi';

Mistake 2: Forgetting quotes around text values

Wrong:

SELECT * FROM Student WHERE Name = Ravi;
-- Error: Invalid column name 'Ravi'

SQL Server thinks Ravi is a column, not text.

Result: "Invalid column name" error.

Fix: Use quotes for text:

SELECT * FROM Student WHERE Name = 'Ravi Kumar';

Mistake 3: Using = instead of LIKE for partial match

Wrong:

SELECT * FROM Student WHERE Name = 'Ravi';
-- Result: No rows found (exact match only)

= requires exact match. Student name is 'Ravi Kumar', not 'Ravi'.

Result: Query runs but no results (silent failure).

Fix: Use LIKE for patterns:

SELECT * FROM Student WHERE Name LIKE 'Ravi%';
-- Result: 'Ravi Kumar' matches

Mistake 4: Mixing AND/OR without parentheses (wrong logic)

Wrong:

SELECT * FROM Student 
WHERE ClassName = '10-A' OR ClassName = '10-B' AND RollNumber > 102;

Confusing. AND has higher priority, so this means: (10-A) OR (10-B AND RollNumber > 102).

Result: Unexpected results.

Fix: Use parentheses to clarify:

SELECT * FROM Student 
WHERE (ClassName = '10-A' OR ClassName = '10-B') AND RollNumber > 102;

Now clearly: (10-A OR 10-B) AND RollNumber > 102.

---

Best Practices for SELECT Queries

1. Select only columns you need -- SELECT Name, RollNumber not SELECT *
2. Always use WHERE to filter -- Don't retrieve all rows then filter in code
3. Use meaningful column names -- Helps readability
4. Quote text values -- 'Ravi Kumar' not Ravi Kumar
5. Use parentheses with AND/OR -- Clarifies logic
6. Use LIKE for partial matches -- Not = for patterns
7. Test queries first -- In SSMS before using in backend code

---

🎯 Q1: What's the difference between SELECT * and SELECT specific columns?

SELECT * (all columns):

SELECT * FROM Student;

Returns all columns. Useful for quick testing. Not good for production APIs.

SELECT specific columns:

SELECT Name, RollNumber, ClassName FROM Student;

Returns only needed columns. Faster. Less bandwidth. Better for APIs.

For backend: Always use specific columns. Faster API, less data transfer.

🎯 Q2: When should I use WHERE vs filtering in C# code?

WRONG -- Filter in C# code:

// Get ALL students
var allStudents = ExecuteQuery("SELECT * FROM Student");
// Filter in code
var result = allStudents.Where(s => s.ClassName == "10-A").ToList();

Retrieves all 10,000 students, then filters to 100. Slow, wastes memory.

RIGHT -- Filter in SQL:

// Get ONLY 10-A students
var students = ExecuteQuery("SELECT * FROM Student WHERE ClassName = '10-A'");

Retrieves only 100 students. Fast, efficient.

Rule: Filter in SQL (WHERE), not in code. Let database do the work.

🎯 Q3: What's the difference between = and LIKE?

= (equals): Exact match only

SELECT * FROM Student WHERE Name = 'Ravi Kumar';
-- Matches: 'Ravi Kumar'
-- No match: 'Ravi', 'ravi kumar' (different case), 'Ravi Kumar Singh'

LIKE: Pattern matching

SELECT * FROM Student WHERE Name LIKE 'Ravi%';
-- Matches: 'Ravi Kumar', 'Ravi Kumar Singh', 'Ravi', anything starting with 'Ravi'

Use:

• = when you know exact value
• LIKE for partial/pattern matching

🎯 Q4: How do AND and OR work in WHERE?

AND: All conditions must be TRUE

SELECT * FROM Student 
WHERE ClassName = '10-A' AND RollNumber > 101;
-- Both TRUE: included
-- One FALSE: excluded

OR: At least ONE condition TRUE

SELECT * FROM Student 
WHERE ClassName = '10-A' OR ClassName = '10-B';
-- Any TRUE: included
-- All FALSE: excluded

Mix both: Use parentheses

SELECT * FROM Student 
WHERE (ClassName = '10-A' OR ClassName = '10-B') AND Status = 'Active';
-- Class is 10-A or 10-B, AND Status is Active

🎯 Q5: What does IN do and when to use it?

IN: Match any value in list

SELECT * FROM Student 
WHERE ClassName IN ('10-A', '10-B', '10-C');

Same as:

SELECT * FROM Student 
WHERE ClassName = '10-A' OR ClassName = '10-B' OR ClassName = '10-C';

When to use IN:

• Multiple exact matches: Use IN (cleaner)
• Single value: Use =
• Pattern matching: Use LIKE
• Range: Use > and < operators

Example:

SELECT * FROM Teacher WHERE TeacherId IN (1, 3, 5);
-- Get teachers with IDs 1, 3, or 5

🎯 Q6: Why is WHERE important for backend developers?

Without WHERE (bad):

// GET /students returns ALL 50,000 students
// Slow API, huge response, wastes bandwidth
var allStudents = db.ExecuteQuery("SELECT * FROM Student");
return Ok(allStudents);

With WHERE (good):

// GET /students?class=10-A returns only class 10-A students
// Fast API, small response, efficient
var students = db.ExecuteQuery("SELECT * FROM Student WHERE ClassName = @class", parameters);
return Ok(students);

Backend rule: Avoid returning all data in production APIs. Use WHERE, TOP, pagination, or search filters so responses stay fast and useful.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on SELECT and WHERE clauses in SQL Server. Try these prompts:

• "Explain SELECT and WHERE step-by-step using Student table examples"
• "What's the difference between SELECT * and SELECT specific columns? Which is better for APIs?"
• "When should I use = vs LIKE vs IN in WHERE clause?"
• "Walk me through a complex WHERE with AND/OR and parentheses"
• "Quiz me: ask 5 questions about SELECT, WHERE, operators, and filtering"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

06. Sorting & Limiting Results -- ORDER BY & TOP ->