Text : The SQL Where Clause

Setting conditions or constraints in SQL commands.

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

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

Ex:

SELECT * FROM tblbooks WHERE book_year < 2000;

SELECT * FROM tblbooks WHERE book_publisher = 1;

SELECT a.book_title, b.auth_name
FROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_id
INNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_id
WHERE b.auth_name = 'Pitok Batolata';

The SQL AND Operator
The WHERE clause can contain one or many AND operators. The AND operator is used to filter records based on more than one condition.

SELECT a.book_title, b.auth_name
FROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_id
INNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_id
WHERE b.auth_name = 'Pitok Batolata' AND a.book_year;

The SQL OR Operator
The WHERE clause can contain one or more OR operators. The OR operator is used to filter records based on more than one condition.

SELECT a.book_title, b.auth_name, a.book_year
FROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_id
INNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_id
WHERE b.auth_name = 'Pitok Batolata' OR b.auth_name = 'Kulas D. Malas';

The NOT Operator
The NOT operator is used in combination with other operators to give the opposite result, also called the negative result.

SELECT a.book_title, b.auth_name, a.book_year
FROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_id
INNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_id
WHERE NOT b.auth_name = 'Pitok Batolata';

The SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

 - The percent sign % represents zero, one, or multiple characters
 - The underscore sign _ represents one, single character

SELECT a.book_title, b.auth_name, a.book_year
FROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_id
INNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_id
WHERE a.book_title LIKE 'THE%';

SELECT a.book_title, b.auth_name, a.book_year
FROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_id
INNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_id
WHERE a.book_title LIKE '%song%';

The SQL IN Operator
The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.

The SQL BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.

SELECT a.book_title, b.auth_name, a.book_year
FROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_id
INNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_id
WHERE a.book_year IN (2023,2001)
ORDER BY a.book_title;

SELECT a.book_title, b.auth_name, a.book_year
FROM tblbooks a INNER JOIN tblbooks_authors c ON a.book_id = c.bookAuth_book_id
INNER JOIN tblbooks_authors_master b ON b.auth_id = c.bookAuth_author_id
WHERE a.book_year BETWEEN 1990 AND 2010;