SQL Where
How Where used in table?
The WHERE clause is used to filter the rows returned by a SELECT statement.
It allows you to specify a condition, and only the rows that satisfy condition will be included in the result set.
Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
- column1, column2, ...: The columns you want to retrieve. You can use * to select all columns.
- table_name: The name of the table from which you want to retrieve data.
- WHERE condition: The condition that must be satisfied for a row to be included in the result set.
Examples:
Assuming we have a table named students with columns student_id, first_name, last_name, and age.
Example 1: Simple WHERE Clause
SELECT * FROM students
WHERE age > 20;
This retrieves all columns for students where the age is greater than 20.
Example 2: Multiple Conditions
SELECT * FROM students
WHERE age > 20 AND first_name = 'John';
This retrieves all columns for students where the age is greater than 20 and the first name is 'John'.
Example 3: Using Comparison Operators
SELECT * FROM students
WHERE age BETWEEN 18 AND 25;
This retrieves all columns for students where the age is between 18 and 25 (inclusive).
Example 4: Text Matching
SELECT * FROM students
WHERE last_name LIKE 'S%';
This retrieves all columns for students where the last name starts with 'S'.
Example 5: NULL Values
SELECT * FROM students
WHERE last_name IS NULL;
This retrieves all columns for students where the last name is NULL.
Example 6: Combining Conditions with OR
SELECT * FROM students
WHERE age > 20 OR first_name = 'Jane';
This retrieves all columns for students where the age is greater than 20 or the first name is 'Jane'.
Example 7: Negating Conditions with NOT
SELECT * FROM students
WHERE NOT age > 20;
This retrieves all columns for students where the age is not greater than 20.
Tips:
- Use parentheses to ensure the correct order of evaluation when combining multiple conditions.
- Be cautious with NULL values; use IS NULL or IS NOT NULL for proper filtering.
The WHERE clause is a powerful tool for filtering and retrieving specific data from your database based on conditions. It allows you to tailor your queries to extract exactly the information you need.