71:{"json":{"id":"seed_036eea0d52fe1f0f44ba92112191363b","slug":"logical-operators","category":"Database Management Systems","topic":"SQL Queries","title":"Logical Operators","description":"Master SQL logical operators AND, OR, and NOT to build sophisticated query conditions. Learn operator precedence, truth table semantics, and how to construct complex filtering logic that precisely expresses business requirements while maintaining query clarity and performance.","difficulty":"BEGINNER","estimatedMinutes":55,"pages":[{"id":"seed_b54a8439f143df81980591a8bc8b5e4b","title":"AND Operator","order":0,"content":[{"id":"opening-hook","title":"The Power of Combining Conditions","content":[{"data":{"content":"When querying databases, single conditions are rarely sufficient. Real-world data retrieval almost always requires **multiple conditions that must all be true simultaneously**. A human resources system doesn't just find employees—it finds *active employees in the engineering department hired after 2020 with a salary above $80,000*. An e-commerce platform doesn't just list products—it finds *in-stock products in the electronics category priced between $100 and $500 with ratings above 4 stars*.\n\nThe **AND operator** is the fundamental building block for expressing this kind of conjunctive logic in SQL. It allows you to specify that every condition in your query must evaluate to TRUE for a row to be included in the result set.\n\nUnderstanding AND thoroughly—including its truth table semantics, behavior with NULL values, and performance characteristics—is essential for writing precise, efficient queries."},"type":"text"},{"data":{"icon":"Target","title":"What You Will Learn","content":"By the end of this page, you will master the AND operator completely: its formal semantics derived from Boolean algebra, its behavior in SQL's three-valued logic with NULLs, its practical applications for multi-condition filtering, and optimization strategies for AND-heavy queries.","variant":"info"},"type":"callout"}]},{"id":"and-fundamentals","title":"The AND Operator Fundamentals","content":[{"data":{"content":"The AND operator performs **logical conjunction**—a fundamental operation from Boolean algebra. In SQL, AND combines two or more conditions and returns TRUE only when **all conditions are TRUE**.\n\n**Formal Definition:**\n\n```\ncondition1 AND condition2 → TRUE if and only if:\n - condition1 evaluates to TRUE, AND\n - condition2 evaluates to TRUE\n```\n\nThis definition extends naturally to any number of conditions:\n\n```\ncondition1 AND condition2 AND condition3 AND ... AND conditionN → TRUE\nif and only if ALL conditions evaluate to TRUE\n```"},"type":"text"},{"data":{"code":"-- Basic AND Syntax\nSELECT column1, column2, ...\nFROM table_name\nWHERE condition1 AND condition2;\n\n-- Example: Find employees in Engineering department earning over $80,000\nSELECT employee_id, first_name, last_name, department, salary\nFROM employees\nWHERE department = 'Engineering'\n AND salary > 80000;\n\n-- Multiple AND conditions\nSELECT product_id, name, price, stock_quantity\nFROM products\nWHERE category = 'Electronics'\n AND price >= 100\n AND price <= 500\n AND stock_quantity > 0\n AND rating >= 4.0;","fileName":"basic_and_syntax.sql","language":"sql","showLineNumbers":true},"type":"code"},{"data":{"icon":"Lightbulb","title":"Formatting Convention","content":"When chaining multiple AND conditions, place each condition on a new line with AND at the beginning. This improves readability and makes it easy to add, remove, or comment out individual conditions during development and debugging.","variant":"tip"},"type":"callout"}],"titleType":"number","titleNumber":1},{"id":"truth-table","title":"Truth Table Analysis","content":[{"data":{"content":"Understanding AND requires internalizing its truth table—the complete specification of how AND evaluates based on its operands.\n\n**Binary Truth Table (Classical Logic):**\n\nIn classical Boolean algebra with only TRUE and FALSE values:"},"type":"text"},{"data":{"rows":[["TRUE","TRUE","**TRUE**"],["TRUE","FALSE","FALSE"],["FALSE","TRUE","FALSE"],["FALSE","FALSE","FALSE"]],"caption":"AND Truth Table (Two-Valued Logic)","headers":["Condition A","Condition B","A AND B"]},"type":"table"},{"data":{"content":"**Key Insight:** AND is the **most restrictive** logical operator. Only one combination—both operands TRUE—produces a TRUE result. Every other combination yields FALSE.\n\nThis property has profound implications:\n\n1. **Each additional AND condition can only reduce (or maintain) the result set size**\n2. **The more AND conditions you add, the fewer rows typically match**\n3. **AND acts as a filter cascade**, progressively narrowing results"},"type":"text"},{"data":{"icon":"Info","title":"Why Understanding Truth Tables Matters","content":"Truth tables aren't just theoretical—they help you predict query behavior. If you have 1,000 rows, a single condition might match 200 rows. Adding an AND condition can only further reduce this (matching perhaps 50 rows). Understanding this helps you structure efficient queries and anticipate result sizes.","variant":"note"},"type":"callout"}],"titleType":"number","titleNumber":2},{"id":"three-valued-logic","title":"AND with Three-Valued Logic (NULL)","content":[{"data":{"content":"SQL extends classical two-valued Boolean logic with a third value: **UNKNOWN**, which arises when expressions involve NULL. This creates **three-valued logic (3VL)**, which profoundly affects how AND behaves.\n\n**Why NULL Creates UNKNOWN:**\n\nNULL represents an unknown, missing, or inapplicable value. When you compare something to NULL, the result isn't TRUE or FALSE—it's UNKNOWN because we cannot determine the truth without knowing the actual value.\n\n```sql\n-- This expression evaluates to UNKNOWN, not TRUE or FALSE\nNULL = 5 → UNKNOWN\nNULL > 10 → UNKNOWN\nNULL = NULL → UNKNOWN\n```"},"type":"text"},{"data":{"rows":[["TRUE","TRUE","**TRUE**","Both known to be true"],["TRUE","UNKNOWN","UNKNOWN","Can't determine—depends on unknown value"],["TRUE","FALSE","FALSE","One false is sufficient"],["UNKNOWN","TRUE","UNKNOWN","Can't determine—depends on unknown value"],["UNKNOWN","UNKNOWN","UNKNOWN","Both values unknown"],["UNKNOWN","FALSE","**FALSE**","One false is sufficient"],["FALSE","TRUE","FALSE","One false is sufficient"],["FALSE","UNKNOWN","**FALSE**","One false is sufficient"],["FALSE","FALSE","FALSE","Both false"]],"caption":"AND Truth Table with Three-Valued Logic","headers":["Condition A","Condition B","A AND B","Explanation"]},"type":"table"},{"data":{"content":"**Critical Rule:** `FALSE AND UNKNOWN = FALSE`\n\nThis makes logical sense: if we know one condition is FALSE, the conjunction must be FALSE regardless of the other value. AND requires *all* conditions to be TRUE, so a single FALSE is decisive.\n\n**But:** `TRUE AND UNKNOWN = UNKNOWN`\n\nThis is trickier: we know one condition is TRUE, but the other is unknown. The conjunction *might* be TRUE (if the unknown value is TRUE) or FALSE (if it's FALSE). We simply can't tell."},"type":"text"},{"data":{"code":"-- Table: employees\n-- | emp_id | name | department | commission |\n-- |--------|---------|------------|------------|\n-- | 1 | Alice | Sales | 5000 |\n-- | 2 | Bob | Sales | NULL | -- Commission unknown\n-- | 3 | Charlie | Engineering| NULL | -- No commission (N/A)\n-- | 4 | Diana | Sales | 3000 |\n\n-- Query: Find Sales employees with commission > 2000\nSELECT emp_id, name, commission\nFROM employees\nWHERE department = 'Sales'\n AND commission > 2000;\n\n-- Analysis:\n-- Alice: 'Sales' = 'Sales' → TRUE AND 5000 > 2000 → TRUE = TRUE ✓\n-- Bob: 'Sales' = 'Sales' → TRUE AND NULL > 2000 → UNKNOWN = UNKNOWN (excluded)\n-- Charlie: 'Engineering' = 'Sales' → FALSE AND ... = FALSE (excluded)\n-- Diana: 'Sales' = 'Sales' → TRUE AND 3000 > 2000 → TRUE = TRUE ✓\n\n-- Result: Only Alice and Diana returned\n-- Bob is EXCLUDED because TRUE AND UNKNOWN = UNKNOWN, not TRUE","fileName":"and_with_null.sql","language":"sql","showLineNumbers":true},"type":"code"},{"data":{"icon":"AlertCircle","title":"NULL Exclusion in AND Queries","content":"Rows with NULL values in columns used in AND conditions are typically excluded from results unless explicitly handled. This is a common source of bugs—developers expect rows with NULL to appear when they match other conditions, but UNKNOWN results in exclusion. Always consider: 'What if this column is NULL?'","variant":"warning"},"type":"callout"}],"titleType":"number","titleNumber":3},{"id":"short-circuit","title":"Short-Circuit Evaluation","content":[{"data":{"content":"Many programming languages implement **short-circuit evaluation** for AND: if the first operand is FALSE, the second operand is never evaluated because the result is already determined.\n\n**Does SQL short-circuit AND?**\n\nThe SQL standard does not mandate short-circuit evaluation, and behavior varies across database systems:\n\n- **PostgreSQL, MySQL, SQL Server:** May short-circuit, but the optimizer may also reorder conditions for efficiency\n- **Oracle:** Historically evaluated all conditions; modern versions may optimize\n\n**Why This Matters:**\n\nIf you write a query assuming left-to-right short-circuit evaluation, you might be surprised when a database evaluates conditions in a different order—potentially causing errors or unexpected behavior."},"type":"text"},{"data":{"code":"-- Dangerous assumption: relying on short-circuit evaluation\n-- Suppose 'value' column sometimes contains non-numeric strings\n\n-- WRONG: Assumes left-to-right short-circuit\nSELECT *\nFROM mixed_data\nWHERE is_numeric = TRUE \n AND CAST(value AS INTEGER) > 100;\n\n-- If the database evaluates CAST() first (before checking is_numeric),\n-- this query will ERROR on non-numeric values!\n\n-- SAFER: Use CASE to guarantee evaluation order\nSELECT *\nFROM mixed_data\nWHERE CASE \n WHEN is_numeric = TRUE THEN CAST(value AS INTEGER) > 100\n ELSE FALSE\n END;\n\n-- BEST: Separate with a CTE or subquery\nWITH numeric_only AS (\n SELECT * FROM mixed_data WHERE is_numeric = TRUE\n)\nSELECT * FROM numeric_only\nWHERE CAST(value AS INTEGER) > 100;","fileName":"short_circuit_pitfall.sql","language":"sql","showLineNumbers":true},"type":"code"},{"data":{"icon":"AlertCircle","title":"Never Rely on Condition Order","content":"Database optimizers may reorder AND conditions for performance. A condition you placed 'first' for safety might be evaluated last. If one condition guards against errors in another, use CASE expressions or CTEs to guarantee evaluation order.","variant":"error"},"type":"callout"}],"titleType":"number","titleNumber":4},{"id":"practical-applications","title":"Practical Applications","content":[{"data":{"content":"AND enables sophisticated data filtering that mirrors real-world business requirements. Let's examine common patterns:"},"type":"text"},{"data":{"tabs":[{"id":"range-filtering","label":"Range Filtering","content":[{"data":{"content":"**Range queries** require both lower and upper bounds, naturally using AND:"},"type":"text"},{"data":{"code":"-- Price range\nSELECT product_name, price\nFROM products\nWHERE price >= 50.00 AND price <= 200.00;\n\n-- Equivalent using BETWEEN (which is AND under the hood)\nSELECT product_name, price\nFROM products\nWHERE price BETWEEN 50.00 AND 200.00;\n\n-- Date range with explicit AND\nSELECT order_id, order_date, total\nFROM orders\nWHERE order_date >= '2024-01-01'\n AND order_date < '2024-04-01';\n\n-- Age range for eligibility\nSELECT applicant_id, name, age\nFROM applicants\nWHERE age >= 21 AND age <= 65;","fileName":"range_filtering.sql","language":"sql","showLineNumbers":true},"type":"code"}],"titleIcon":"Target"},{"id":"multi-criteria","label":"Multi-Criteria Search","content":[{"data":{"content":"**Complex search forms** translate directly to AND-chained conditions:"},"type":"text"},{"data":{"code":"-- Job search with multiple filters\nSELECT job_id, title, company, location, salary, remote_option\nFROM job_postings\nWHERE category = 'Software Engineering'\n AND experience_level = 'Senior'\n AND salary >= 150000\n AND remote_option = TRUE\n AND posted_date >= CURRENT_DATE - INTERVAL '30 days';\n\n-- Real estate search\nSELECT property_id, address, bedrooms, price, square_feet\nFROM properties\nWHERE property_type = 'House'\n AND bedrooms >= 3\n AND price <= 500000\n AND square_feet >= 1500\n AND year_built >= 2000\n AND city = 'Austin';","fileName":"multi_criteria.sql","language":"sql","showLineNumbers":true},"type":"code"}],"titleIcon":"ListIcon"},{"id":"data-validation","label":"Data Validation","content":[{"data":{"content":"**Finding valid records** that meet all quality criteria:"},"type":"text"},{"data":{"code":"-- Find complete customer profiles\nSELECT customer_id, name, email, phone\nFROM customers\nWHERE email IS NOT NULL\n AND phone IS NOT NULL\n AND email LIKE '%@%.%'\n AND LENGTH(phone) >= 10\n AND verified = TRUE;\n\n-- Identify valid transactions\nSELECT transaction_id, amount, status\nFROM transactions\nWHERE amount > 0\n AND status = 'completed'\n AND error_code IS NULL\n AND processed_at IS NOT NULL;\n\n-- Find records with all required fields\nSELECT order_id\nFROM orders\nWHERE customer_id IS NOT NULL\n AND shipping_address IS NOT NULL\n AND payment_confirmed = TRUE\n AND items_count > 0;","fileName":"data_validation.sql","language":"sql","showLineNumbers":true},"type":"code"}],"titleIcon":"CheckCircle"},{"id":"business-rules","label":"Business Rules","content":[{"data":{"content":"**Enforcing business logic** through multi-condition queries:"},"type":"text"},{"data":{"code":"-- Find eligible loan applicants\nSELECT applicant_id, name, credit_score, annual_income\nFROM loan_applications\nWHERE credit_score >= 680\n AND annual_income >= 50000\n AND employment_status = 'Employed'\n AND bankruptcy_history = FALSE\n AND debt_to_income_ratio <= 0.43;\n\n-- Premium membership qualification\nSELECT customer_id, name, total_purchases, member_since\nFROM customers\nWHERE total_purchases >= 10000\n AND member_since <= CURRENT_DATE - INTERVAL '1 year'\n AND account_status = 'Active'\n AND returns_percentage <= 5.0;\n\n-- Inventory reorder triggers\nSELECT product_id, name, current_stock, reorder_point\nFROM inventory\nWHERE current_stock <= reorder_point\n AND supplier_status = 'Active'\n AND discontinued = FALSE;","fileName":"business_rules.sql","language":"sql","showLineNumbers":true},"type":"code"}],"titleIcon":"Zap"}],"defaultTab":"range-filtering"},"type":"tabs"}],"titleType":"number","titleNumber":5},{"id":"performance","title":"Performance Considerations","content":[{"data":{"content":"AND conditions directly impact query performance. Understanding how databases process AND helps you write efficient queries:"},"type":"text"},{"data":{"items":["**Index Utilization** — If columns in AND conditions have indexes, the database can use them to quickly narrow results. Composite indexes covering multiple AND columns are especially efficient.","**Selectivity Order** — Database optimizers often evaluate the most selective (restrictive) conditions first to minimize rows processed. A condition matching 1% of rows is evaluated before one matching 50%.","**Index Intersection** — Some databases can use multiple single-column indexes, intersecting their results. This is less efficient than a composite index but better than full table scans.","**Range Scan Termination** — For range conditions with AND (like BETWEEN), indexes enable efficient range scans that stop early once bounds are exceeded."],"title":{"icon":"Zap","text":"AND Performance Principles","type":"icon","color":"primary","variant":"outline-circle"},"variant":"accent"},"type":"list"},{"data":{"code":"-- Scenario: Frequent query pattern\nSELECT * FROM orders\nWHERE customer_id = 12345\n AND order_date >= '2024-01-01'\n AND status = 'Shipped';\n\n-- Optimal: Create a composite index matching AND conditions\nCREATE INDEX idx_orders_customer_date_status \nON orders(customer_id, order_date, status);\n\n-- This index enables efficient lookup:\n-- 1. Seek directly to customer_id = 12345\n-- 2. Within those, range scan order_date >= '2024-01-01'\n-- 3. Filter by status = 'Shipped'\n\n-- Index column order matters!\n-- Place equality conditions (=) before range conditions (>=, <=, BETWEEN)\n-- Because range conditions \"consume\" the remaining index","fileName":"index_optimization.sql","language":"sql","showLineNumbers":true},"type":"code"},{"data":{"icon":"Lightbulb","title":"Index Design for AND Queries","content":"When designing indexes for queries with multiple AND conditions, include columns in order of: (1) equality conditions first, (2) range conditions second, (3) most selective conditions earlier. This maximizes index efficiency by allowing the database to seek rather than scan.","variant":"tip"},"type":"callout"}],"titleType":"number","titleNumber":6},{"id":"common-mistakes","title":"Common Mistakes with AND","content":[{"data":{"content":"Even experienced developers make these AND-related errors:"},"type":"text"},{"data":{"left":[{"data":{"items":["**Forgetting NULL behavior** — Expecting rows with NULL to match when they evaluate to UNKNOWN","**Impossible conditions** — Writing `WHERE status = 'Active' AND status = 'Pending'` which can never be true","**Redundant conditions** — Adding conditions already implied by others, cluttering the query","**Mixing AND/OR without parentheses** — Getting unexpected results due to operator precedence"],"title":{"icon":"XCircle","text":"Common Errors","type":"icon","color":"destructive","variant":"outline-circle"},"variant":"glass"},"type":"list"}],"right":[{"data":{"items":["**Always consider NULL** — Use IS NOT NULL when NULL values should exclude rows","**Verify logical consistency** — Ensure conditions can actually all be true together","**Eliminate redundancy** — Review conditions for overlap or implication","**Use parentheses** — Make precedence explicit when mixing AND with OR"],"title":{"icon":"CheckCircle","text":"Best Practices","type":"icon","color":"primary","variant":"outline-circle"},"variant":"glass"},"type":"list"}]},"type":"two-column"},{"data":{"code":"-- MISTAKE 1: Impossible condition\nSELECT * FROM products\nWHERE category = 'Electronics' AND category = 'Clothing';\n-- This returns NOTHING! A product can't be in two categories simultaneously.\n\n-- MISTAKE 2: Forgetting NULL\nSELECT * FROM employees\nWHERE department = 'Sales' AND bonus > 0;\n-- Employees with NULL bonus are excluded! If you want them, use:\nSELECT * FROM employees\nWHERE department = 'Sales' AND (bonus > 0 OR bonus IS NULL);\n\n-- MISTAKE 3: Redundant conditions\nSELECT * FROM products\nWHERE price > 100 AND price > 50;\n-- The second condition is redundant—if price > 100, it's already > 50\n\n-- MISTAKE 4: Precedence confusion (covered in depth in later pages)\nSELECT * FROM employees\nWHERE department = 'Sales' OR department = 'Marketing'\n AND salary > 50000;\n-- This is actually: Sales OR (Marketing AND salary > 50000)\n-- NOT: (Sales OR Marketing) AND salary > 50000","fileName":"common_mistakes.sql","language":"sql","showLineNumbers":true},"type":"code"}],"titleType":"number","titleNumber":7},{"id":"summary","title":"Summary: Mastering AND","content":[{"data":{"content":"The AND operator is deceptively simple but forms the backbone of precise data querying. Let's consolidate the key insights:"},"type":"text"},{"data":{"type":"ordered","items":["**AND performs logical conjunction** — Returns TRUE only when ALL conditions are TRUE. It's the most restrictive logical operator.","**Three-valued logic with NULL** — Comparisons involving NULL yield UNKNOWN. TRUE AND UNKNOWN = UNKNOWN, which excludes the row from results.","**FALSE is decisive** — FALSE AND anything = FALSE. This enables potential short-circuit optimization.","**Don't rely on evaluation order** — Database optimizers may reorder conditions. Use CASE or CTEs when order matters for safety.","**Each AND condition narrows results** — Adding AND conditions can only reduce or maintain result set size, never increase it.","**Composite indexes help AND queries** — Design indexes with equality conditions first, then range conditions, ordered by selectivity."],"title":{"icon":"CheckCircle","text":"Key Takeaways","type":"icon","color":"primary","variant":"outline-circle"},"variant":"accent"},"type":"list"},{"data":{"content":"**What's Next:**\n\nThe AND operator enables precise conjunction, but many queries require specifying alternative conditions—'either this OR that.' The next page explores the OR operator, which provides disjunction: matching rows that satisfy *any* of the specified conditions."},"type":"text"},{"data":{"icon":"CheckCircle","title":"Page Complete","content":"You now understand the AND operator comprehensively—from its Boolean algebra foundations through SQL's three-valued logic to practical performance optimization. Next, we'll explore OR, which opens up query possibilities by allowing alternative matching conditions.","variant":"success"},"type":"callout"}],"titleType":"number","titleNumber":8}],"isLocked":false},{"id":"seed_6cbb3a5dadd1802aa408a7a3f9fb2150","title":"OR Operator","order":1,"content":null,"isLocked":true},{"id":"seed_00f356c2738acc4c0ccfe46b813c17bd","title":"NOT Operator","order":2,"content":null,"isLocked":true},{"id":"seed_158c4332e6b5f0d28ece699a425582e8","title":"Operator Precedence","order":3,"content":null,"isLocked":true},{"id":"seed_c0a36be75828cb0a331313ef6c7cb633","title":"Complex Conditions","order":4,"content":null,"isLocked":true}]}}