DML: SELECT with a WHERE clause

Basic Introduction

The SELECT statement is one of the fundamental elements of SQL (Structured Query Language). It allows you to retrieve data from a database. However, if you want to retrieve specific records from one or more tables in a database, the WHERE clause is what you use. The WHERE clause describes the condition for a record to be selected.

Here’s the basic syntax:

SELECT column1, column2, ..., columnN
FROM table_name
WHERE condition;

You can replace column1, column2, ..., columnN with the specific columns you want to select from the table. If you want to select all columns, use *.

The condition is a criterion that must be met for a row in the table to be included in the result set. It could involve logical operators such as =, <>, <, >, <=, >=, etc.

Examples

Suppose we have the following employee table:

Now, we want to select employees who work in the IT department. We can write:

SELECT *
FROM employee
WHERE department = 'IT';

The result will be:

We can also combine conditions using logical operators like AND, OR, NOT. For instance, to select employees in the IT department who are older than 28:

SELECT *
FROM employee
WHERE department = 'IT' AND age > 28;

The result will be:

More Examples

Multiple Conditions

Suppose you want to get the details of employees from the ‘IT’ department and whose age is less than or equal to 28. You can use the AND keyword to combine these conditions.

SELECT *
FROM employee
WHERE department = 'IT' AND age <= 28;

The result will be:

Select with OR

If you want to select employees who are either from the ‘IT’ department or from the ‘Sales’ department, you can use the OR keyword:

SELECT *
FROM employee
WHERE department = 'IT' OR department = 'Sales';

The result will be:

Using NOT

The NOT keyword can be used to select records where the condition is NOT TRUE. For instance, if you want to select employees who are not in the ‘HR’ department:

SELECT *
FROM employee
WHERE NOT department = 'HR';

The result will be:

Using LIKE

The WHERE clause uses the LIKE operator to search for a specific pattern. If we want to select all employees whose first name begins with ‘J’:

SELECT *
FROM employee
WHERE first_name LIKE 'J%';

The result will be:

Using BETWEEN

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. For instance, to select employees who are between 30 and 40 years old:

SELECT *
FROM employee
WHERE age BETWEEN 30 AND 40;

The result will be:

Further Readings

1. SQL SELECT Statement - W3Schools
2. SQL WHERE Clause - W3Schools
3. SQL: SELECT Statement
4. SQL WHERE Clause - SQLTutorial