SQL Logic and Comparison

Filtering by criteria

In most cases, when we proceed with a query, we want to extract only those records that comply with specific criteria. To filter the selection, SQL provides a particular operator WHERE.

The syntax for this operator is as follows:

SELECT * 
FROM table 
WHERE conditions

In the conditions field, we can insert any parameters we want our extracted data to be consistent with. However, to define those specific selection conditions, we need to apply them to expressions.

If a client wants to buy a book by Charles Dickens, the query that selects books that meet the criteria will look as follows:

SELECT id, title, rating 
FROM books 
WHERE author = 'Charles Dickens';

At the end of the SELECT statement, we have added the WHERE keyword followed by the logical expression that specifies the filters: author = 'Charles Dickens'.

The SELECT clause shouldn't contain the columns or expressions listed in the WHERE clause. There is no need to output author in the query above, even though there is a corresponding condition in the WHERE statement.

Filtering with Comparisons

This is a full list of comparison operations that can be used in the WHERE operator.

To make a selection by string literals or dates, put them in quotation marks (" ").

Below are some examples of data extraction from the products table using comparisons.

Consider the scenario where the objective is to identify products within the dataset with a price exceeding 250 units. In this context, the > operator is employed to construct the following query:

SELECT * 
FROM products 
WHERE price > 250

Below is the table with the requested items:

To select items that cost more than or equal to 250, use >= instead. The same principle works for comparisons < and <=. 

To select all products from the table that are related to the vegetables category, the query will be:

SELECT * 
FROM products 
WHERE category = 'vegetables'

Quotation marks are essential when making a selection by a string literal. Without them, the query won't work.

The result of our selection:

Logical expressions

The three operators from Boolean algebra:

• NOT returns True if the argument equals to False and vice versa.
• AND compares operands and returns True only if all of them are True. Alternatively, returns False.
• OR returns True if at least one of the operands is True. Otherwise, returns False.

The order of operators given above, as they are sorted according to their priority (meaning that having all three operands in the expression, first is proceeded with NOT, then AND, and finally OR).

The picture below shows the logic of the operands mentioned above:

Below is a table named staff containing info on programmers working in our company.

To pick a person who meets the requirements of a Middle or a Senior and knows SQL, the query will be:

SELECT * 
FROM staff 
WHERE (status="Middle" OR status="Senior") AND skills="SQL"

The parentheses are being used with the OR operand, as it's an important tool for prioritizing the OR condition over AND. Without using them, SQL would first process the AND operand, and only then OR, making the query request irrelevant.

The same criteria can be arranged via selection by using the NOT operand instead of OR:

SELECT * 
FROM staff 
WHERE NOT(status="Junior") AND skills="SQL"

The result will look as follows: