AND, OR operators, Exists and Not Exists, use of Alias

📖 Theory

More than one condition

Meera's questions get richer: 'groceries that are also cheap', or 'anything in Dairy or Beverage'. One condition is not enough; she needs to combine them.

That is AND and OR, and you already know their behaviour cold, they are the same AND/OR from BCA102 truth tables and Unit 2's Excel IF. AND is the strict one, OR the generous one.

This lesson combines conditions, peeks at EXISTS (checking if matching rows exist), and gives things readable nicknames with aliases. Old logic, new syntax.

📖 Theory

The strict list and the generous list

A wedding invite says 'must be family AND live in Surat', a strict filter, both must hold, so the guest list shrinks. Change it to 'family OR friends', a generous filter, either qualifies, so the list grows. AND narrows, OR widens. Exactly the truth-table behaviour from BCA102: AND is true only when all inputs are true; OR is true when any is.

⌨️ Practical

AND narrows, OR widens

-- AND: both must hold (narrows)
SELECT item FROM sales
WHERE category = 'Grocery' AND price < 50;
-- returns: Sugar

-- OR: either will do (widens)
SELECT item FROM sales
WHERE category = 'Dairy' OR category = 'Beverage';
-- returns: Tea, Milk

-- Alias: a readable nickname
SELECT item AS product, price AS cost FROM sales;

This example runs in Gri-Learn on the web, where you can edit it and see the output.

📖 Theory

Precedence, EXISTS, and aliases

• Precedence: AND binds tighter than OR, so A OR B AND C means A OR (B AND C). When in doubt, use parentheses to say exactly what you mean.
• EXISTS (subquery): true if the inner query returns any row; NOT EXISTS true if it returns none. Used to test presence: 'categories that have at least one item over 100'.
• Alias (AS): a temporary nickname for a column (price AS cost) or table (FROM sales AS s). It makes output readable and is essential when a query refers to a table twice. The AS keyword is optional (sales s works too).

🧩 Quiz

How many rows does WHERE category = 'Grocery' AND price > 100 return, given the sales table (groceries: Sugar 45, Rice 60)?

1. Zero, no grocery is priced over 100, and AND needs BOTH true
2. Two, both groceries match
3. One, Rice is close
4. Five, all rows

💭 Think first

Add parentheses

Meera writes WHERE category = 'Grocery' OR category = 'Dairy' AND price < 40. Because AND binds tighter, how does SQL actually read this, and is it what she probably meant?

⚠️ Watch out

Where marks leak

Forgetting AND binds tighter than OR, mixed conditions without parentheses read wrongly (the classic trap above). Confusing AND (narrows, all true) with OR (widens, any true). Thinking EXISTS returns data, it returns true/false (does any matching row exist?). And missing that an alias is just a nickname (AS optional) that does not change stored data. Parenthesise mixed logic, always.

📖 Theory

The same logic, a third time

Count the appearances: AND/OR truth tables (BCA102), Excel IF-AND-OR (Unit 2), and now SQL WHERE (Unit 4). When one idea recurs across maths, spreadsheets and databases, it is bedrock, own it once, use it forever. Next lessons make SELECT summarise: the aggregate functions (SUM, AVG, COUNT, MAX, MIN) that turn many rows into one answer, and the scalar functions that transform each value.

✅ Summary

Key takeaways

• AND requires BOTH conditions true (narrows the result); OR requires at least ONE (widens it).
• AND binds tighter than OR, use parentheses to control mixed conditions.
• EXISTS (subquery) is true if the subquery returns any row; NOT EXISTS if it returns none.
• An alias (AS) gives a column or table a readable nickname; AS is optional.
• This is the same AND/OR logic as BCA102 truth tables and Excel IF.
• Memory hook: AND is the strict guest list (shrinks), OR is the generous one (grows).