IN and NOT IN Operators

Use the IN operator to filter records matching any value in a specified list. Conversely, NOT IN excludes records matching values in the list.

Retrieve records where supplier_id is 101 or 102:

SELECT supplier_id, supplier_name FROM products WHERE supplier_id IN (101, 102);

Retrieve records where supplier_id is neither 101 nor 102:

SELECT supplier_id, supplier_name FROM products WHERE supplier_id NOT IN (101, 102);

BETWEEN and NOT BETWEEN Operators

Filter records based on a range of values, inclusive of the endpoints.

Find products with a unit_price between 2 and 10:

SELECT product_name, unit_price FROM products WHERE unit_price BETWEEN 2 AND 10;

LIKE Operator for Pattern Matching

Perform wildcard searches. The % symbol matches any sequence of characters, while _ matches exactly one character.

Find product names starting with 'b':

SELECT product_name FROM products WHERE product_name LIKE 'b%';

Find product names matching patterns like 'bal_' (e.g., 'ball', 'balk'):

SELECT product_name FROM products WHERE product_name LIKE 'bal_';

Querying NULL Values

Use IS NULL to find records where a column contains a NULL value (distinct from zero or an empty string).

Find records with a NULL product_name:

SELECT product_name FROM products WHERE product_name IS NULL;

ORDER BY for Sorting Results

Sort query results in ascending (ASC) or descending (DESC) order.

SELECT product_name FROM products ORDER BY product_name DESC;

GROUP BY for Data Aggregation

Group rows that share a column value, typically used with aggregate functions like MAX(), MIN(), COUNT(), SUM(), and AVG().

GROUP BY column_name HAVING condition;

Group products by supplier_id and count items per group:

SELECT supplier_id, COUNT(*) AS item_count FROM products GROUP BY supplier_id;

GROUP_CONCAT Function

Display concatenated values from a column for each group.

SELECT supplier_id, GROUP_CONCAT(product_name) AS product_list FROM products GROUP BY supplier_id;

HAVING Clause for Filtering Groups

Apply conditions to aggregated results after GROUP BY.

SELECT supplier_id, supplier_name FROM products GROUP BY supplier_id HAVING COUNT(product_name) > 1;

WITH ROLLUP for Super-Aggregate Totals

Add summary rows showing grand totals to grouped results.

SELECT supplier_id, COUNT(*) FROM products GROUP BY supplier_id WITH ROLLUP;

Multi-Column Grouping

Group data by multiple columns for finer granularity.

SELECT * FROM products GROUP BY supplier_id, product_name;

LIMIT for Result Set Restriction

Constrain the number of rows returned, optionally with an offset.

LIMIT offset, row_count

Fetch the first four products:

SELECT * FROM products LIMIT 4;

INNER JOIN for Table Relationships

Combine rows from two tables based on a related column.

SELECT t2.name, supplier_name FROM products, suppliers t2 WHERE products.supplier_id = t2.supplier_id;

Or using explicit INNER JOIN syntax:

SELECT t2.name, supplier_name FROM products INNER JOIN suppliers t2 ON products.supplier_id = t2.supplier_id;

Self-Join: A join where a table is joined with itself.

SELECT p1.product_name, p1.supplier_id FROM products AS p1, products AS p2 WHERE p1.supplier_id = p2.supplier_id;

LEFT OUTER JOIN

Return all rows from the left table, with matched rows from the right table. Unmatched right table columns show as NULL.

SELECT s.supplier_id, p.product_name FROM suppliers s LEFT OUTER JOIN products p ON s.supplier_id = p.supplier_id;

RIGHT OUTER JOIN

Return all rows from the right table, with matched rows from the left table. Unmatched left table columns show as NULL.

SELECT s.supplier_id, p.product_name FROM suppliers s RIGHT OUTER JOIN products p ON s.supplier_id = p.supplier_id;

Conditional Joins

Apply additional filters directly within the join condition.

SELECT s.supplier_id, p.product_name FROM suppliers s INNER JOIN products p ON s.supplier_id = p.supplier_id AND p.product_name = 'apple' ORDER BY p.product_name;

Subqueries with ANY, ALL, EXISTS

ANY: Returns rows where the condition is true for any value in the subquery result set.

SELECT * FROM products WHERE supplier_id > ANY(SELECT supplier_id FROM suppliers WHERE region='North');

ALL: Returns rows where the condition is true for all values in the subquery result set.

SELECT * FROM products WHERE supplier_id > ALL(SELECT supplier_id FROM suppliers WHERE region='North');

EXISTS: Returns rows if the subquery returns at least one row.

SELECT * FROM products WHERE EXISTS(SELECT supplier_id FROM suppliers WHERE status='Active');

NOT EXISTS: Returns rows if the subquery returns no rows.

SELECT * FROM products WHERE NOT EXISTS(SELECT supplier_id FROM suppliers WHERE status='Inactive');

IN / NOT IN with Subqueries:

SELECT * FROM products WHERE supplier_id IN(SELECT supplier_id FROM suppliers WHERE region='West');

Comparison Operators with Subqueries: Ensure the subquery returns a single value.

SELECT * FROM products WHERE supplier_id = (SELECT supplier_id FROM suppliers WHERE supplier_id = 101);

Combining Result Sets with UNION

UNION combines results from multipel SELECT statements, removing duplicate rows by default. UNION ALL retains all rows, including duplicates.

SELECT supplier_id FROM products WHERE unit_price < 9
UNION ALL
SELECT supplier_id FROM suppliers WHERE supplier_id IN(10, 11);

Regular Expression Queries (REGEXP)

Perform advanced pattern matching using regular expressions.

Find product names starting with 'b':

SELECT * FROM products WHERE product_name REGEXP '^b';