Logical Operators

• IN - Operand must be in a list of expressions

SELECT * | <value1>, <value2>
FROM <table_name>
WHERE <field1> IN ('abc', def', 'ghi');

For example, if we wanted to return all the types of apples and pears we stock:

SELECT *
FROM "sequel-mart-schema"."Products"
WHERE product_item IN ('apples', 'pears');

This is a short-hand equivalent of:

SELECT *
FROM "sequel-mart-schema"."Products"
WHERE product_item = 'apples' OR  product_item = 'pears';

• BETWEEN - Operand must fall between a range of inputs

SELECT * | <value1>, <value2>
FROM <table_name>
WHERE <field1> BETWEEN <input1> AND <input2>;

For example, let's see 'mid-popularity' products with scores between 50 and 55:

SELECT product_id, product_category, product_item, product_variety, popularity
FROM "sequel-mart-schema"."Products"
WHERE Popularity BETWEEN 50 AND 55;

This is a short-hand equivalent of:

SELECT product_id, product_category, product_item, product_variety, popularity
FROM "sequel-mart-schema"."Products"
WHERE Popularity >= 50 AND Popularity <= 55;

This works for text as well. For example,

SELECT product_id, product_category, product_item, product_variety, popularity
FROM "sequel-mart-schema"."Products"
WHERE product_item BETWEEN 'gl' AND 'les';

This returns 'gladioli' and 'leeks' as they are greater than 'gl' alphabetically. It doesn't include 'lettuce' as that is greater then 'les'

• LIKE - Operand must match a pattern of input

SELECT * | <value1>, <value2>
FROM <table_name>
WHERE <field1> LIKE 'abc%' | '%abc' | '%abc%';

For example, if we wanted to see all customers whose name starts with Jack we could:

SELECT *
FROM "sequel-mart-schema"."Customers"
WHERE customer_name LIKE ('Jack%');

Notice that this also returns someone called Jackson as it starts with Jack

Some of our products have varieties with a season at the end. If we wanted to see these we could query:

SELECT *
FROM "sequel-mart-schema"."Products"
WHERE product_variety LIKE ('%season');

• NOT - Operand must NOT match a pattern of input

This can be placed in front of any of the above NOT IN, NOT BETWEEN and NOT LIKE

When used, this will flip the logic around.

SELECT product_id, product_category, product_item, product_variety, popularity
FROM "sequel-mart-schema"."Products"
WHERE Popularity NOT BETWEEN 50 AND 55;

Would return any product that did not have a popularity between 50 and 55