You are currently viewing SQL :1.5 SQL WHERE Clause

SQL :1.5 SQL WHERE Clause

SQL WHERE Clause

In SQL, the WHERE clause is used to filter records based on specified conditions. It allows you to retrieve data from a table based on a condition or set of conditions. This tutorial will cover the basics of the WHERE clause in SQL, with sample codes and scenarios for better understanding.

1. Syntax of the WHERE Clause

The basic syntax of the WHERE clause is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition;
  • SELECT: Specifies the columns you want to retrieve.
  • FROM: Specifies the table from which you are retrieving data.
  • WHERE: Specifies the condition(s) that must be met for the records to be included in the result.

2. Sample Database

For our examples, let’s assume we have a simple users table with the following structure:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

3. Basic WHERE Clause Example

Let’s say we want to retrieve users who are 25 years old:

SELECT * 
FROM users
WHERE age = 25;

Explanation:

  • SELECT *: Selects all columns from the users table.
  • FROM users: Specifies the users table.
  • WHERE age = 25: Filters the result to include only rows where the age column is equal to 25.

4. Using Operators in WHERE Clause

You can use various operators in the WHERE clause. Here are some examples:

Greater Than (>)

Retrieve users older than 30:

SELECT * 
FROM users
WHERE age > 30;
Less Than (<)

Retrieve users younger than 30:

SELECT * 
FROM users
WHERE age < 30;
Not Equal To (<> or !=)

Retrieve users who are not 25 years old:

SELECT * 
FROM users
WHERE age <> 25;
-- or
SELECT * 
FROM users
WHERE age != 25;

5. Combining Conditions with AND, OR

You can use AND and OR to combine multiple conditions:

AND

Retrieve users who are 25 years old and live in ‘New York’:

SELECT * 
FROM users
WHERE age = 25 AND city = 'New York';
OR

Retrieve users who are 25 years old or live in ‘New York’:

SELECT * 
FROM users
WHERE age = 25 OR city = 'New York';

6. Using LIKE for Pattern Matching

The LIKE operator is used for pattern matching with wildcard characters.

Starting with ‘J’

Retrieve users whose name starts with ‘J’:

SELECT * 
FROM users
WHERE name LIKE 'J%';
Ending with ‘son’

Retrieve users whose name ends with ‘son’:

SELECT * 
FROM users
WHERE name LIKE '%son';
Containing ‘an’

Retrieve users whose name contains ‘an’:

SELECT * 
FROM users
WHERE name LIKE '%an%';

7. Using IN and NOT IN

The IN operator allows you to specify multiple values in a WHERE clause.

Retrieve Specific Cities

Retrieve users from ‘New York’ or ‘Los Angeles’:

SELECT * 
FROM users
WHERE city IN ('New York', 'Los Angeles');
Exclude Specific Cities

Retrieve users not from ‘New York’ or ‘Los Angeles’:

SELECT * 
FROM users
WHERE city NOT IN ('New York', 'Los Angeles');

8. Using BETWEEN for Range

The BETWEEN operator selects values within a given range.

Age Between 20 and 30

Retrieve users whose age is between 20 and 30:

SELECT * 
FROM users
WHERE age BETWEEN 20 AND 30;

Conclusion

The WHERE clause is a powerful tool in SQL for filtering records based on specific conditions. By using various operators and functions, you can tailor your queries to retrieve exactly the data you need from your database.

Remember, this tutorial covers the basics. SQL offers many more features and functions for more complex queries, but mastering the WHERE clause is a fundamental step in SQL programming.

Leave a Reply