DB --> LEC 7 --> Topics --> 19 --> 30

Below is a revised version of the provided content, rewritten to focus specifically on MySQL, ensuring all examples, syntax, and explanations are tailored to MySQL’s implementation of SQL. Each concept is explained clearly, with MySQL-specific details, syntax, and practical examples using MySQL conventions and features. The content retains the original structure and depth but is now MySQL-centric.

---

MySQL Concepts: Detailed Explanation with Syntax and Examples

Below is a detailed explanation of each MySQL concept listed, including their syntax, purpose, and examples. Each topic is explained with clarity and depth to ensure a comprehensive understanding, with practical MySQL examples to illustrate their use.

---

19. DISTINCT With COUNT in MySQL

The DISTINCT keyword in MySQL eliminates duplicate values from a query’s result set. When used with the COUNT function, COUNT(DISTINCT column_name) counts only unique, non-NULL values in the specified column.

Syntax:

SELECT COUNT(DISTINCT column_name) AS alias_name
FROM table_name
WHERE condition;

Key Points:

• DISTINCT ensures only unique values are counted.
• NULL values are excluded because MySQL does not consider NULL a distinct value.
• Performance may be slower with COUNT(DISTINCT ...) compared to COUNT(*) due to sorting and deduplication.
• Can be combined with GROUP BY to count distinct values within groups.
• MySQL supports APPROX_COUNT_DISTINCT (introduced in MySQL 8.0.13 for Aurora) for faster approximate counts in large datasets.

Example: Consider a MySQL table orders:

cust_code	ord_amount	ord_date
C00001	1000	2023-01-15
C00002	1500	2023-02-10
C00001	2000	2023-03-05
C00003	1000	2023-04-20

To count unique customers:

SELECT COUNT(DISTINCT cust_code) AS unique_customers
FROM orders;

Result:

unique_customers
3

Explanation: Only three unique cust_code values (C00001, C00002, C00003) are counted, despite C00001 appearing twice.

Use with GROUP BY: To count distinct customers per order date:

SELECT ord_date, COUNT(DISTINCT cust_code) AS unique_customers
FROM orders
GROUP BY ord_date;

Result:

ord_date	unique_customers
2023-01-15	1
2023-02-10	1
2023-03-05	1
2023-04-20	1

Performance Note: COUNT(DISTINCT ...) can be resource-intensive on large datasets. For approximate counts in MySQL Aurora, use APPROX_COUNT_DISTINCT:

SELECT APPROX_COUNT_DISTINCT(cust_code) AS approx_unique_customers
FROM orders;

---

20. MySQL SELECT AS Alias

The AS keyword in MySQL assigns a temporary name (alias) to a column or table in the result set, improving readability or simplifying complex queries.

Syntax:

SELECT column_name AS alias_name
FROM table_name;

Key Points:

• Aliases are temporary and only affect the query output, not the database schema.
• The AS keyword is optional in MySQL; you can write SELECT column_name alias_name.
• MySQL allows double quotes (") or backticks (`) for aliases with spaces or special characters.
• Useful for renaming columns or clarifying computed expressions.

Example: Consider a MySQL table employees:

emp_id	first_name	salary
1	John	50000
2	Jane	60000

To rename columns in the output:

SELECT first_name AS `Employee Name`, salary AS `Annual Salary`
FROM employees;

Result:

Employee Name	Annual Salary
John	50000
Jane	60000

Example with Expression: To calculate a yearly bonus (10% of salary) with an alias:

SELECT first_name, salary * 0.10 AS bonus
FROM employees;

Result:

first_name	bonus
John	5000
Jane	6000

Note: Table aliases (e.g., FROM employees AS e) are useful in MySQL joins to shorten table names:

SELECT e.first_name
FROM employees AS e;

---

21. MySQL AS With More Than One Column

In MySQL, you can use AS to alias multiple columns in a single SELECT statement, including computed columns or concatenated expressions, to make output more descriptive.

Syntax:

SELECT column1 AS alias1, column2 AS alias2, expression AS alias3
FROM table_name;

Key Points:

• Each column or expression can have its own alias.
• MySQL uses CONCAT for string concatenation.
• Aliases improve readability, especially for concatenated or computed columns.

Example: Using the employees table:

emp_id	first_name	last_name	salary
1	John	Doe	50000
2	Jane	Smith	60000

To alias multiple columns and concatenate first_name and last_name:

SELECT first_name AS `First`, last_name AS `Last`,
       CONCAT(first_name, ' ', last_name) AS `Full Name`,
       salary AS `Annual Salary`
FROM employees;

Result:

First	Last	Full Name	Annual Salary
John	Doe	John Doe	50000
Jane	Smith	Jane Smith	60000

Example with Computed Columns: To calculate salary after a 5% tax and alias the result:

SELECT first_name AS `Name`, salary AS `Gross Salary`,
       salary * 0.95 AS `Net Salary`
FROM employees;

Result:

Name	Gross Salary	Net Salary
John	50000	47500
Jane	60000	57000

Note: Use meaningful, unique aliases to avoid confusion. MySQL’s CONCAT is efficient for combining strings, but ensure proper spacing (e.g., ' ' in CONCAT).

---

22. MySQL SELECT LIMIT

The LIMIT clause in MySQL restricts the number of rows returned by a query, ideal for pagination or sampling data.

Syntax:

SELECT column_name
FROM table_name
LIMIT number_of_rows;

Extended Syntax (with Offset):

SELECT column_name
FROM table_name
LIMIT offset, number_of_rows;

Key Points:

• LIMIT accepts a non-negative integer.
• LIMIT offset, count skips offset rows and returns count rows.
• Often used with ORDER BY to control which rows are returned.
• MySQL does not use TOP or FETCH FIRST (unlike SQL Server or Oracle).

Example: Consider a MySQL table products:

prod_id	prod_name	price
1	Laptop	1000
2	Phone	500
3	Tablet	300
4	Monitor	200

To retrieve the top 2 most expensive products:

SELECT prod_name, price
FROM products
ORDER BY price DESC
LIMIT 2;

Result:

prod_name	price
Laptop	1000
Phone	500

Example with Offset: To skip the first row and return the next 2 rows:

SELECT prod_name, price
FROM products
ORDER BY price DESC
LIMIT 1, 2;

Result:

prod_name	price
Phone	500
Tablet	300

Note: LIMIT is highly efficient in MySQL for small datasets or paginated results. For large datasets, ensure indexes support the ORDER BY clause to optimize performance.

---

23. MySQL IN Operator

The IN operator in MySQL filters rows where a column’s value matches any value in a specified list, simplifying multiple OR conditions.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name IN (value1, value2, ..., valueN);

Key Points:

• Equivalent to column_name = value1 OR column_name = value2.
• Can be used with subqueries to dynamically generate the list.
• Improves readability and performance compared to chained OR clauses.

Example: Using the products table:

prod_id	prod_name	category
1	Laptop	Electronics
2	Phone	Electronics
3	Desk	Furniture
4	Chair	Furniture

To select products in specific categories:

SELECT prod_name, category
FROM products
WHERE category IN ('Electronics', 'Furniture');

Result:

prod_name	category
Laptop	Electronics
Phone	Electronics
Desk	Furniture
Chair	Furniture

Example with Subquery: To select products from categories with a product priced above 500:

SELECT prod_name, category
FROM products
WHERE category IN (
    SELECT category
    FROM products
    WHERE price > 500
);

Result:

prod_name	category
Laptop	Electronics
Phone	Electronics

Note: Ensure subqueries used with IN are optimized, as they can impact performance on large datasets.

---

24. MySQL NOT IN Operator

The NOT IN operator in MySQL filters rows where a column’s value does not match any value in a specified list.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name NOT IN (value1, value2, ..., valueN);

Key Points:

• Opposite of the IN operator.
• Can be used with subqueries.
• Be cautious with NULL values in the list or subquery, as they can cause unexpected results (e.g., NOT IN (value1, NULL) may return no rows).

Example: Using the products table:

SELECT prod_name, category
FROM products
WHERE category NOT IN ('Electronics');

Result:

prod_name	category
Desk	Furniture
Chair	Furniture

Example with Subquery: To exclude products from categories with high-priced items:

SELECT prod_name, category
FROM products
WHERE category NOT IN (
    SELECT category
    FROM products
    WHERE price > 500
);

Result:

prod_name	category
Desk	Furniture
Chair	Furniture

Caution with NULL: If a subquery returns NULL, NOT IN may return no rows because column_name NOT IN (value1, NULL) evaluates to UNKNOWN. To avoid this, exclude NULL values:

SELECT prod_name, category
FROM products
WHERE category NOT IN (
    SELECT category
    FROM products
    WHERE price > 500
    AND category IS NOT NULL
);

Note: For better performance with large lists, consider using NOT EXISTS instead of NOT IN for subqueries.

---

25. MySQL BETWEEN Operator

The BETWEEN operator in MySQL selects values within a specified range, inclusive of the start and end values. It works with numbers, text, and dates.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Key Points:

• Inclusive: Both value1 and value2 are included.
• Equivalent to column_name >= value1 AND column_name <= value2.
• Can be combined with other conditions using AND or OR.

Example (Numbers): Using the products table:

prod_id	prod_name	price
1	Laptop	1000
-dense_rank	2	Phone
3	Tablet	300
4	Monitor	200

To select products with prices between 300 and 1000:

SELECT prod_name, price
FROM products
WHERE price BETWEEN 300 AND 1000;

Result:

prod_name	price
Laptop	1000
Phone	500
Tablet	300

Example with Additional Condition: To select products in a price range and specific categories:

SELECT prod_name, price, category
FROM products
WHERE price BETWEEN 300 AND 1000
AND category IN ('Electronics');

Result:

prod_name	price	category
Laptop	1000	Electronics
Phone	500	Electronics
Tablet	300	Electronics

Note: BETWEEN is inclusive, so verify range boundaries to ensure correct results.

---

26. MySQL NOT BETWEEN Operator

The NOT BETWEEN operator in MySQL selects values outside a specified range, excluding the start and end values.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

Key Points:

• Equivalent to column_name < value1 OR column_name > value2.
• Works with numbers, text, and dates.
• Can be combined with other conditions.

Example: Using the products table:

SELECT prod_name, price
FROM products
WHERE price NOT BETWEEN 300 AND 1000;

Result:

prod_name	price
Monitor	200

Example with Additional Condition: To select products outside a price range and in specific categories:

SELECT prod_name, price, category
FROM products
WHERE price NOT BETWEEN 300 AND 1000
AND category IN ('Electronics');

Result:

prod_name	price	category
Monitor	200	Electronics

Note: Ensure logical range boundaries to avoid empty result sets.

---

27. MySQL BETWEEN Operator with Text and Dates

The BETWEEN operator in MySQL can be used with text (alphabetical ranges) and dates (time ranges) in addition to numbers.

Syntax (Text):

SELECT column_name
FROM table_name
WHERE column_name BETWEEN 'value1' AND 'value2';

Syntax (Dates):

SELECT column_name
FROM table_name
WHERE column_name BETWEEN 'date1' AND 'date2';

Key Points:

• Text: Compares strings alphabetically (case-sensitive by default in MySQL, depending on collation).
• Dates: Compares dates chronologically; use YYYY-MM-DD format for MySQL compatibility.
• Inclusive for both text and dates.

Example (Text): Using the products table:

SELECT prod_name
FROM products
WHERE prod_name BETWEEN 'Laptop' AND 'Tablet'
ORDER BY prod_name;

Result:

prod_name
Laptop
Phone
Tablet

Explanation: Products with names alphabetically between Laptop and Tablet (inclusive) are returned.

Example (Dates): Consider a MySQL table orders:

order_id	order_date
1	2023-01-15
2	2023-02-10
3	2023-03-05
4	2023-04-20

To select orders between February and March 2023:

SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2023-02-01' AND '2023-03-31';

Result:

order_id	order_date
2	2023-02-10
3	2023-03-05

Note:

• For text, ensure consistent case or use LOWER()/UPPER() if case-insensitive comparison is needed.
• For dates, MySQL expects YYYY-MM-DD by default, but you can use STR_TO_DATE for custom formats.

---

28. MySQL IS NULL Syntax

The IS NULL condition in MySQL checks if a column contains a NULL value, representing missing or unknown data.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

Key Points:

• NULL is not the same as zero or an empty string; it indicates no value.
• You cannot use column_name = NULL because NULL comparisons with = yield UNKNOWN.
• Useful for identifying missing data.

Example: Consider a MySQL table customers:

cust_id	name	phone
1	John	123-456-7890
2	Jane	NULL
3	Bob	NULL

To find customers with no phone number:

SELECT name
FROM customers
WHERE phone IS NULL;

Result:

name
Jane
Bob

Note: IS NULL is critical for data cleaning or validation in MySQL.

---

29. MySQL IS NOT NULL Syntax

The IS NOT NULL condition in MySQL checks if a column contains a non-NULL value.

Syntax:

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;

Key Points:

• Opposite of IS NULL.
• Filters rows with valid, non-missing data.
• Cannot use column_name != NULL for the same reason as IS NULL.

Example: Using the customers table:

SELECT name, phone
FROM customers
WHERE phone IS NOT NULL;

Result:

name	phone
John	123-456-7890

Note: IS NOT NULL is useful for ensuring data completeness in MySQL reports or queries.

---

30. MySQL IS NULL With COUNT()

Using IS NULL with COUNT in MySQL allows you to count rows where a column is NULL or analyze missing data.

Syntax:

SELECT COUNT(*) AS null_count
FROM table_name
WHERE column_name IS NULL;

Key Points:

• COUNT(*) counts all rows matching the WHERE condition, including those with NULL.
• COUNT(column_name) excludes NULL values, so it’s not suitable for counting NULL rows.
• COUNT(DISTINCT column_name) ignores NULL values.
• To include NULL as a distinct value, use COALESCE.

Example: Using the customers table:

SELECT COUNT(*) AS null_phone_count
FROM customers
WHERE phone IS NULL;

Result:

null_phone_count
2

Explanation: Two customers have NULL phone numbers.

Example with Non-NULL Count: To count non-NULL phone numbers:

SELECT COUNT(phone) AS non_null_phone_count
FROM customers;

Result:

non_null_phone_count
1

Explanation: COUNT(phone) only counts rows where phone is not NULL.

Example with DISTINCT and NULL: To count distinct phone numbers, excluding NULL:

SELECT COUNT(DISTINCT phone) AS distinct_phones
FROM customers;

Result:

distinct_phones
1

Workaround to Count NULL as Distinct: To treat NULL as a distinct value:

SELECT COUNT(DISTINCT COALESCE(phone, 'NULL')) AS distinct_phones_including_null
FROM customers;

Result:

distinct_phones_including_null
2

Note: COALESCE is a MySQL function that returns the first non-NULL value, making it useful for handling NULL in such scenarios.

---

This content is now fully tailored to MySQL, with accurate syntax, MySQL-specific features (e.g., CONCAT, LIMIT, COALESCE), and examples that reflect MySQL’s behavior. Let me know if you need further adjustments or additional MySQL topics!

Database SQL

You may like these posts

Comments