---

Table of Contents

1. Introduction
2. What is the HAVING Clause?
3. Difference Between WHERE and HAVING
4. Why Use HAVING?
5. Basic Syntax of HAVING
6. Simple Example with HAVING
7. Using HAVING with COUNT()
8. Filtering on SUM() with HAVING
9. Using HAVING with AVG(), MIN(), MAX()
10. Combining WHERE and HAVING
11. HAVING with Multiple Conditions
12. Sorting After HAVING
13. HAVING Without GROUP BY (Yes, You Can!)
14. Real-World Use Case: Active Customers
15. Real-World Use Case: Sales by Region
16. Common Errors with HAVING
17. Performance Considerations
18. Best Practices for Using HAVING
19. Summary Table: HAVING vs WHERE
20. Summary and What’s Next

---

1. Introduction

The HAVING clause in SQL allows you to filter grouped data after aggregation. It’s often misunderstood or confused with WHERE, but they serve very different purposes. In this module, we’ll clarify the role of HAVING and teach you how to use it effectively.

---

2. What is the HAVING Clause?

HAVING is used to filter the results of GROUP BY queries based on aggregate functions like COUNT(), SUM(), AVG(), etc.

---

3. Difference Between WHERE and HAVING

Clause	Filters	Applied Before/After Grouping
WHERE	Rows	Before GROUP BY
HAVING	Groups	After GROUP BY

---

4. Why Use HAVING?

HAVING lets you:

• Show only groups that meet certain conditions
• Filter aggregated data
• Extend the power of GROUP BY

---

5. Basic Syntax of HAVING

sqlCopyEditSELECT column, AGG_FUNC(column)
FROM table
GROUP BY column
HAVING AGG_FUNC(column) condition;

Example:

sqlCopyEditSELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department
HAVING total_employees > 5;

---

6. Simple Example with HAVING

sqlCopyEditSELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING user_count >= 10;

This returns only cities with 10 or more users.

---

7. Using HAVING with COUNT()

sqlCopyEditSELECT product_id, COUNT(*) AS order_count
FROM orders
GROUP BY product_id
HAVING order_count > 20;

This helps identify popular products.

---

8. Filtering on SUM() with HAVING

sqlCopyEditSELECT customer_id, SUM(amount) AS total_spent
FROM transactions
GROUP BY customer_id
HAVING total_spent > 1000;

Finds customers who’ve spent over ₹1000.

---

9. Using HAVING with AVG(), MIN(), MAX()

sqlCopyEditSELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING avg_price < 500;

You can apply HAVING to any aggregate metric.

---

10. Combining WHERE and HAVING

sqlCopyEditSELECT department, COUNT(*) AS total
FROM employees
WHERE is_active = TRUE
GROUP BY department
HAVING total >= 5;

• WHERE filters before grouping
• HAVING filters after aggregation

---

11. HAVING with Multiple Conditions

sqlCopyEditSELECT region, SUM(sales) AS total_sales
FROM orders
GROUP BY region
HAVING total_sales > 100000 AND COUNT(*) > 10;

You can use AND, OR, and even parentheses for logic.

---

12. Sorting After HAVING

sqlCopyEditSELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 40000
ORDER BY avg_salary DESC;

ORDER BY works after HAVING in the execution order.

---

13. HAVING Without GROUP BY (Yes, You Can!)

Yes, HAVING can be used without GROUP BY — usually to filter on aggregates over the entire table.

sqlCopyEditSELECT SUM(amount) AS total_revenue
FROM sales
HAVING total_revenue > 100000;

---

14. Real-World Use Case: Active Customers

sqlCopyEditSELECT user_id, COUNT(*) AS login_count
FROM logins
GROUP BY user_id
HAVING login_count >= 20;

Find power users based on login activity.

---

15. Real-World Use Case: Sales by Region

sqlCopyEditSELECT region, SUM(total_amount) AS total_sales
FROM orders
GROUP BY region
HAVING total_sales > 50000;

Filter out underperforming regions.

---

16. Common Errors with HAVING

Mistake	Why it’s Wrong
Using HAVING without aggregation	Use WHERE instead
Using WHERE with aggregates	Should be moved to HAVING
Forgetting to alias or qualify columns	Leads to confusion or SQL errors

---

17. Performance Considerations

• Use WHERE for row-level filters to reduce rows before grouping
• Avoid putting non-aggregated conditions in HAVING
• Combine HAVING with indexes for faster filtering

---

18. Best Practices for Using HAVING

• Only use HAVING for aggregated conditions
• Use meaningful aliases for readability
• Use WHERE first to limit rows
• Document logic when combining with GROUP BY

---

19. Summary Table: HAVING vs WHERE

Feature	WHERE	HAVING
Filters	Rows	Groups
Used With	Any query	With GROUP BY
Can Use Aggregates?	❌	✅
Evaluated When?	Before grouping	After grouping

---

20. Summary and What’s Next

The HAVING clause is your post-aggregation filter, allowing you to include only those groups that meet a specific condition. Combined with GROUP BY and aggregate functions, it becomes a powerful tool for analytical queries.

---

Table of Contents

1. Introduction
2. What is GROUP BY in SQL?
3. Basic Syntax of GROUP BY
4. When to Use GROUP BY
5. Simple GROUP BY Example
6. Grouping by One Column
7. Grouping by Multiple Columns
8. Using Aggregate Functions with GROUP BY
9. Using HAVING to Filter Groups
10. GROUP BY vs WHERE
11. Ordering Grouped Results
12. Grouping NULL Values
13. Real-World Example: Sales by Region
14. Real-World Example: Employee Count by Department
15. Grouping with Joins
16. Common Errors with GROUP BY
17. Performance Considerations
18. Nested Grouping and Subqueries
19. Best Practices for GROUP BY
20. Summary and What’s Next

---

1. Introduction

The GROUP BY clause in SQL is one of the most powerful tools for summarizing data. It allows you to organize rows into groups and apply aggregate functions such as COUNT(), SUM(), AVG(), etc., on each group.

---

2. What is GROUP BY in SQL?

GROUP BY groups rows that have the same values in specified columns into summary rows. It’s often used in combination with aggregate functions to perform group-wise computations.

---

3. Basic Syntax of GROUP BY

sqlCopyEditSELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;

Example:

sqlCopyEditSELECT department, COUNT(*) 
FROM employees
GROUP BY department;

---

4. When to Use GROUP BY

Use GROUP BY when:

• You want to aggregate values based on a specific column (e.g., department, category).
• You need summary statistics per group.
• You’re building reports or dashboards.

---

5. Simple GROUP BY Example

sqlCopyEditSELECT category, SUM(price) AS total_sales
FROM products
GROUP BY category;

This shows total sales per product category.

---

6. Grouping by One Column

sqlCopyEditSELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

Returns one row per city, showing how many users are from each.

---

7. Grouping by Multiple Columns

sqlCopyEditSELECT city, gender, COUNT(*) 
FROM users
GROUP BY city, gender;

This groups first by city, then by gender within each city.

---

8. Using Aggregate Functions with GROUP BY

You can use:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

Example:

sqlCopyEditSELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

---

9. Using HAVING to Filter Groups

Use HAVING to filter the grouped results:

sqlCopyEditSELECT department, COUNT(*) AS total
FROM employees
GROUP BY department
HAVING total > 5;

Unlike WHERE, HAVING works on aggregated data.

---

10. GROUP BY vs WHERE

Clause	When It Works
WHERE	Before grouping (filters rows)
HAVING	After grouping (filters groups)

Example:

sqlCopyEditSELECT department, SUM(salary)
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING SUM(salary) > 100000;

---

11. Ordering Grouped Results

sqlCopyEditSELECT department, COUNT(*) AS total
FROM employees
GROUP BY department
ORDER BY total DESC;

You can sort the output using ORDER BY, even on aliases.

---

12. Grouping NULL Values

GROUP BY treats NULL as a valid group:

sqlCopyEditSELECT department, COUNT(*) 
FROM employees
GROUP BY department;

Rows with NULL in department will be grouped together.

---

13. Real-World Example: Sales by Region

sqlCopyEditSELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Use GROUP BY to break down KPIs by geography.

---

14. Real-World Example: Employee Count by Department

sqlCopyEditSELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING employee_count >= 10;

---

15. Grouping with Joins

sqlCopyEditSELECT c.country, COUNT(o.id) AS total_orders
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.country;

This groups joined data — e.g., orders by country.

---

16. Common Errors with GROUP BY

• Using columns in SELECT that aren’t part of GROUP BY or aggregates
• Using WHERE to filter aggregated values (should use HAVING)
• Forgetting to group on all non-aggregated columns

Example of incorrect usage:

sqlCopyEditSELECT department, name, COUNT(*) 
FROM employees
GROUP BY department;  -- ERROR: 'name' must be aggregated or grouped

---

17. Performance Considerations

• Use indexes on GROUP BY columns for faster grouping
• Avoid grouping on columns with high cardinality unless necessary
• Apply WHERE filters before grouping to reduce the dataset size

---

18. Nested Grouping and Subqueries

sqlCopyEditSELECT region, AVG(total_sales)
FROM (
  SELECT region, SUM(amount) AS total_sales
  FROM sales
  GROUP BY region, month
) AS monthly_sales
GROUP BY region;

This calculates average monthly sales by region.

---

19. Best Practices for GROUP BY

• Use aliases for clarity (AS)
• Filter with WHERE before grouping
• Use HAVING only when working with aggregates
• Combine with ORDER BY to make reports readable
• Always include only grouped or aggregated columns in SELECT

---

20. Summary and What’s Next

The GROUP BY clause is your gateway to powerful analytical queries in SQL. Whether you’re generating category-wise sales, user stats by country, or monthly trends, GROUP BY helps convert rows into grouped summaries.

---

Table of Contents

1. Introduction
2. What are Aggregate Functions?
3. Syntax of Aggregate Functions
4. Using COUNT() to Count Records
5. Counting Non-NULL vs All Rows
6. Using SUM() for Totaling Values
7. Applying SUM() with Filters
8. Using AVG() to Find Averages
9. Ignoring NULL in AVG()
10. Using MIN() and MAX() to Find Extremes
11. Aggregate Functions with GROUP BY
12. Using HAVING with Aggregate Results
13. Combining Aggregates in One Query
14. Nesting Aggregate Functions
15. Aggregate Functions in Joins
16. Handling NULL Values in Aggregations
17. Performance Considerations
18. Common Mistakes and How to Avoid Them
19. Best Practices for Aggregation
20. Summary and What’s Next

---

1. Introduction

SQL aggregate functions are essential for data analysis. They allow you to perform calculations across rows — like counting, totaling, averaging, and finding the maximum or minimum. Whether you’re generating reports, dashboards, or analytics, these functions are foundational.

---

2. What are Aggregate Functions?

Aggregate functions take a set of values (like a column) and return a single summarized result.

Most common:

• COUNT(): total number of rows or values
• SUM(): total of numeric values
• AVG(): average of numeric values
• MIN(): lowest value
• MAX(): highest value

---

3. Syntax of Aggregate Functions

sqlCopyEditSELECT AGGREGATE_FUNCTION(column_name)
FROM table_name
[WHERE condition];

Example:

sqlCopyEditSELECT COUNT(*) FROM users;
SELECT AVG(price) FROM products;

---

4. Using COUNT() to Count Records

COUNT() returns the number of rows that match a condition.

sqlCopyEditSELECT COUNT(*) FROM employees;
SELECT COUNT(name) FROM employees;

• COUNT(*) counts all rows
• COUNT(column) counts only non-NULL values in that column

---

5. Counting Non-NULL vs All Rows

sqlCopyEdit-- Count all employees
SELECT COUNT(*) FROM employees;

-- Count employees with recorded emails
SELECT COUNT(email) FROM employees;

If email has NULL values, those rows will be excluded in the second query.

---

6. Using SUM() for Totaling Values

SUM() adds up all values in a numeric column.

sqlCopyEditSELECT SUM(salary) FROM employees;

Returns the total salary expense.

---

7. Applying SUM() with Filters

sqlCopyEditSELECT SUM(amount) FROM orders
WHERE status = 'completed';

This returns the total value of all completed orders.

---

8. Using AVG() to Find Averages

AVG() returns the arithmetic mean:

sqlCopyEditSELECT AVG(price) FROM products;

Useful for finding average prices, ages, ratings, etc.

---

9. Ignoring NULL in AVG()

AVG() ignores NULL values:

sqlCopyEditSELECT AVG(rating) FROM reviews;

Only includes rows where rating is not NULL.

---

10. Using MIN() and MAX() to Find Extremes

sqlCopyEditSELECT MIN(price) AS lowest_price, MAX(price) AS highest_price 
FROM products;

This gives both minimum and maximum in a single query.

---

11. Aggregate Functions with GROUP BY

GROUP BY combines rows with the same value into groups, allowing you to apply aggregate functions to each group.

sqlCopyEditSELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department;

---

12. Using HAVING with Aggregate Results

HAVING filters after aggregation (unlike WHERE).

sqlCopyEditSELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 60000;

---

13. Combining Aggregates in One Query

sqlCopyEditSELECT 
  COUNT(*) AS total_orders,
  SUM(amount) AS total_sales,
  AVG(amount) AS avg_order_value
FROM orders;

Returns multiple summaries in one result.

---

14. Nesting Aggregate Functions

You can use aggregates in subqueries:

sqlCopyEditSELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

This returns employees earning more than the average.

---

15. Aggregate Functions in Joins

sqlCopyEditSELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

Get each user’s order count using a join + group.

---

16. Handling NULL Values in Aggregations

• COUNT(column): ignores NULL
• SUM(), AVG(): skip NULL
• MIN()/MAX() typically ignore NULL

If a column is entirely NULL, the aggregate may return NULL.

---

17. Performance Considerations

• Aggregations on indexed columns are faster
• Avoid unnecessary columns in SELECT
• Use filters (WHERE, HAVING) to reduce row count before aggregating
• Prefer COUNT(*) for full row counts over COUNT(column) unless filtering nulls

---

18. Common Mistakes and How to Avoid Them

Mistake	Correction
Using WHERE instead of HAVING	Use HAVING to filter aggregated results
Forgetting to GROUP BY	Always GROUP BY non-aggregated columns
Assuming NULL = 0	Use COALESCE() or expect NULL to be skipped
Comparing aggregates in WHERE	Move those comparisons to HAVING

---

19. Best Practices for Aggregation

• Name your output with aliases (AS)
• Use GROUP BY for per-category summaries
• Use HAVING for aggregate-based filtering
• Handle NULL explicitly using COALESCE() if needed
• Keep queries lean — only select what you need

---

20. Summary and What’s Next

Aggregate functions like COUNT, SUM, AVG, MIN, and MAX are at the heart of data analytics in SQL. They allow you to turn raw data into meaningful insights.

1. Introduction
2. What is NULL in SQL?
3. NULL vs Zero vs Empty String
4. Why NULL Exists in Databases
5. Checking for NULL: IS NULL and IS NOT NULL
6. Using NULL in WHERE Clauses
7. NULL in SELECT Statements
8. Aggregating Data with NULL
9. Handling NULL in Comparisons
10. Using COALESCE() to Replace NULL
11. Using IFNULL() and NULLIF()
12. Using CASE to Deal with NULL
13. Sorting with NULL Values
14. Filtering with NOT IN and NULL
15. NULL in JOIN Conditions
16. NULL in GROUP BY and HAVING
17. Designing Tables with or without NULL
18. Common Mistakes with NULL
19. Best Practices for Working with NULL
20. Summary and What’s Next

---

1. Introduction

In SQL, handling missing or unknown data is critical. That’s where NULL comes in. Unlike 0 or '' (empty string), NULL means “no value” or “unknown value.” This module explains how SQL treats NULL, how it affects queries, and how to handle it cleanly.

---

2. What is NULL in SQL?

NULL represents the absence of a value. It is not a number, not a string, and not even a default value. It simply indicates nothing.

---

3. NULL vs Zero vs Empty String

Value	Meaning
NULL	Unknown or missing data
0	Known numeric value (zero)
'' (empty)	Known blank string

They are not equal to each other in SQL.

---

4. Why NULL Exists in Databases

• Optional data (e.g., phone number, middle name)
• Missing values during data entry
• Future updates
• Unknown measurements or references

NULL allows databases to handle incomplete or pending data gracefully.

---

5. Checking for NULL: IS NULL and IS NOT NULL

You cannot use = or != to check for NULL.

Use:

sqlCopyEditSELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

---

6. Using NULL in WHERE Clauses

sqlCopyEditSELECT * FROM employees 
WHERE department IS NULL;

To find employees without assigned departments.

---

7. NULL in SELECT Statements

NULL appears in the result as blank or NULL, depending on the client.

sqlCopyEditSELECT name, phone FROM customers;

If phone is missing, the result will show a NULL.

---

8. Aggregating Data with NULL

Most aggregate functions ignore NULLs:

sqlCopyEditSELECT AVG(salary) FROM employees;  -- NULLs excluded
SELECT COUNT(*) FROM employees;     -- counts all rows
SELECT COUNT(salary) FROM employees; -- excludes NULL salaries

---

9. Handling NULL in Comparisons

sqlCopyEditSELECT * FROM products 
WHERE price != 0;  -- Excludes NULLs!

SELECT * FROM products 
WHERE price IS NULL OR price != 0;  -- Includes NULLs too

Any expression with NULL in it returns NULL (which is treated as false in conditions).

---

10. Using COALESCE() to Replace NULL

COALESCE() returns the first non-NULL value in a list:

sqlCopyEditSELECT name, COALESCE(phone, 'Not Provided') AS contact_number
FROM customers;

---

11. Using IFNULL() and NULLIF()

• IFNULL(col, default) → MySQL / SQLite
• NULLIF(val1, val2) → returns NULL if both values are equal

sqlCopyEditSELECT IFNULL(email, 'N/A') FROM users;
SELECT NULLIF(10, 10);  -- Returns NULL

---

12. Using CASE to Deal with NULL

sqlCopyEditSELECT name,
  CASE 
    WHEN phone IS NULL THEN 'Unknown'
    ELSE phone
  END AS phone_number
FROM customers;

You can customize NULL logic using CASE.

---

13. Sorting with NULL Values

Different DBMS handle NULL sorting differently:

• PostgreSQL:

sqlCopyEditORDER BY age DESC NULLS LAST;

• MySQL/SQLite: NULL usually appears first with ASC, last with DESC.

---

14. Filtering with NOT IN and NULL

Be cautious:

sqlCopyEditSELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

If the subquery contains a NULL, it may return no results.

---

15. NULL in JOIN Conditions

Join results are affected when keys are NULL:

sqlCopyEditSELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

If no order exists, o.total will be NULL.

---

16. NULL in GROUP BY and HAVING

sqlCopyEditSELECT department, COUNT(*) 
FROM employees
GROUP BY department;

Rows with NULL department will be grouped together.

You can also filter groups with:

sqlCopyEditHAVING department IS NULL;

---

17. Designing Tables with or without NULL

sqlCopyEditCREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(15)  -- nullable by default
);

Use NOT NULL where values must always exist.

---

18. Common Mistakes with NULL

• Comparing with = NULL instead of IS NULL
• Forgetting NULLs in NOT IN subqueries
• Assuming NULL = 0 or ''
• Failing to account for NULLs in aggregations

---

19. Best Practices for Working with NULL

• Use NOT NULL constraints where data is mandatory
• Use COALESCE() or CASE to clean up query output
• Avoid NULL in foreign keys when possible
• Handle NULLs in sorting, aggregations, and filtering explicitly
• Document which columns may contain NULLs

---

20. Summary and What’s Next

NULL is a powerful feature of SQL that allows databases to handle incomplete or unknown information. However, if used without care, it can lead to incorrect or confusing results. Mastering how to work with NULL helps you write more accurate and resilient queries.

---

Table of Contents

1. Introduction
2. What is the LIMIT Clause?
3. Syntax of LIMIT
4. Why Use LIMIT?
5. Basic Example of LIMIT
6. What is the OFFSET Clause?
7. Syntax of LIMIT with OFFSET
8. Practical Pagination with LIMIT and OFFSET
9. Using ORDER BY with LIMIT
10. Retrieving Top-N Records
11. Pagination UI and SQL Integration
12. MySQL vs PostgreSQL vs SQLite: Syntax Notes
13. Common Errors with LIMIT and OFFSET
14. LIMIT with Joins
15. LIMIT in Subqueries
16. Performance Tips
17. Real-World Use Cases
18. Best Practices
19. Summary Table of Syntax Patterns
20. Summary and What’s Next

---

1. Introduction

In SQL, the result of a query can return thousands of records — but often, we only need a subset. The LIMIT and OFFSET clauses let you control how many rows you get and where to start reading from. This is essential for building pagination, leaderboards, and top-ranked reports.

---

2. What is the LIMIT Clause?

The LIMIT clause restricts the number of rows returned by a query.

---

3. Syntax of LIMIT

sqlCopyEditSELECT column1, column2
FROM table_name
LIMIT number_of_rows;

Example:

sqlCopyEditSELECT * FROM products
LIMIT 10;

This returns only the first 10 rows of the products table.

---

4. Why Use LIMIT?

• Prevents excessive data retrieval
• Speeds up query performance
• Useful in pagination, previews, summaries
• Reduces network and UI load

---

5. Basic Example of LIMIT

sqlCopyEditSELECT name, price FROM products
ORDER BY price DESC
LIMIT 5;

This returns the 5 most expensive products.

---

6. What is the OFFSET Clause?

OFFSET skips a specified number of rows before starting to return rows.

---

7. Syntax of LIMIT with OFFSET

sqlCopyEditSELECT column1, column2
FROM table_name
ORDER BY column
LIMIT number_of_rows OFFSET number_to_skip;

Example:

sqlCopyEditSELECT name FROM students
ORDER BY marks DESC
LIMIT 10 OFFSET 10;

This retrieves records 11 to 20 in descending marks order.

---

8. Practical Pagination with LIMIT and OFFSET

For page 1 of 10 items per page:

sqlCopyEditSELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 0;

For page 2:

sqlCopyEditLIMIT 10 OFFSET 10;

For page N:

sqlCopyEditLIMIT 10 OFFSET (N - 1) * 10;

Use in web apps to handle pagination efficiently.

---

9. Using ORDER BY with LIMIT

Always combine LIMIT with ORDER BY to get predictable, meaningful results:

sqlCopyEditSELECT * FROM orders
ORDER BY order_date DESC
LIMIT 5;

Without ORDER BY, the order of rows is not guaranteed.

---

10. Retrieving Top-N Records

sqlCopyEditSELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 3;

Get top 3 highest-paid employees.

---

11. Pagination UI and SQL Integration

Page	SQL Query Example
1	LIMIT 10 OFFSET 0
2	LIMIT 10 OFFSET 10
3	LIMIT 10 OFFSET 20

Works with frontend frameworks (React, Angular, etc.) via API calls that support page numbers.

---

12. MySQL vs PostgreSQL vs SQLite: Syntax Notes

Feature	MySQL	PostgreSQL	SQLite
LIMIT only	✅	✅	✅
LIMIT + OFFSET	✅	✅	✅
OFFSET keyword required	Yes	Yes	Yes

You can also use this compact format (PostgreSQL, SQLite):

sqlCopyEditSELECT * FROM table
LIMIT 10, 20;  -- Fetch 20 rows, starting from offset 10 (MySQL only)

---

13. Common Errors with LIMIT and OFFSET

• Using LIMIT without ORDER BY (leads to random results)
• Incorrect offset math in pagination
• Forgetting that OFFSET starts from 0
• Overusing high OFFSET values can degrade performance

---

14. LIMIT with Joins

sqlCopyEditSELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
ORDER BY o.total DESC
LIMIT 10;

LIMIT applies after the join result is computed.

---

15. LIMIT in Subqueries

sqlCopyEditSELECT * FROM (
  SELECT * FROM products
  ORDER BY price DESC
  LIMIT 5
) AS top_products
ORDER BY price ASC;

Use subqueries when needing multiple sorted stages.

---

16. Performance Tips

• Avoid high OFFSET values on large datasets
• Consider using key-based pagination (e.g., WHERE id > last_seen_id)
• Add indexes on columns used in ORDER BY

---

17. Real-World Use Cases

• Product catalog pagination
• Paginating blog posts or search results
• Top N performers in a leaderboard
• Infinite scrolling in modern web apps

---

18. Best Practices

• Always use ORDER BY with LIMIT
• Validate page number to prevent invalid offsets
• Use indexes to support sorted fields
• For large datasets, consider cursor-based or seek-based pagination instead of OFFSET

---

19. Summary Table of Syntax Patterns

Use Case	SQL Syntax Example
Top 5 users	SELECT * FROM users ORDER BY score DESC LIMIT 5
Page 2 of 10	LIMIT 10 OFFSET 10
Skip 50, take 20	LIMIT 20 OFFSET 50
Last 5 orders	ORDER BY order_date DESC LIMIT 5
With JOIN	JOIN ... ORDER BY column LIMIT X

---

20. Summary and What’s Next

The LIMIT and OFFSET clauses are essential tools for managing how much data is returned from a query. They power everything from pagination to performance optimization.