SQL WHERE
The WHERE clause is used to filter records.
The SQL WHERE clause is used in conjunction with the SELECT, UPDATE, DELETE, or other SQL statements to filter and retrieve specific rows from a database table that meet certain conditions or criteria. It allows you to specify a condition that must be satisfied for a row to be included in the result set.
The basic syntax of the WHERE clause is as follows:
|
1 2 3 |
SELECT column1, column2, ... FROM table_name WHERE condition; |
Here’s a breakdown of how the WHERE clause works:
SELECT: This part of the SQL statement specifies the columns you want to retrieve from the table.FROM: This part of the SQL statement specifies the table from which you want to retrieve data.WHERE: This is where you specify the condition or criteria that rows must meet to be included in the result set.condition: The condition is an expression that evaluates to either true or false for each row in the table. Rows that evaluate to true for the condition are included in the result set.
Common operators and keywords used in conditions include:
- Comparison operators (e.g., =, <>, >, <, >=, <=) for comparing values.
- Logical operators (e.g., AND, OR, NOT) for combining multiple conditions.
- LIKE for pattern matching.
- BETWEEN for specifying a range of values.
- IN for specifying a list of values.
- IS NULL for checking if a column contains NULL values.
- IS NOT NULL for checking if a column does not contain NULL values.
Here are some examples of using the WHERE clause in different SQL statements:
1. SELECT Statement:
|
1 2 3 4 |
SELECT * FROM employees WHERE department = 'HR'; |
This query retrieves all columns for employees who work in the HR department.
2. UPDATE Statement:
|
1 2 3 4 |
UPDATE products SET price = price * 1.1 WHERE category = 'Electronics'; |
This query updates the prices of products in the “Electronics” category by increasing them by 10%.
3. DELETE Statement:
|
1 2 3 |
DELETE FROM orders WHERE order_date < '2023-01-01'; |
This query deletes all orders placed before January 1, 2023.
4. Condition statements:
SELECT with Multiple Conditions:
|
1 2 3 4 |
SELECT product_name, price FROM products WHERE category = 'Electronics' AND price < 500; |
This query retrieves the names and prices of electronic products that cost less than $500.
Using the OR Operator:
|
1 2 3 4 |
SELECT first_name, last_name FROM employees WHERE department = 'Sales' OR department = 'Marketing'; |
This query retrieves the names of employees who work in either the Sales or Marketing departments.
Pattern Matching with LIKE:
|
1 2 3 4 |
SELECT product_name FROM products WHERE product_name LIKE 'Laptop%'; |
This query retrieves product names that start with “Laptop.”
Checking for NULL Values:
|
1 2 3 4 |
SELECT customer_name, email FROM customers WHERE email IS NULL; |
This query retrieves customer names and email addresses where the email address is not provided (NULL).
Using the NOT Operator:
|
1 2 3 4 |
SELECT order_id, order_date FROM orders WHERE NOT status = 'Shipped'; |
This query retrieves orders that are not yet shipped.
Filtering by Date Range:
|
1 2 3 4 |
SELECT event_name, event_date FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-12-31'; |
This query retrieves events that fall within the date range of the entire year 2023.
Using IN to Match Multiple Values:
|
1 2 3 4 |
SELECT product_name, price FROM products WHERE category IN ('Electronics', 'Clothing', 'Books'); |
This query retrieves products that belong to the specified categories.
These examples demonstrate how you can use the WHERE clause with various operators and conditions to filter and retrieve specific data from your database tables based on your requirements. You can combine multiple conditions and operators to create more complex queries to meet your specific needs.
