What are the most used MySQL queries?

The 25 most used MySQL queries include: SELECT (with WHERE, ORDER BY, LIMIT, GROUP BY), JOIN (INNER, LEFT, RIGHT), INSERT (single and multiple rows), UPDATE, DELETE, aggregate functions (COUNT, SUM, AVG, MAX, MIN), pattern matching (LIKE), IN clause, BETWEEN, subqueries, and UNION. These cover 90% of common database operations.

How do I write a SELECT query in MySQL?

Basic syntax: SELECT column1, column2 FROM table_name WHERE condition ORDER BY column LIMIT n; Example: SELECT name, email FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 10; Always specify columns instead of * for better performance.

What is the difference between WHERE and HAVING in MySQL?

WHERE filters rows before grouping, HAVING filters groups after GROUP BY. Use WHERE for row-level conditions, HAVING for aggregate conditions. Example: WHERE price > 100 filters products, HAVING COUNT(*) > 10 filters groups with more than 10 items.

How do I join tables in MySQL?

Use JOIN clauses: INNER JOIN returns matching rows, LEFT JOIN returns all left table rows, RIGHT JOIN returns all right table rows. Syntax: SELECT * FROM table1 JOIN table2 ON table1.id = table2.foreign_id; Always specify join conditions to avoid cartesian products.

MySQL 25 Most Used Queries: Complete Guide with Examples

MySQL queries are the foundation of database operations. Mastering the most used queries enables efficient data retrieval, manipulation, and analysis. This comprehensive guide covers the 25 most used MySQL queries with detailed explanations, real-world examples, and best practices.

From basic SELECT statements to complex JOINs and subqueries, these queries will handle most of your database needs.

25 Most Used MySQL Queries

SELECT All Records

SELECT

Retrieve all columns and rows from a table

Query:

SELECT * FROM users;

Explanation:

Returns all columns and rows from users table. Use specific columns instead of * for better performance.

Use Case:

Viewing all data, initial exploration

SELECT Specific Columns

SELECT

Retrieve only specific columns

Query:

SELECT id, name, email FROM users;

Explanation:

Returns only id, name, and email columns. More efficient than SELECT *.

Use Case:

Reducing data transfer, improving performance

SELECT with WHERE Clause

SELECT

Filter rows based on conditions

Query:

SELECT * FROM users WHERE status = 'active';

Explanation:

Returns only users with active status. WHERE clause filters rows before retrieval.

Use Case:

Filtering data, conditional retrieval

SELECT with ORDER BY

SELECT

Sort results in ascending or descending order

Query:

SELECT * FROM products ORDER BY price DESC;

Explanation:

Returns products sorted by price in descending order (highest first).

Use Case:

Sorting results, ranking data

SELECT with LIMIT

SELECT

Limit the number of rows returned

Query:

SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

Explanation:

Returns only the 10 most recent orders. Useful for pagination and top N queries.

Use Case:

Pagination, top N records, performance optimization

SELECT with GROUP BY

SELECT

Group rows by one or more columns

Query:

SELECT category, COUNT(*) AS count FROM products GROUP BY category;

Explanation:

Groups products by category and counts items in each group.

Use Case:

Aggregations, statistics, reporting

SELECT with HAVING

SELECT

Filter groups after GROUP BY

Query:

SELECT category, COUNT(*) AS count FROM products GROUP BY category HAVING count > 10;

Explanation:

Shows only categories with more than 10 products. HAVING filters groups, WHERE filters rows.

Use Case:

Filtering aggregated results, conditional grouping

SELECT DISTINCT

SELECT

Return unique values

Query:

SELECT DISTINCT country FROM users;

Explanation:

Returns unique country values, removing duplicates.

Use Case:

Finding unique values, data deduplication

INNER JOIN

JOIN

Join tables returning only matching rows

Query:

SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;

Explanation:

Returns users and their orders. Only shows users who have orders.

Use Case:

Relating data from multiple tables, combining datasets

LEFT JOIN

JOIN

Join tables returning all rows from left table

Query:

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

Explanation:

Returns all users, even if they have no orders. Orders will be NULL for users without orders.

Use Case:

Including all records from primary table, optional relationships

RIGHT JOIN

JOIN

Join tables returning all rows from right table

Query:

SELECT u.name, o.total FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

Explanation:

Returns all orders, even if user doesn't exist. User will be NULL for orphaned orders.

Use Case:

Including all records from secondary table, finding orphaned records

INSERT Single Row

INSERT

Insert one row into a table

Query:

INSERT INTO users (name, email, status) VALUES ('John Doe', 'john@example.com', 'active');

Explanation:

Inserts a new user with specified values. Returns the auto-increment ID if id is AUTO_INCREMENT.

Use Case:

Adding new records, user registration, data entry

INSERT Multiple Rows

INSERT

Insert multiple rows in one statement

Query:

INSERT INTO products (name, price) VALUES ('Product A', 10.99), ('Product B', 20.99), ('Product C', 30.99);

Explanation:

Inserts three products in a single statement. More efficient than multiple INSERT statements.

Use Case:

Bulk inserts, data migration, batch operations

UPDATE Single Column

UPDATE

Update values in existing rows

Query:

UPDATE users SET status = 'inactive' WHERE id = 123;

Explanation:

Updates status to inactive for user with id 123. Always use WHERE to avoid updating all rows.

Use Case:

Modifying records, status changes, data corrections

UPDATE Multiple Columns

UPDATE

Update multiple columns at once

Query:

UPDATE users SET name = 'Jane Doe', email = 'jane@example.com' WHERE id = 123;

Explanation:

Updates both name and email for the specified user.

Use Case:

Updating user profiles, bulk modifications

DELETE

Delete rows from a table

Query:

DELETE FROM users WHERE status = 'deleted' AND deleted_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

Explanation:

Deletes users marked as deleted more than 30 days ago. Always use WHERE clause!

Use Case:

Removing records, data cleanup, soft delete cleanup

COUNT Records

Aggregate

Count total number of rows

Query:

SELECT COUNT(*) AS total_users FROM users;

Explanation:

Returns total number of users. COUNT(*) counts all rows, including NULLs.

Use Case:

Statistics, reporting, data validation

SUM Values

Aggregate

Calculate sum of numeric column

Query:

SELECT SUM(amount) AS total_revenue FROM orders WHERE status = 'completed';

Explanation:

Calculates total revenue from completed orders. SUM() ignores NULL values.

Use Case:

Financial calculations, totals, aggregations

AVG Values

Aggregate

Calculate average of numeric column

Query:

SELECT AVG(rating) AS avg_rating FROM reviews WHERE product_id = 456;

Explanation:

Calculates average rating for a specific product.

Use Case:

Performance metrics, ratings, statistical analysis

MAX and MIN

Aggregate

Find maximum and minimum values

Query:

SELECT MAX(price) AS max_price, MIN(price) AS min_price FROM products;

Explanation:

Finds highest and lowest prices in products table.

Use Case:

Finding extremes, range analysis, peak values

LIKE Pattern Matching

SELECT

Search for patterns in text

Query:

SELECT * FROM users WHERE email LIKE '%@gmail.com';

Explanation:

Finds all users with Gmail addresses. % matches any characters, _ matches single character.

Use Case:

Text search, pattern matching, filtering

IN Clause

SELECT

Match values in a list

Query:

SELECT * FROM products WHERE category IN ('Electronics', 'Books', 'Clothing');

Explanation:

Returns products in specified categories. More readable than multiple OR conditions.

Use Case:

Multiple value matching, filtering by list

BETWEEN Range

SELECT

Match values within a range

Query:

SELECT * FROM orders WHERE total BETWEEN 100 AND 500;

Explanation:

Returns orders with total between 100 and 500 (inclusive).

Use Case:

Range queries, date ranges, numeric ranges

Subquery

SELECT

Use query result in another query

Query:

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

Explanation:

Returns products with price above average. Subquery calculates average first.

Use Case:

Complex filtering, dynamic conditions, correlated queries

UNION

SELECT

Combine results from multiple queries

Query:

SELECT name FROM users UNION SELECT name FROM customers;

Explanation:

Combines names from users and customers tables, removing duplicates. Use UNION ALL to keep duplicates.

Use Case:

Combining datasets, merging results, data consolidation

Query Categories

SELECT Queries

Retrieve data from tables:

SELECT with WHERE, ORDER BY, LIMIT
GROUP BY and HAVING
DISTINCT, LIKE, IN, BETWEEN
Subqueries and UNION

JOIN Queries

Combine data from multiple tables:

INNER JOIN - matching rows
LEFT JOIN - all left table rows
RIGHT JOIN - all right table rows

Data Modification

Insert, update, and delete data:

INSERT - single and multiple rows
UPDATE - modify existing data
DELETE - remove records

Aggregate Queries

Calculate statistics:

COUNT, SUM, AVG
MAX and MIN
Used with GROUP BY

Best Practices

⚠️ Always Use WHERE in UPDATE/DELETE

Never run UPDATE or DELETE without WHERE clause. Always test with SELECT first: SELECT * FROM table WHERE condition; then UPDATE/DELETE with same condition.

✅ Use Specific Columns

Avoid SELECT * in production. Specify columns you need: SELECT id, name, email FROM users; This improves performance and reduces data transfer.

✅ Index Frequently Queried Columns

Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses. This dramatically improves query performance on large tables.

✅ Use LIMIT for Large Result Sets

Always use LIMIT when retrieving large datasets. For pagination, use LIMIT offset, count or better yet, cursor-based pagination with WHERE id > last_id.

Format Your MySQL Queries

Use our SQL Formatter tool to format, validate, and beautify your MySQL queries for better readability and debugging.

Open SQL Formatter