Conditional Queries ​

Using SELECT * FROM <table_name> retrieves all records from a table. However, often we want to selectively obtain records based on specific conditions, such as querying students with scores above 80. Fetching all records can be time-consuming and resource-intensive, especially with millions of records.

The SELECT statement can use the WHERE clause to set conditions, returning records that meet those criteria. For example, to specify the condition "students with scores of 80 or above," we write:

-- Query students with scores of 80 or above:
SELECT * FROM students WHERE score >= 80;

Here, WHERE score >= 80 is the condition, filtering records based on the score column.

The syntax for conditional queries is:

SELECT * FROM <table_name> WHERE <condition_expression>

Condition expressions can use <condition1> AND <condition2>, which means both conditions must be met. For instance, to find students who have scores of 80 or above and are male:

-- Query students with scores of 80 or above and are male:
SELECT * FROM students WHERE score >= 80 AND gender = 'M';

Another condition type is <condition1> OR <condition2>, meaning either condition can be satisfied. For instance, to find students with scores of 80 or above or who are male:

-- Query students with scores of 80 or above or are male:
SELECT * FROM students WHERE score >= 80 OR gender = 'M';

Clearly, the OR condition is less strict than AND, returning more records.

A third condition is NOT <condition>, which indicates records that do not meet the specified condition. For example, to find students who are not in class 2:

-- Query students not in class 2:
SELECT * FROM students WHERE NOT class_id = 2;

The NOT class_id = 2 condition is equivalent to class_id <> 2, but NOT queries are less common.

To combine three or more conditions, use parentheses () to indicate the order of operations. For example, to find male students with scores below 80 or above 90:

-- Query male students with scores below 80 or above 90:
SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

Without parentheses, the order of operations follows the precedence of NOT, AND, and then OR, so using parentheses can alter that priority.

Common Condition Expressions ​

To query students with scores between 60 (inclusive) and 90 (inclusive), you can use:

• WHERE score >= 60 AND score <= 90
• WHERE score IN (60, 90)
• WHERE score BETWEEN 60 AND 90

Summary ​

Using the WHERE clause allows you to filter records that meet specific conditions, rather than retrieving all records from a table.