Basic Queries
Select All Columns
SELECT * FROM table_name;
When to use: Use this when you need to retrieve all columns from a table. Be cautious with large tables as it can be resource-intensive.
Example:
SELECT * FROM employees;
Select Specific Columns
SELECT column1, column2 FROM table_name;
When to use: Use this when you only need specific columns from a table to reduce the amount of data retrieved.
Example:
SELECT first_name, last_name FROM employees;
Where Clause
SELECT * FROM table_name WHERE condition;
When to use: Use this to filter results based on a condition.
Example:
SELECT * FROM employees WHERE department = 'Sales';
Order By
SELECT * FROM table_name ORDER BY column_name [ASC|DESC];
When to use: Use this to sort the results by a specific column in ascending or descending order.
Example:
SELECT * FROM employees ORDER BY last_name ASC;
Limit
SELECT * FROM table_name LIMIT number;
When to use: Use this to limit the number of rows returned by the query.
Example:
SELECT * FROM employees LIMIT 10;
Distinct
SELECT DISTINCT column_name
FROM table_name;
When to use: Use this to remove duplicate rows from the result set.
Example:
SELECT DISTINCT department FROM employees;
Count
SELECT COUNT(column_name)
FROM table_name;
When to use: Use this to count the number of rows that match a specified condition.
Example:
SELECT COUNT(*) FROM employees WHERE department = 'Sales';
Sum
SELECT SUM(column_name)
FROM table_name;
When to use: Use this to calculate the total sum of a numeric column.
Example:
SELECT SUM(salary) FROM employees;
Avg
SELECT AVG(column_name)
FROM table_name;
When to use: Use this to calculate the average value of a numeric column.
Example:
SELECT AVG(salary) FROM employees;
Min
SELECT MIN(column_name)
FROM table_name;
When to use: Use this to find the minimum value in a column.
Example:
SELECT MIN(salary) FROM employees;
Max
SELECT MAX(column_name)
FROM table_name;
When to use: Use this to find the maximum value in a column.
Example:
SELECT MAX(salary) FROM employees;
Intermediate Queries
Join
Inner Join
SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.common_column = b.common_column;
When to use: Use this to combine rows from two tables based on a related column between them.
Example:
SELECT employees.first_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
Left Join
SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b ON a.common_column = b.common_column;
When to use: Use this to get all rows from the left table and the matched rows from the right table. If no match, NULL values are returned for columns from the right table.
Example:
SELECT employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
Right Join
SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b ON a.common_column = b.common_column;
When to use: Use this to get all rows from the right table and the matched rows from the left table. If no match, NULL values are returned for columns from the left table.
Example:
SELECT employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;
Subquery
SELECT column1
FROM table1
WHERE column2 = (SELECT column2 FROM table2 WHERE condition);
When to use: Use this when you need to use the result of one query as a condition in another query.
Example:
SELECT first_name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE department_name = 'Sales');
Group By
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1;
When to use: Use this to group rows that have the same values in specified columns into summary rows.
Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
Having
SELECT column1, COUNT(*)
FROM table_name
GROUP BY column1
HAVING COUNT(*) > 1;
When to use: Use this to filter groups based on a condition.
Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
Case
SELECT column1,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END
FROM table_name;
When to use: Use this to perform conditional logic in SQL queries.
Example:
SELECT first_name,
CASE
WHEN salary > 50000 THEN 'High'
WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_range
FROM employees;
Union
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
When to use: Use this to combine the results of two or more SELECT statements without duplicates.
Example:
SELECT first_name FROM employees
UNION
SELECT first_name FROM contractors;
Union All
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;
When to use: Use this to combine the results of two or more SELECT statements with duplicates.
Example:
SELECT first_name FROM employees
UNION ALL
SELECT first_name FROM contractors;
Exists
SELECT column1
FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE condition);
When to use: Use this to check for the existence of rows in a subquery.
Example:
SELECT first_name
FROM employees
WHERE EXISTS (SELECT 1 FROM departments WHERE department_name = 'Sales');
Advanced Queries
Window Functions
Row Number
SELECT column1,
ROW_NUMBER() OVER (PARTITION BY column2 ORDER BY column3) as row_num
FROM table_name;
When to use: Use this to assign a unique sequential integer to rows within a partition of a result set.
Example:
SELECT first_name,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num
FROM employees;
Rank
SELECT column1,
RANK() OVER (PARTITION BY column2 ORDER BY column3) as rank
FROM table_name;
When to use: Use this to rank rows within a partition of a result set, with gaps in ranking values.
Example:
SELECT first_name,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;
Dense Rank
SELECT column1,
DENSE_RANK() OVER (PARTITION BY column2 ORDER BY column3) as dense_rank
FROM table_name;
When to use: Use this to rank rows within a partition of a result set, without gaps in ranking values.
Example:
SELECT first_name,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank
FROM employees;
Common Table Expressions (CTE)
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT * FROM cte_name;
When to use: Use this to create a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
Example:
WITH SalesEmployees AS (
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales'
)
SELECT * FROM SalesEmployees;
Recursive CTE
WITH RECURSIVE cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
UNION ALL
SELECT column1, column2
FROM table_name
JOIN cte_name ON table_name.column = cte_name.column
)
SELECT * FROM cte_name;
When to use: Use this to perform recursive queries, such as hierarchical or tree-structured data.
Example:
WITH RECURSIVE EmployeeHierarchy AS (
SELECT employee_id, manager_id, first_name, last_name
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.first_name, e.last_name
FROM employees e
JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;
Pivot
SELECT *
FROM (
SELECT column1, column2, column3
FROM table_name
) src
PIVOT (
MAX(column3)
FOR column2 IN ([value1], [value2], [value3])
) pvt;
When to use: Use this to transform rows into columns.
Example:
SELECT *
FROM (
SELECT department, job_title, salary
FROM employees
) src
PIVOT (
MAX(salary)
FOR job_title IN ('Manager', 'Developer', 'Analyst')
) pvt;
Unpivot
SELECT column1, column2, column3
FROM (
SELECT column1, value1, value2, value3
FROM table_name
) p
UNPIVOT (
column3 FOR column2 IN (value1, value2, value3)
) AS unpvt;
When to use: Use this to transform columns into rows.
Example:
SELECT department, job_title, salary
FROM (
SELECT department, Manager, Developer, Analyst
FROM salaries
) p
UNPIVOT (
salary FOR job_title IN (Manager, Developer, Analyst)
) AS unpvt;
JSON Functions
JSON Extract
SELECT JSON_EXTRACT(column, '$.key') as extracted_value
FROM table_name;
When to use: Use this to extract data from a JSON column.
Example:
SELECT JSON_EXTRACT(details, '$.address.city') as city
FROM employees;
JSON Array Length
SELECT JSON_ARRAY_LENGTH(column) as array_length
FROM table_name;
When to use: Use this to get the length of a JSON array.
Example:
SELECT JSON_ARRAY_LENGTH(details->'$.projects') as project_count
FROM employees;
Full-Text Search
SELECT * FROM table_name
WHERE MATCH(column1, column2) AGAINST('search_term');
When to use: Use this to perform full-text search on text columns.
Example:
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('SQL tutorial');
Indexing
Create Index
CREATE INDEX index_name ON table_name (column1, column2);
When to use: Use this to improve the performance of queries that filter or sort by indexed columns.
Example:
CREATE INDEX idx_department ON employees (department);
Drop Index
DROP INDEX index_name ON table_name;
When to use: Use this to remove an index when it is no longer needed or if it negatively impacts performance.
Example:
DROP INDEX idx_department ON employees;
Transactions
Begin Transaction
BEGIN TRANSACTION;
When to use: Use this to start a transaction.
Example:
BEGIN TRANSACTION;
Commit
COMMIT;
When to use: Use this to save the changes made in the transaction.
Example:
COMMIT;
Rollback
ROLLBACK;
When to use: Use this to undo the changes made in the transaction.
Example:
ROLLBACK;
Stored Procedures
Create Procedure
CREATE PROCEDURE procedure_name (IN param1 INT, OUT param2 INT)
BEGIN
-- procedure body
END;
When to use: Use this to encapsulate complex logic that can be reused.
Example:
CREATE PROCEDURE GetEmployeeCount (OUT emp_count INT)
BEGIN
SELECT COUNT(*) INTO emp_count FROM employees;
END;
Call Procedure
CALL procedure_name(param1, @param2);
When to use: Use this to execute a stored procedure.
Example:
CALL GetEmployeeCount(@emp_count);
Get Output Parameter
SELECT @param2;
When to use: Use this to retrieve the value of an output parameter from a stored procedure.
Example:
SELECT @emp_count;
Triggers
Create Trigger
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
-- trigger body
END;
When to use: Use this to automatically perform an action in response to an event on a table.
Example:
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_log (action, employee_id)
VALUES ('INSERT', NEW.employee_id);
END;
Drop Trigger
DROP TRIGGER trigger_name;
When to use: Use this to remove a trigger when it is no longer needed.
Example:
DROP TRIGGER after_employee_insert;
Top comments (0)