Pre-filteirng Joined Tables

When joining tables, applying filters before the join ipmroves performance and clarity. Instead of filtering after the join with WHERE, embed the condition in a derived table:

SELECT *
FROM table_a a
LEFT JOIN (
  SELECT * 
  FROM table_b 
  WHERE month_value = 1
) b ON a.identifier = b.foreign_key
WHERE a.status = 'active';

This approach restricts table_b to only rows where month_value equals 1 before performing the left join. The outer WHERE clause then applies additional filtering on the joined result—here, limiting table_a records to those with status 'active'.

Extracting Substrings with SUBSTRING_INDEX

The SUBSTRING_INDEX() function splits a string by a delimiter and returns a portion based on occurrence count:

• SUBSTRING_INDEX(str, delim, count)
  • If count > 0: returns the substring from the start up to (but not including) the count-th occurrence of delim.
  • If count < 0: returns the substring from the |count|-th occurrence from the end onward.

Practical Examlpes

Extract first segment:

SELECT SUBSTRING_INDEX('alpha,beta,gamma,delta', ',', 1);
-- Returns: 'alpha'

Extract last segment:

SELECT SUBSTRING_INDEX('alpha,beta,gamma,delta', ',', -1);
-- Returns: 'delta'

Extract middle segment (e.g., third element):

SELECT SUBSTRING_INDEX(
  SUBSTRING_INDEX('alpha,beta,gamma,delta', ',', 3), 
  ',', 
  -1
);
-- Returns: 'gamma'

This nested usage isolates the third comma-delimited value by first truncating after the third delimiter, then extracting the final part.