WHERE clause in MySQL Workbench

• The `WHERE` clause in SQL is a powerful tool that allows you to filter rows from a table based on specified conditions. It's used within the `SELECT` statement to narrow down the data retrieved from a table.

Here's a detailed explanation of how the `WHERE` clause works:

• Basic Usage: The basic syntax of the `WHERE` clause is as follows:

    SELECT column1, column2, ...

    FROM table_name

    WHERE condition;

• column1, column2, etc.: The columns you want to retrieve data from.

• table_name: The name of the table you're selecting data from.

• WHERE condition: The condition that specifies which rows to include in the result. If the condition evaluates to `TRUE`, the row is included; otherwise, it's excluded.

• Comparisons and Operators: The `WHERE` clause supports various comparison operators to define conditions. Common operators include:

• `=` (equal to)
• `<>` or `!=` (not equal to)
• `<` (less than)
• `>` (greater than)
• `<=` (less than or equal to)
• `>=` (greater than or equal to)

• Logical Operators: You can use logical operators to combine multiple conditions in the `WHERE` clause:

• AND: Requires all conditions on both sides to be `TRUE`.

• OR: Requires at least one condition on either side to be `TRUE`.

• NOT: Negates a condition.

• Using Strings and Wildcards: When working with text columns, you can use the `%` wildcard to match any sequence of characters, and `_` wildcard to match any single character.

    SELECT * FROM customers WHERE customer_name LIKE 'J%';

    SELECT * FROM products WHERE product_name LIKE '_apple%';

• Using IN and NOT IN: The `IN` operator allows you to specify a list of values that a column's value can be compared against.

    SELECT * FROM orders WHERE order_status IN ('Shipped', 'Delivered');

• Using BETWEEN and NOT BETWEEN: The `BETWEEN` operator allows you to specify a range of values.

    SELECT * FROM employees WHERE salary BETWEEN 30000 AND 50000;

• Using NULL and IS NULL: To check for NULL values, you can use the `IS NULL` operator.

    SELECT * FROM customers WHERE phone_number IS NULL;

• Combining Conditions: You can create complex conditions by combining multiple comparisons and logical operators within parentheses.

    SELECT * FROM products WHERE (price > 100 AND stock_quantity > 10) OR discontinued = 1;

• The `WHERE` clause allows you to retrieve specific subsets of data from a table based on your criteria. By using comparison operators, logical operators, wildcards, and other techniques, you can create intricate conditions to precisely control which rows are included in your query results.