Handling Row Uniqueness

When ensuring unique records, understand that DISTINCT operates on the combined set of selected columns rather than isolated fields. Although GROUP BY is often preferred for performance to avoid full table scans, specific combinations require verification.

SELECT customer_id, region_code FROM regional_sales
GROUP BY customer_id, region_code;

Inspecting Table Metadata

To retrieve the definition for application synchronization, rely on the DDL generator. For quick field type inspection, the metadata descriptor is sufficient.

SHOW CREATE TABLE inventory_stocks;
DESC product_variants;

Determining Extremes

Identify minimum or maximum values across multiple arguments within a single expression using aggregate-style functions. These handle mixed numeric and lexicographical data.

SELECT LEAST(100, 20, 50); -- Returns 20
SELECT GREATEST(100, 200, 150); -- Returns 200

Arithmetic Type Coercion

Mixing numeric literals with string literals triggers implicit conversion. Non-numeric strings usual convert to zero, which can lead to unintended arithmetic results. Division by zero yields null rather than an error in standard select modes.

SELECT 5 + '0';       -- Evaluates to 5
SELECT 10 / 0;        -- Returns NULL
SELECT -12 % -5;      -- Returns -2

Strict Equality Logic

Standard equality operators treat NULL as unknown, causing comparisons to return NULL instead of boolean true/false. Use the safe equality operator too treat two NULL values as equal.

SELECT 5 = '0';           -- Might return true depending on conversion
SELECT NULL <=> NULL;     -- Returns 1 (True)
SELECT field_x IS NULL;   -- Returns 1 if x is absent

Logical Inversion

Logical negation treats any non-zero number as true, so negating a positive integer results in false. Both symbolic NOT and exclamation mark behave similarly in boolean contexts.

SELECT !42;               -- Returns 0 (False)
SELECT !FALSE_VALUE;      -- Returns 1 (True)

Wildcard Escaping Mechanisms

Control the interpretation of special characters in LIKE queries using either backslash escapes or the explicit ESCAPE clause. The latter provides clearer intent in complex patterns.

SELECT code FROM items WHERE code LIKE '\%_';
SELECT code FROM items WHERE code LIKE '%%_' ESCAPE '%';

Exclusive OR Operation

Perform bitwise exclusive operations. If one operand is valid and the other is null, the result propagates null. Otherwise, return true only if operands differ.

SELECT 1 XOR 1;           -- Returns 0
SELECT 1 XOR NULL;        -- Returns NULL

Clause Evaluation Order

Operator precedence dictates execution flow. Conjunction (AND) binds tighter than disjunction (OR), meaning groups of conditions must often be praenthesized explicitly to override defaults.

SELECT active AND inactive OR pending;
SELECT (active AND inactive) OR pending;

Modern Pagination Syntax

Prior to version 8.0, comma-separated offsets were common. Modern standards prefer specifying the limit followed by the offset keyword for clarity and compatibility with ANSI SQL.

SELECT id, title FROM blog_posts LIMIT 20 OFFSET 40;

Simplified Joins

When joining tables sharing identical column names for the join condition, the USING clause reduces redundancy compared to standard ON comparisons. It also implicitly merges the joined columns in the result set.

SELECT id, name FROM dept JOIN emp USING(dept_id);

Decimal Precision Management

Rounding adjusts values based on proximity, while truncation simply cuts off digits beyond a specified position. Negative precision scales round to the left of the decimal point.

SELECT ROUND(15.5, 0);   -- 16
SELECT TRUNCATE(15.99, 1);-- 15.9

Concatenation Rules

Combining strings with CONCAT results in NULL if any component is null. Conversely, CONCAT_WS allows ignoring null delimiters unless the separator itself is null.

SELECT CONCAT('Hello', NULL, 'World');        -- NULL
SELECT CONCAT_WS(',', 'A', NULL, 'B');         -- A,B

Substring Replacement

Replace occurrences of a specific search pattern within a larger string. If any input parameter evaluates to NULL, the entire function returns NULL.

SELECT REPLACE(domain.net, 'net', 'com');    -- domain.com
SELECT REPLACE(url, NULL, '/api');           -- NULL

Index Location

Determine the starting position of a substring within a main string for filtering purposes. These functions offer alternatives to regular expressions for simple positional checks.

SELECT INSTR('AlphaBeta', 'Beta');           -- 6
SELECT LOCATE('Beta', 'AlphaBeta');          -- 6