Essential SQL Queries collection

Query Collections

Duplicate value

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1

Delete Duplicate Records While Keeping One

Efficiently remove duplicates but keep one copy.

WITH cte AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS rn
    FROM your_table
)
DELETE FROM your_table WHERE id IN (SELECT id FROM cte WHERE rn > 1)

Nth Largest Value

SELECT * FROM table_name ORDER BY column_name DESC LIMIT 1 OFFSET n-1

Replace query

This query will be helpful if you want to replace a part of the string in the table.

UPDATE table_name SET 'col-1' = REPLACE('col-1', 'old string', 'new-string')

Detect missing values in a sequence

WITH RECURSIVE sequence_generator AS (
SELECT MIN(id) AS id FROM table_name
UNION ALL
SELECT id + 1 FROM sequence_generator
WHERE id < (SELECT MAX(id) FROM table_name)
)
SELECT sg.id AS missing_id
FROM sequence_generator sg
LEFT JOIN table_name o ON sg.id = o.id
WHERE o.id IS NULL;

Good To Know

Delete or truncate row having foreign key constraints

You should be 100% sure if you going to delete or truncate a table with a foreign key as it may break your foreign key constraints

Steps to be followed

• Disable the foreign key check
• Do require operations
• Enable the foreign key check

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

SET the logs of each query

Enabling query logs can be useful for debugging.

Time-based logs

Table-based logging means that MySQL logs queries and activities into a database table (mysql.general_log) instead of a file. This allows you to inspect queries directly within MySQL using SQL commands.

Command

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

View the Logs from the table

SELECT * FROM mysql.general_log;

File Based Logs

File-based logging means MySQL writes query logs to a specified file instead of storing them in a database table. This is useful when you want to analyze logs outside MySQL using text editors, log management tools, or shell commands.

SET GLOBAL log_output = "FILE"; 
SET GLOBAL general_log_file = "/path/to/your/logfile.log";
SET GLOBAL general_log = 'ON';

Off

Don’t forget to off the logs, if you are not using them. keeping them on for too long may impact performance.

SET GLOBAL general_log = 'OFF';

Common Table Expression(CTE)

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement in SQL. CTEs are often used to simplify complex queries, improve readability, and reuse query logic.

Syntax

WITH cte_name (column1, column2, ...)
AS (
    -- Query to generate the result set
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM cte_name;

What is temporary table

Temporary tables in SQL are special types of tables that exist only for the duration of a session or transaction. They are useful for storing intermediate results, reducing query complexity, and improving performance.

Temporary tables vs CTE

Both Temporary Tables and Common Table Expressions (CTEs) are used for storing intermediate results in SQL, but they serve different purposes and have different behaviors.